Using global variable in criteria

[Ann in CA]

Hello,
I'm trying to save a query that will test to see if there are two global
variables set up or not. If there is a value in the variable, I will use it
as criteria; if not, no. My first problem comes when trying to use the
variable. I found this on this site:

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]

Unfortunately, when I try this, I get error 2482, "My database can't fine
the name 'glUsername' you entered in the expression."

And yes, I've declared glusername as a variable and entered a value in it.

TIA.

 

[Klatuu]

Where did you declare the varialbe? It has to be in a standard module just
below the Option statments and before any code. Global variables are more
trouble than they are worth. Here is how I handle this. I have a public
function in a standard module with a Static variable. If you pass it a
value, it will retain the value and return that value. If you don't pass it
a value, it will just return the current value.
Avoids the hazards of global variables and can be used in your query:

Public Function GetSomeValue(Optional varNewValue As Variant) as String
Static varOldValue As Variant

If Not IsMissing(varNewValue) Then
varOldValue = varNewValue
End If
GetSomeValue = varOldValue
End Function

 

[Ann in CA]

Thank you for your advice. How do you erase the value of this variable? If
someone presses the clear button, I can set the global variable to "", but I
don't find that same option as working here.

 

[Klatuu]

just call it with a null string

GetValue(vbNullString)

Or, you can change the return type from string to variant

Public Function GetSomeValue(Optional varNewValue As Variant) as String
to
Public Function GetSomeValue(Optional varNewValue As Variant) as Variant

Then you can assign it a Null
GetValue(Null)

 

[Ann in CA]

Great, the first one works perfect. I am assuming there is no advantage of
changing it to a variant but that it would work the same. Thank you so much
for your help!

 

[Klatuu]

No, there is no real advantage. The reason I included the Null version is
that some people (I included) use the concept that Null means "it has no
value" and vbNullString to mean "I don't know what the value is"

For example, if a person has a middle initial, but we don't know what it is,
I would populate the middle initial field with vbNullString, but If we know
for certain the person has no middle initial, I would populate the middle
initial field with Null.

Now assume we have a form with FirstName, LastName, and MI and they are all
required except if we know the person has no middle initial. Then you can
test for the difference in a validation event like the Form Before Update
event:

Form_BeforeUpdate(Cancel As Integer)

If Me.MI = vbNullString Then
MsgBox "Middle Initial Required"
Cancel = True
End If
End Sub

Note that a Null value in this case in considered valid. And the example is
very simplistic. I know it would require some value at this point, but just
keeping it simple.

 

[Michel Walsh]

Unknown is often seen as the Boolean third value, true, false, and unknown;
this last one is represented with a NULL. So I use exactly the reverse:
unknown would be NULL and knowing there is nothing would be a zero length
string. It is also consistent with NULL propagation.

Vanderghast, Access MVP

 

[Klatuu]

That would be another way of doing it.

 

[Kari]

What are the hazards of using Global Variables? I hadn't heard of any
problems. Are they bugs or "best practigces" issues? Do they exist in
Access 2007?

 

[John Spencer]

One problem with global variables is that they get reset if there is any
unhandled error in your code. So if you have set a value to 25 and you hit an
unhandled error the value is now zero.

Another problem is making sure your code doesn't step on itself. That is if
you set the value in several different procedures then you may not be working
with the value you expect.
--Procedure A sets the value to 12
--Procedure B uses the value that is set by procedure A.
--HOWEVER, in the meantime you call procedure C which sets the value to 9.
--When you do run Procedure B the value is 9, but you wanted it to be 12.
--WHOOPS



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[Kari]

John,

I've never needed to use Global Variables until now. Thanks for the tip on
what to watch out for. Hopefully this will save me some blood sweat and
tears.

Kari