Can't pass global variable value to Access query

[Shawn29316]

I've read a hodgepodge of suggestions all over the internet for passing global variable info to Access queries but can't make it work for me.

I'm using ACCESS 2010. I have a form with 3 subforms. The subforms are almost identical and each has a button which should open a report. I want to have one report based on one query which receives its criteria based on which of the 3 buttons is pressed.

Here is what I've cobbled together from the various internet searches....

' I create the public variable in a module by itself

Public EmplID As String

' Next I ensure I got the global variable value and open a query.  You'll see that the report is inactive.  Eventually I'll take the MsgBox and query out of the code and just open the report but for now, the query is the problem.

Private Sub Command41_Click()

MsgBox "global variable " & (EmplID)

DoCmd.OpenQuery ("Q_JB_OfferLtr2")

'DoCmd.OpenReport ("R_JB_Job_Offer2"), acViewPreview
End Sub

' Then I try to pass the variable to the ACCESS query.  On the criteria line I use:

 = EmplID()

' And I get error 3270, saying "Property not found"

This one problem has held me up for hours. I'd appreciate any help you guys can provide!

[mbizup]

You have two options...

1. Use a "Wrapper Function"

Public Function GetEmpID() as String
   GetEmpID = EmplID 
End Function

and call the wrapper function from your query:

= GetEmpID

2. OR if your database is in .accdb format, use TempVars:
TempVars

Tempvars, can be used anywhere in your database... queries, property sheets, code, etc...

[Shawn29316]

I still didn't get the global variable to work. I assume it's just a syntax problem but.....

However, I tried the TempVars option (first time I ever heard of it) and was able to make that work. I'm thrilled to get over the first of what I'm sure will be many hurdles!

Thanks mbizup!

[mbizup]

>>> I assume it's just a syntax problem but.....

Did you try the wrapper function (it has to be a function, not a Sub)? Bottom line is you can only use global variables in VBA. For queries, property sheets, etc you need to get at your globals indirectly, by calling a function which returns their value (and the Module that contains the function cannot have the same name as the function, otherwise you'll get an 'Ambiguous Name' error).

All that said, TempVars are a wonderful tool in accdb databases, and I'm glad you've got it working!