无法从 Access 2007 中的用户定义函数中提取值

Question

我创建了这个函数 GetSubName，我需要返回从下拉框中保存的名称。它做得很好，因为我使用的对话框显示它正确设置了变量。问题是，当下面的 SQL 在查询中运行时，我收到错误：“表达式中未定义函数‘GetSubName’。”我是 VBA 新手，因此我们将不胜感激。

代码如下：

Option Compare Database
Option Explicit
Private stSubName As String

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim stDocName As String
Dim stSubName As String

SubcontractorCombo.SetFocus
stSubName = SubcontractorCombo.SelText
'Confirm that stSubName variable is holding correct value'
MsgBox "Name of Subcontractor Selected is " & stSubName

SetSubName stSubName
GetSubName

DoCmd.Close

stDocName = "Summary Asphalt Production for Subcontractor"
DoCmd.OpenQuery stDocName

Exit_Command2_Click:
Exit Sub

Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click

End Sub

Public Sub SetSubName(Value As String)
'Set the module variable to be the value passed in from externally'
stSubName = Value
End Sub

Public Function GetSubName() As String
'Returns the value of the module variable'
GetSubName = stSubName
'MsgBox "GetSubName Variable is " & stSubName'

End Function

这是 Access 2007 内部的 SQL：

SELECT DISTINCTROW Subs.Subcontractor, Counties.County, Projects.ContractID,
Sum(Project_Items.USTons) AS SumOfUSTons, Projects.PlanQuantity,
Max(Project_Items.EstDate) AS MaxOfEstDate, Project_Items.Sub
FROM Counties INNER JOIN (Subs INNER JOIN (Projects INNER JOIN Project_Items ON  
Projects.ContractID = Project_Items.ProjectID) ON Subs.VendID = Project_Items.Sub) ON 
Counties.ID = Project_Items.County
WHERE (((Projects.Completed)<>True) AND ((Subs.Subcontractor)=GetSubName()))
GROUP BY Subs.Subcontractor, Counties.County, Projects.ContractID,   
Projects.PlanQuantity, Project_Items.Sub;

Answer 1

函数无法识别的原因是您没有完全指定名称。表单模块中的公共函数需要使用表单名称来指定：

  Forms!MyForm.GetSubName()

但这是错误的方法，并且您的代码太复杂了。您可以直接访问查询中组合框的值：

  Forms!MyForm!SubcontractorCombo

现在，您正在使用 .SelText 的事实表明，您要么正在做一些非常棘手的事情，要么您的组合框设置错误。组合框可以有一个找到的字段和一个显示值，这样员工列表可能会显示员工姓氏/名字，而组合框实际上将 EmployeeID 作为其绑定字段。

如果您的组合框有一个隐藏的绑定字段，但您想要显示的值，则不需要使用 .SelText - 只需使用组合框的相应 .Column() ：（

  Forms!MyForm!SubcontractorCombo.Column(1)

列数从零开始，所以隐藏的列将是第 0 列，假设它是隐藏的第一列）

此外，还有一个问题是，如果用户选择组合框中的部分文本，则会出现不完整的匹配，因此您确实不会这样做不想用.SelText 根本没有。

所以，你的 SQL 的 WHERE 子句最终会是这样的（假设我已经正确诊断了所有内容）：

  WHERE Projects.Completed<>True 
     AND Subs.Subcontractor=Forms!MyForm!SubcontractorCombo.Column(1)

...并且你可能会丢失所有标记的代码：

Option Compare Database
Option Explicit
<strike>Private stSubName As String</strike>

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
  Dim stDocName As String
  Dim stSubName As String

  SubcontractorCombo.SetFocus
  <strike>stSubName = SubcontractorCombo.SelText</strike>
  'Confirm that stSubName variable is holding correct value'
  <strike>MsgBox "Name of Subcontractor Selected is " & stSubName</strike>

  <strike>SetSubName stSubName</strike>
  <strike>GetSubName</strike>

  DoCmd.Close

  stDocName = "Summary Asphalt Production for Subcontractor"
  DoCmd.OpenQuery stDocName

Exit_Command2_Click:
  Exit Sub

Err_Command2_Click:
  MsgBox Err.Description
  Resume Exit_Command2_Click

End Sub

<strike>Public Sub SetSubName(Value As String)
  'Set the module variable to be the value passed in from externally'
  stSubName = Value
End Sub</strike>

<strike>Public Function GetSubName() As String
  'Returns the value of the module variable'
  GetSubName = stSubName
  'MsgBox "GetSubName Variable is " & stSubName'    
End Function</strike>

Answer 2

另一种方法可行吗？

创建一个表 (SubNameTable)，其中包含一个字段：SubName。

向其中添加一条记录。

然后将您的 Sub 更改为：

Public Sub SetSubName(Value As String)
  CurrentDb.Execute ("Update SubNameTable Set SubName = '" & Value & "'")
End Sub

现在您可以删除函数和模块化变量。

然后，修改你的 SQL：

SELECT 
  *BlahBlahBlahFields*
FROM 
     *BlahBlahBlahTables* 
              INNER JOIN Subs 
                 INNER JOIN SubNameTable ON Subs.SubContractor = SubNameTable.SubName
WHERE (((Projects.Completed)<>True) 
GROUP BY 
          Subs.Subcontractor, 
          Counties.County, 
          Projects.ContractID,   
          Projects.PlanQuantity, 
          Project_Items.Sub

Answer 3

这并不是最好的解决方案，但应该解决未来尝试在表单中引用函数的一些问题。您可以在查询中使用参数并将其值设置为组合框。

您应该将公共函数放在模块中。我将这个称为 Module2

Option Compare Database
Option Explicit


Private stSubName As String

Public Sub SetSubName(Value As String)
'Set the module variable to be the value passed in from externally'
stSubName = Value
End Sub

Public Function GetSubName() As String
'Returns the value of the module variable'
GetSubName = stSubName
'MsgBox "GetSubName Variable is " & stSubName'

End Function

您的表单将引用模块中的函数：

Option Compare Database
Option Explicit
Private stSubName As String

Private Sub Command2_Click()
On Error GoTo Err_Command2_Click
Dim stDocName As String
Dim stSubName As String

SubcontractorCombo.SetFocus
stSubName = SubcontractorCombo.SelText
'Confirm that stSubName variable is holding correct value'
MsgBox "Name of Subcontractor Selected is " & stSubName

Module2.SetSubName stSubName
Module2.GetSubName

DoCmd.Close

stDocName = "Summary Asphalt Production for Subcontractor"
DoCmd.OpenQuery stDocName

Exit_Command2_Click:
Exit Sub
Err_Command2_Click:
MsgBox Err.Description
Resume Exit_Command2_Click
End Sub

查询将能够找到模块中的公共函数。