Please Help With DLookup Error

[Meanmyrlin]

I have been trying to piece together VBA code from this forum and the book I have to create a Login Form that opens a form depending on what UserName and Password you enter. Thanks to all the great info. on this forum I think I have come close. Unfortunately as an inexperienced user I cannot seem to identify what I am doing to cause a "Runtime Error 2001 You have canceled the previous operation". Looking through the forum it seems almost anything can cause this error so I was hoping maybe someone could identify it for me. The code I am using is:

Private Sub OK_Click()
If IsNull(Me.UserName) Or Me.UserName = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.UserName.SetFocus
Exit Sub
End If
If IsNull(Me.Password) Or Me.Password = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.Password.SetFocus
Exit Sub
End If
If Me.Password.Value = DLookup("InTblPassword", "UserNameAndPassword", "[InTblUserName]=" & "Me.UserName.Value") Then
InTblUserName = Me.UserName.Value
Dim Struserlevel As String
Struserlevel = DLookup("strlevel", "UserNameAndPassword", "[InTblUserName]=" & Me.UserName.Value)
Select Case Struserlevel
Case "A"
DoCmd.Close acForm, "LoginForm", acSaveNo
DoCmd.OpenForm "ALG_Button"

Case "M"
DoCmd.Close acForm, "LoginForm", acSaveNo
DoCmd.OpenForm "MB_Button"

Case "C"
DoCmd.Close acForm, "LoginForm", acSaveNo
DoCmd.OpenForm "CH_Button"

Case Else

MsgBox "Invalid Password", vbOKOnly, "Invalid Entry!"
Me.Password.SetFocus
End Select
Else
MsgBox "Username and password do not match"
End If
End Sub


The Bold and Underlined code is causing the problem. I have a feeling I have incorrect syntax but I am not sure how to correct it.

Password = The text box on the LoginForm that you key the Password into
UserName = The text box on the LoginForm that you key the UserName into

Example of my UserNameAndPasswordTable:


InTblUserName
N
A
M

InTblPassword
X
Y
Z

InTblCustomer
A
C
M

Any suggestions would be greatly appreciated.

Thanks

 

[Moniker]

Change this:

If Me.Password.Value = DLookup("InTblPassword", "UserNameAndPassword", "[InTblUserName]=" & "Me.UserName.Value") Then

To this:

If Me.Password.Value = DLookup("InTblPassword", "UserNameAndPassword", "[InTblUserName]='" & Me.UserName.Value & "'") Then

Color added so you could see what I did. Text is wrapped in single quotes. Numbers are not. You cannot use double quotes within double quotes. (Well, technically, you can, but it looks weird and is hard to read.)

 

[Meanmyrlin]

Thank you very much, that worked great. Now I am getting the same error referring to the second DLookup statement. I incorporated your previous suggestion and changed it to:

Struserlevel = DLookup("strlevel", "UserNameAndPassword", "[InTblUserName]='" & Me.UserName.Value & "'")

but the error persists. Do you have any other thoughts?

Thanks again.

Disregard this message. It was a typo. Thank you for all your help.

 

[Moniker]

Not a problem.

 

[Wet_Blanket]

I'll piggy back on this thread - I am experiencing the exact same problem, but can not figure it out...

Pgrm_1.Value = DLookup("acct_pgm", "[AMS_Accounts]", "acct_num='" & Account1 & "'")

Any thoughts?