Using Dlookup to declare global string

I am trying to set a global string that can be used on multiple forms. I created a standard module and put the following code in it but I am getting an error when I try to compile the vba code. The error is "Invalid Outside Procedure". Why can't I use a DLookup in this situation? Or can I and I just have it set up wrong?

' Stores Initials of Current User to store Estimator Control value
Global strEstimator As String
strEstimator = DLookup("[Initials]", "qryGetUserName", "[UserName]=WindowsUserName()")

Open in new window

LVL 1
Lawrence SalvucciInformation Technology ManagerAsked:
Who is Participating?
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Why can't I use a DLookup in this situation? Or can I and I just have it set up wrong?>>

The only thing that can appear in a module's declarations section is Declares (Dim, Const) and Option statements.

So a DLookup(), which is a function call, is something that can't go there.

What you have to do is this:

' Stores Initials of Current User to store Estimator Control value
Global strEstimator As String

Then write a procedure to do this:

Public Sub SetEstimator()

       strEstimator = DLookup("[Initials]", "qryGetUserName", "[UserName]=WindowsUserName()")

End Sub

 and call it from somewhere, like a menu or auto exec macro.

You can then have another procedure like this:


 Public Function GetEstimator() as string

       GetEstimator = strEstimator

End Sub

 To call when you need the value.

Jim.
0
 
Lawrence SalvucciInformation Technology ManagerAuthor Commented:
Thank you for clearing that up for me. I wasn't aware of those rules. But now I am. I will set it up just as you explained. Thank you very much, Jim!
0
 
Fabrice LambertFabrice LambertCommented:
Hi,

Only declaration instructions can be written outside of procedures or function.

In your code, the 2nd line is a declaration.
The 3rd line is an assignation (ehence why you get the error).

I suggest that you wrap your statement within a function that you'll call whenever it is necessary, this will also nullify the need for a global variables (globals are rarely a good thing).
Public function getEstimator() As String
    getEstimator = DLookup("[Initials]", "qryGetUserName", "[UserName]=WindowsUserName()")
End Function

Open in new window

0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
BTW, you might want to combine it like this:


 Public Function GetEstimator() as string

       If strEstimator="" then
           strEstimator = NZ(DLookup("[Initials]", "qryGetUserName", "[UserName]=WindowsUserName()"),"")
       End if
       GetEstimator = strEstimator

End Sub

  Note that I've added a NZ() function call as Dlookup will return a NULL if the record is not found, which cannot go into a string variable.


Jim.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.