Criteria Expression Mismatch

[razchip]

In the following code, I'm trying to verify that the password entered and the password in the table are the same. I keep getting a criteria expression mismatch when the DLookup portion runs.

In my UserInfo table, the name is text and the password is numeric. Any ideas why I'm getting this mismatch, I've tried changing everything to text, but I get the same problem.

Private Sub Cancel_Click()

'Check to see if data is entered into the UserName combo box

If IsNull(Me.userstart) Or Me.userstart = "" Then
MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
Me.userstart.SetFocus
Exit Sub
End If

'Check to see if data is entered into the password box

If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
Me.txtPassword.SetFocus
Exit Sub
End If

'Check value of password in tblEmployees to see if this matches value chosen in combo box

If Me.txtPassword.Value = DLookup("Password", "UserInfo", "[Password]=" & Me.txtPassword.Value) Then

DoCmd.OpenForm "Current User"
DoCmd.OpenForm "frmMain"
Forms![user input].Visible = False

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txtPassword.SetFocus
End If

'If User Enters incorrect password 3 times database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
MsgBox "You do not have access to this database.Please contact admin.", _
vbCritical, "Restricted Access!"
Application.Quit
End If

End Sub

Thanks for the help.
Greg

 

[Gammachaser]

In the comment just before the Password DLookup you say the table is tblEmployees yet in the DLookup you have "UserInfo" where the table name should be. Which is it?

 

[razchip]

UserInfo, I forgot to change the message when I renamed the table, sorry.

Thanks for the help.
Greg

 

[Gammachaser]

The way your DLookup is stuctured, you will get a positive match if ANY password in the table matches the password in the textbox, even if it does not belong to the current user. Effectively, you are saying 'If this password is in the table, it is correct.' While it is not likely that someone would accidently type someone else's password, it is possible and therefore not a good way to check. You should use something like

If Me.txtPassword.Value = DLookup("Password", "UserInfo", "[UserName]= '" & CurrentUser & "'") Then

Also, they way you are using DLookup, if the password is not in the table (somewhere), you will get a null which could be a source of a type mismatch (but I would expect 'Invalid use of Null' instead.)

 

[razchip]

Gammachaser,

Thanks for the help. I made the corrections you listed and it works great. Appreicate your time.

Thanks for the help.
Greg