在访问查询中调用VBA函数 [英] Calling a VBA Function in an Access Query

问题描述

我正在尝试将8个不同查询的结果组合回一个查询中.所有要使用的查询都是查询的查询. 8个系列的4个查询将球员分为多少场，具体取决于他们打了几轮高尔夫球.每个系列中的最后一个查询将计算每个玩家的确切让分.

I am trying to combine the results of 8 different queries back into the one query. All the queries to be used are queries of queries of queries of queries. The 8 series of 4 queries split the players up depending on how many rounds of golf they have played. The last query in each series calculates the exact handicap of each player.

我尝试使用的代码可能无法实现.

What I am trying to do may not be possible with the code I am using.

功能代码如下:

Function EHC(PlayerID As Long) As Long

    Dim queT20 As Recordset
    Dim que3to6 As Recordset
    Dim que7or8 As Recordset
    Dim que9or10 As Recordset
    Dim que11or12 As Recordset
    Dim que13or14 As Recordset
    Dim que15or16 As Recordset
    Dim que17or18 As Recordset
    Dim que19or20 As Recordset

    Set queT20 = CurrentDb.OpenRecordset("Top20Count")
    queT20.FindFirst ("PlayerID =" & PlayerID)
    If queT20![PlayerID] >= 19 Then
        Set que19or20 = CurrentDb.OpenRecordset("P_19or20ExactHC")
        que19or20.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que19or20.Exact_HC
        que19or20.Close
        Set que19or20 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que17or18 = CurrentDb.OpenRecordset("P_17or18ExactHC")
        que17or18.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que17or18.Exact_HC
        que17or18.Close
        Set que17or18 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que15or16 = CurrentDb.OpenRecordset("P_15or16ExactHC")
        que15or16.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que15or16.Exact_HC
        que15or16.Close
        Set que15or16 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que13or14 = CurrentDb.OpenRecordset("P_13or14ExactHC")
        que13or14.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que13or14.Exact_HC
        que13or14.Close
        Set que13or14 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que11or12 = CurrentDb.OpenRecordset("P_11or12ExactHC")
        que11or12.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que11or12.Exact_HC
        que11or12.Close
        Set que11or12 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que9or10 = CurrentDb.OpenRecordset("P_9or10ExactHC")
        que9or10.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que9or10.Exact_HC
        que9or10.Close
        Set que9or10 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que7or8 = CurrentDb.OpenRecordset("P_7or8ExactHC")
        que7or8.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que7or8.Exact_HC
        que7or8.Close
        Set que7or8 = Nothing
    ElseIf queT20![PlayerID] >= 17 Then
        Set que3to6 = CurrentDb.OpenRecordset("P_3to6ExactHC")
        que3to6.FindFirst ("PlayerID =" & PlayerID)
        ExactHC = que3to6.Exact_HC
        que3to6.Close
        Set que3to6 = Nothing
    Else: ExactHC = 0
    End If
    queT20.Close
    Set queT20 = Nothing
End Function

和SQL查询语句:

SELECT PlayerInfo.PlayerID, PlayerInfo.Display, EHC([PlayerInfo]![PlayerID]) AS ExactHandicap
FROM ((((((((PlayerInfo INNER JOIN Top20Count ON PlayerInfo.PlayerID = Top20Count.PlayerID) 
    INNER JOIN P_3to6ExactHC ON PlayerInfo.PlayerID = P_3to6ExactHC.PlayerID) 
    INNER JOIN P_7or8ExactHC ON PlayerInfo.PlayerID = P_7or8ExactHC.PlayerID) 
    INNER JOIN P_9or10ExactHC ON PlayerInfo.PlayerID = P_9or10ExactHC.PlayerID) 
    INNER JOIN P_11or12ExactHC ON PlayerInfo.PlayerID = P_11or12ExactHC.PlayerID) 
    INNER JOIN P_13or14ExactHC ON PlayerInfo.PlayerID = P_13or14ExactHC.PlayerID) 
    INNER JOIN P_15or16ExactHC ON PlayerInfo.PlayerID = P_15or16ExactHC.PlayerID) 
    INNER JOIN P_17or18ExactHC ON PlayerInfo.PlayerID = P_17or18ExactHC.PlayerID) 
    INNER JOIN P_19or20ExactHC ON PlayerInfo.PlayerID = P_19or20ExactHC.PlayerID
WHERE (((PlayerInfo.Display)=True));

当我尝试运行此程序时，出现错误消息Undefined function 'EHC' in expression.

When i try to run this i get an error message of Undefined function 'EHC' in expression.

我还尝试了使用IIF或SWITCH语句代替函数调用的相同查询.

I have also tried the same query with either a IIF or SWITCH statement in replace of the function call.

我大致基于任何帮助将不胜感激，因为我只有这个问题有待解决，我希望我将接近完成这一自我强加的噩梦.

Any help would be much appreciated as I only have this problem to sort out and I hope I will be close to finishing this self imposed nightmare.

推荐答案

我在这里看到了几个问题:

I see several issues here:

1. re:未定义函数"错误-确保您的函数在标准VBA模块中.

1. re: "Undefined function" error - Make sure that your Function is in a standard VBA module.

确保模块的最顶部附近包含Option Explicit语句.

Make sure that the Module includes an Option Explicit statement near the very top.

确保您的函数实际上对您的查询可见.最好将其明确声明为Public Function.

Ensure that your Function is in fact visible to your query. It is best to declare it explicitly as a Public Function.

您的函数实际上并未返回值.它需要在最后一个End Function语句之前分配EHC = ExactHC.

Your Function does not actually return a value. It needs to assign EHC = ExactHC just before the final End Function statement.

检查您的查询以查看是否确实需要所有这些INNER JOIN.我真诚地怀疑您会这样做，因为您的EHC()函数会在计算精确差点的过程中从其他查询中提取所需的信息.

Check your query to see if you really do need all of those INNER JOINs. I sincerely doubt that you do, since your EHC() function will pull the required information from those other queries in the process of calculating the Exact Handicap.

这篇关于在访问查询中调用VBA函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！