query refer to a global variable

[Guest]

How can the condition in a query refer to a global variable?

 

[John Vinson]

How can the condition in a query refer to a global variable?

Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put

GetGlobal("glUsername")

in the Criteria line of the query.

John W. Vinson[MVP]

 

[Douglas J. Steele]

It can't. You'll have to create a function that returns the value of the
global variable.

 

[Guest]

Great! Understandable and do-able. Thanks much.

Queries don't have direct access to VBA variables, global or not. You
need to write a dumb little wrapper function:

Public Function GetGlobal(varname as string) As Variant
GetGlobal = Eval(varname)
End Function

If the variable is named glUsername, you'ld put

GetGlobal("glUsername")

in the Criteria line of the query.

John W. Vinson[MVP]

 

[Guest]

Very helpful. Thanks much.

It can't. You'll have to create a function that returns the value of the
global variable.

 

[Neal]

I am try to use global variables in a queries. I understand that you have to
put a wrapper function around them, and can get it to work by defining
separate wrapper functions for each variable. I liked John's suggestion
below which seemed to be a generic wrapper using the Eval function. However,
I can't get it to work - I end up with error '2482' "...can't find the name
'gstrName' you entered in the expression." I guess I just don't understand
the Eval function well enough. Below is the code I am using to test:

Option Compare Database

Public gstrName As String

Public Function GetGlobal(varname As String) As Variant
GetGlobal = Eval(varname)
End Function

Public Sub Test()
gstrName = "Contents of gstrName"
Debug.Print GetGlobal("gstrName")
End Sub