Login Form

[phillbaker]

I have been trying to create a login form so people have to enter a username and password.

the username box is working as when i press the login button without a password it displays an error which is in the VB code. However if i try and do it with a password as well i get the following error.

The expression you entered as a query parameter produced this error: 'test'.

Below is the code im using:

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

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

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

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

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

If Me.txt_password.Value = DLookup("[Password]", "[Users]", _
"[ID]=" & Me.cbo_login.Value) Then

ID = Me.cbo_login.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login", acSaveNo
DoCmd.OpenForm "Splash_Screen"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txt_password.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

hope someone can help i have made sure all the names of the text boxes are correct. Am i missing some brackets or " " somewhere.

 

[boblarson]

First of all - which event do you have this on? Can you post the entire event including the headers (you know the Private xxxx_xxx() stuff?

Also, since you are using DLookup, you need to use the NZ function in case a null value is returned (meaning no entry in the table that matches that criteria).

 

[phillbaker]

i have this for the OnClick Event for the Login button on the form. Hopefully below is all the code you want. Is it ok to be using the DLookup function or would your recommend something else?.

Private Sub btn_login_Click()

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

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

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

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

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

If Me.txt_password.Value = DLookup("[Password]", "[Users]", _
"[ID]=" & Me.cbo_login.Value) Then

ID = Me.cbo_login.Value

'Close logon form and open splash screen

DoCmd.Close acForm, "Login", acSaveNo
DoCmd.OpenForm "Splash_Screen"

Else
MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
"Invalid Entry!"
Me.txt_password.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

 

[boblarson]

Okay, here it goes -

1. Change this code:

If Me.txt_password.Value = DLookup("[Password]", "[Users]", _
"[ID]=" & Me.cbo_login.Value)

To this

If Me.txt_password.Value = [B][COLOR=red]Nz([/COLOR][/B]DLookup("[Password]", "[Users]", _
"[ID]=" & Me.cbo_login.Value)[B][COLOR=red],"")[/COLOR][/B]

2. I don't see where you've declared intLogonAttempts so you might have done it at form module level or in a standard module. If you haven't then you can also use

Static intLogonAttempts As Integer

from within the click event itself. That way it will retain the value and add on to it like you are doing.

 

[phillbaker]

Thank you for your suggestion. I tried that and the other parts and no go its still displaying the same error. I even come completey out of the database and went back in with still the same error.

 

[boblarson]

Which line does it highlight if you click DEBUG from the error message?

 

[phillbaker]

i have it now working i think it was proberly an error on my part. thank you for your help