Using a global variable as criteria in a combo box

[Guest]

Hi,

I have a global variable declared to store the employee id of the user that
is logged into the Access front-end.

I want to use that global variable as a limiter in the combobox so that the
combobox only pulls the employees that have supervisor ID that matches the
global variable.

However, I can't seem to get the syntax correct for the global variable to
be accessed. Instead of pulling the global variable, it comes up as a prompt
when the query is triggered.

Is there an easier way to do this than assigning that global variable to a
hidden textbox on the form to drive the query?

Thanks!

 

[George Nicholson]

If glngEmpID is your global variable, add the following to a general (i.e.,
not a form) code module:

Public Function GetEmpID() as Long
GetEmpID = glngEmpID
End Function

Then, in your query, use "GetEmpID()" for the criteria (without quotes but
*with* the parenthesis)

HTH,

 

[Marshall Barton]

I have a global variable declared to store the employee id of the user that
is logged into the Access front-end.

I want to use that global variable as a limiter in the combobox so that the
combobox only pulls the employees that have supervisor ID that matches the
global variable.

However, I can't seem to get the syntax correct for the global variable to
be accessed. Instead of pulling the global variable, it comes up as a prompt
when the query is triggered.

Is there an easier way to do this than assigning that global variable to a
hidden textbox on the form to drive the query?

VBA variables, global or otherwise are only known to VBA
code. You can create a Public function in a standard module
to return the value of the variable. E.g.

Public MyVar As Long
Public Function GetMyVar()
GetMyVar = MyVar
End Function

I think the text box on an always open form is a better
approach, because the value will not be reset when an
unhandled error occurs.