Trouble shooting login scrip MS Access 2007

[angellrp]

I have a login script but I cannot see why there is an error, here is the code:

Private Sub cmdLogin_Click()

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

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

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

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 tblStaff to see if this
'matches value chosen in combo box - Errors


'This next bit is the section that gets highlighted in my debugger as being wrong.


If Me.txtPassword.Value = DLookup("strEmpPassword", "tblStaff", "[lngMyEmpID]=" & Me.cmboLogon.Value) Then

lngMyEmpID = Me.cmboLogon.Value
'Close logon form and open switchboard

DoCmd.Close acForm, "frmLogon", acSaveNo
DoCmd.OpenForm "Switchboard"

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



txtPassword - password field in form
strEmpPassword - password field in table
tblStaff - table of users ids & passwords
lngMyEmpID - userids in table
cmboLogon - Logon id field in form (drop down list)


Can anyone help?
Thanks Angel

 

[CyberLynx]

It would be extremely helpful to know what the Error actually is and exactly what line (or statement) is highlighted.

.

 

[angellrp]

I've already indicated the highlighted line from the error in the code (see the bold bit)

The error is as follows:
Run-time error '2471':
The expression you entered as a query parameter produced this error:
'Angela'

(NB Angela is the user login name I picked from the drop down list)

Ta

 

[CyberLynx]

My apologies...my browser must be acting goofy. The only bold line I see you r post is:

This next bit is the section that gets highlighted in my debugger as being wrong..

I've never see the editor light up an entire section of code like that.

In any case...based off the Error, I would say that your Combo Box is not providing the proper data to the Where Clause within the DLookUp Function.

I'm am assuming that the Table Field lngMyEmpID is a numerical Data Type. If your Combo Box (cmboLogon) is displaying the ID number of Angela then it should work fine but, if what you see in the Combo Box when you select a name is in fact he name you selected (Angela) then you have a problem.

My guess is that the Where Clause for the DLookUp Function is looking for Numerical not String (Text). If you used the Combo Box wizard to create the query to fill the Combo Box then chances are you have two columns of Data within the Combo. One is not visible (column Width 0) and the other is visible (Column Width 1).

The First Column of Data within Combo Box list is Column(0), the second Column of Data is Column(1) and so on. I think what you may need to supply in this DLookUp Where Clause is this:

DLookup("strEmpPassword", "tblStaff", "[lngMyEmpID]=" & Me.cmboLogon.Column(0))


Give that a try.

.

 

[angellrp]

Cool Thanks that makes total sense now you say it!

I can't check at the minute as my pc needs a rebuild to make access work again.

but Thanks a lot I'll try it when I can.
Angel