Cannot pull value from user defined function in Access 2007

I have created this function GetSubName that I need to return the name that is saves from a pull down box. It does this just fine as the dialog boxes I have used shows that it sets the variable correctly. The problem is that when the SQL below runs in a query I get the error: "Undefined function 'GetSubName' in expression." I am new to VBA so any help would be much appreciated.

Here is the code:

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

And here is my SQL from inside of Access 2007:

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;

---

The reason the functions are not recognized is that you haven't fully specified the name. A public function in a form module needs to be specified with the form name:

  Forms!MyForm.GetSubName()

But this is the wrong approach, and your code is way too convoluted. You can access the value of the combo box in your query directly:

  Forms!MyForm!SubcontractorCombo

Now, the fact that you're using .SelText suggests to me either that you're doing something very very tricky, or you have your combo box set up wrong. Combo boxes can have a found field and a display value, such that a list of employees might display the employee LastName/FirstName while the combo box actually has as its bound field the EmployeeID.

If your combo box has a hidden bound field, but you want the displayed value, you don't need to use .SelText -- just use the appropriate .Column() of the combo box:

  Forms!MyForm!SubcontractorCombo.Column(1)

(the column count is zero-based, so the hidden column would be column 0, assuming it's the first column that is hidden)

Also, there's an issue that if the user selects PART of the text in the combo box, you'd have an incomplete match, so you really don't want to use .SelText at all.

So, the WHERE clause of your SQL would end up being this (assuming I've diagnosed everything correctly):

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

...and you can lose all of the marked code:

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>

---

Would an alternative approach work?

Create a table (SubNameTable) with one field: SubName.

Add one record to it.

Then change your Sub to this:

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

Now you can Remove the function and modular variable.

Then, alter your SQL as such:

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

---

Not that this is the greatest solution, but should solve some future problems of trying to reference a function in a form. You could use a parameter on your query and set its value to the combo box.

You should put the public functions in a Module. I called this one 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

Your form will reference the functions in the module:

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

The query will be able to find the public function in the module.