CODEWITHSUNDEEP
ms-accessvbams-access-2010
David Morin
David Morin

Reputation: 397

DLookup not working when using a variable?

I've got some code that runs on form load:

 Private Sub Form_Load()
'Declare the username variable
 Dim loginID As String
'Get the username from the environment array variable
 loginID = Environ("USERNAME")
'Pop up a message box stating the obvious and checking the variable is set
 MsgBox ("Hello " & loginID")
'Lookup the permissions of the user based on the windows logon
 MsgBox DLookup("permissions", "Users", "userName = " & loginID)

When I run this code, the "test" message box works and it pops up my windows login id, but when the code continues and I get to the DLookup, I get a VBA error:

2471 The expression you entered as a query parameter produced this error: 'MORINDAV'

If I change the Dlookup statement to this:

MsgBox DLookup("permissions", "Users", "userName = 'loginID'")

I get error '94', invalid use of Null.

If I keep the same syntax as above, but manually enter the user value I'm testing for like this:

MsgBox DLookup("permissions", "Users", "userName = 'MORINDAV'")

The statement works and VBA shows me a message box with that user's permissions level.

I'm sure it's a syntax issue but I can't seem to get the DLookup to work while using the variable: loginID

Upvotes: 2

Views: 3580

Answers (1)

HansUp
HansUp

Reputation: 97101

userName and loginID are text type, so enclose the value of loginID in quotes.

DLookup("permissions", "Users", "userName = '" & loginID & "'")

Upvotes: 4

Related Questions