DLookup statement problem

[apinshot]

This forum has saved me so many times. Thanks to all the experts. I hope to get good enough to contribute and pay back (or pay forward as it were)...anyway...

I know I should look at and perhaps use the "on-board" security in MS Access..but I'm building an application that needs a sign on form...I have the form and the table all set.

Searching the forum I found "Ghudson's audit trail code" and I've tried to build on that.

table: tblUsers
fields: UserID and UserPWD

form: frmTestLogin
Unbound fields: txtUser and txtPWD

When I execute the code...as follows...I get a "Canceled Previous Operation" error on the first DLookup statement. I've tried everything I know to find an answer. Any suggestions?

Private Sub cmdOK_Click()
Dim strUser As String
Dim strPWD As String
Dim intUSL As Integer

'Sign into program - ask for Users name
If IsNull(Me.[txtUser]) Then
MsgBox "Please Enter Your User Name"
Me![txtPWD] = ""
Me![txtUser].SetFocus
Exit Sub
End If
'validates User Name
If IsNull(DLookup("[UserID]", "tblUsers", "[UserID] = " & Me.txtUser)) Then
MsgBox "Not A Valid User Name" 'not a valid User Name returns to Name input field
Me![txtUser] = ""
Me![txtPWD] = ""
Me![txtUser].SetFocus
Exit Sub
End If
' validates User Name and Password
If IsNull(DLookup("[UserID]", "tblUsers", "[UserID] = " & Me.[txtUser] & " and [UserPWD] ='" & Me.[txtPWD] & "'")) Then
MsgBox "Not A Valid User Name or password" 'not a valid User Name returns to Name input field
Me![txtUser] = ""
Me![txtPWD] = ""
Me![txtUser].SetFocus
Exit Sub
End If
'validates User Name and Password
If Not IsNull(DLookup("[UserID]", "tblUsers", "[UserID] = " & Me.txtUser & " and [UserPWD] ='" & Me.[txtPWD] & "'")) Then
DoCmd.OpenForm "Switchboard"
'opens "Switchboard" form
DoCmd.Close acForm, "frmTestLogin"
End If

End Sub

 

[CyberLynx]

Try:

If Nz(DLookup("[UserID]", "tblUsers", "[UserID] = " & Clng(Me.txtUser)), 0) Then

.

 

[apinshot]

That won't work.....

I'm not sure where your going with this suggestion...CLng won't work in this case, txtUser is not numeric, it's text.

When executed your statement returns a type mismatch error.

 

[GolfProRM]

Your if your UserID is a string, you'll need to have the dlookup put a ' around the field.

Dlookup("[UserID]", "tblUsers", "[UserID] = '" & me.txtUser & "'")

 

[apinshot]

Thanks GolfProRM...you got it!

Thank you.

I tried figuring out the single quote syntax but couldn't get it...

The following DLookup also fails...is the use of single quote the problem here too?

If IsNull(DLookup("[UserID]", "tblUsers", "[UserID] = '" & Me.txtUser & "'" And "[UserPWD] = '" & Me.txtPWD & "'")) Then....

Are there some general guidelines I can follow to know when to use a single quote?

 

[GolfProRM]

the single quote has to go around any field that has a string value.

The issue here is that the AND statement needs to be within quotes.
If IsNull(DLookup("[UserID]", "tblUsers", "[UserID] = '" & Me.txtUser & "' And [UserPWD] = '" & Me.txtPWD & "'")) Then

code syntax can be a pain... sometimes you end up spending half the time figuring whether or not you're between qoutes or not.

 

[apinshot]

Thank you...problem solved...

Thanks GolfProRM. Problem solved.