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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.