Global Variable Question

[Stigm@t@]

Got a problem trying to reference a global variable in the control source of a text box.

If i do a DLookup when the form opens and have the text box value = the result it works fine ie

Private Sub Form_Open(Cancel As Integer)
Text2.Value = DLookup("UserName", "Users", "[UserID]= " & MyUserID)
End Sub

Where MyUserID is the global variable.

if i put
= DLookup("UserName", "Users", "[UserID]= " & MyUserID)
in the control source of Text2 i get #Name? as a result and if i input
= DLookup("UserName", "Users", "[UserID]= 1")
i get the correct result.

As a test i tried to have the control source = my global variable, of another text box control source with the same result.

It looks like i can only reference my global variable within the VB and not in the forms control sources. Is this correct or can you referece the Global variable in the control source.

Shot

 

[gemma-the-husky]

is myuserid also a control on your form - it will use this instead of the global var, and this will cause a problem

 

[gemma-the-husky]

set a breakpoint and examine what the code is doing around the area of concern. F8 steps through code a line at a time. hover over variables to see how they change

 

[Pat Hartman]

You can only reference vba variables in vba. If you need to get a global variable, create a function to return it. You can reference the function from SQL and controlsources.

Public Function GetMyVar()
GetMyVar = GlobalVariable
End Function

Then in a ControlSource:
=GetMyVar()

Or in SQL:

Select ...
From ...
Where somefield = GetMyVar();

 

[rockman]

Spot on answers. Good work!

 

[Pat Hartman]

If you like people's answers, add to their reputation

 

[rockman]

Duly noted Pat. I bumped up your reputation (1 vote of 1,234,239 I'm sure! )