DLookup Help Required

[siddnave2]

Hi,
I have written below code for user login and table given below tbluser, But
while login if i am login for row 1 with password of row 2 it is authenticating. Please suggest where i have to correct

tbluser
ID UserName UserLogin Password UserSecurity
1 Naveen Nani **** Admin
2 2022 Blr *** User


Private Sub Command1_Click()
If IsNull(Me.txtLoginID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'Process the job
If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "'"))) Or _
(IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect LoginID or Password"
Else
'MsgBox "LoginID and Passowrd Correct"
DoCmd.Close
DoCmd.OpenForm "frmTempLabelsToPrint"
End If
End If
End Sub

 

[sneuberg]

Consider something like:

If IsNull(DLookup("*", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "' AND Password = '" & Me.txtPassword.Value & "'")) Then

as this would only look at one record.

 

[The_Doc_Man]

You should (in theory) only do this with one lookup. This is based in part on things I have learned working with US Defense Dept servers and the Security+ certification.

Do something like this, and you can figure out the variable types from context. You also need to have some character sequence that is disallowed in usernames and passwords. Let's say that your password rules disallow the tilde (~) character. (Pick some other punctuation if you wish, I'm just offering it as an example.)

txtCmpID = Trim$( Nz( Me.txtLoginID, "~~~~" ) )  'username format validation

txtPwd = Trim$( Nz( Me.txtPassword, "~~~~" ) )  'password format validation

txtCmpPwd = Nz( DLookup( "Password", "tblUser", "Userlogin='" & txtCmpID & "'"), "~~~~" )   'do the lookup

boBlockLogin = ( txtCmpID = "~~~~" ) OR ( txtCmpPwd = "~~~~" ) OR ( txtPwd <> txtCmpPwd )

if boBlockLogin then goto Disallowed

In English, you block logins if the input username or input password is null (which you substitute with four of the disallowed characters) or if the username isn't in your table (in which case you supply a password of four disallowed characters) or if the input password and the password from the user authorization table don't match.

Doing it this way simplifies the lookup and also takes care of a couple of cases that might otherwise have been left as somewhat ambiguous.

Please also note for general reference that D.o.D. standards and most security handbooks suggest that a login rejected for bad username, bad password, or password mismatch would ALL be given the same message - "Invalid username or password." Don't tell the user which one was wrong. This is one case where less information is better (for your system, not for the user). It is also why I am suggesting that you only want to do ONE lookup for ONE field in ONE record - and you will be unambiguously done with the table when you finish that lookup.

Note also that the above sequence doesn't actually limit your username or password to four characters. It just uses the four-disallowed-character sequence to represent any illegal input. For all the above code cares, you could have 255-character usernames and 255-character passwords. (But anyone who enters a 255-character password gets what they deserve.)

 

[arnelgp]

'Process the job
If IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "' And Password = '" & Me.txtPassword.Value & "'")) Then
MsgBox "Incorrect LoginID or Password"

 

[siddnave2]

Hi sneuberg,

Thanks for reply but the same is giving Run time error 3075

Syntax error (Missing operator) in query expression '*'.

Regards

 

[siddnave2]

Hi arnelgp,

Your solution worked out for me.

Thanks you very much .

Naveen.

 

[siddnave2]

Hi arnelgp,

Further to the query corrected i have added few more security levels like if admin it has to open admin page form else other form. But while incorporating this code i am getting error "runtime error 13 type mismatch" in below line of Dlookup, full code is given for your ref. Please help

UserLevel = DLookup("UserSecurity'", "tbluser", "UserLogin ='" & Me.txtLoginID.Value & "'")

Private Sub Command1_Click()
Dim UserLevel As Integer

If IsNull(Me.txtLoginID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required!"
Me.txtLoginID.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required!"
Me.txtPassword.SetFocus
Else
'Process the job
If IsNull(DLookup("UserLogin", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "' And Password = '" & Me.txtPassword.Value & "'")) Then
MsgBox "Incorrect LoginID or Password"

Else

UserLevel = DLookup("UserSecurity'", "tbluser", "UserLogin ='" & Me.txtLoginID.Value & "'")

'MsgBox "WELCOME"
DoCmd.Close
If UserLevel = 1 Then
DoCmd.OpenForm "AdminPage"
Else
DoCmd.OpenForm "frmTempLabelsToPrint"
End If
End If
End If
End Sub

 

[arnelgp]

you have declare a variable there, UserLevel as integer, is your field UserSecurity also an integer or numeric?

you can further trim your code to this:

Private Sub Command1_Click()
Dim UserLevel As Integer

If IsNull(Me.txtLoginID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required!"
Me.txtLoginID.SetFocus
Exit sub
End If
If IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required!"
Me.txtPassword.SetFocus
Exit Sub
End If

'Process the job
UserLevel = NZ(DLookup("UserSecurity", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "' And Password = '" & Me.txtPassword.Value & "'"), 0)

If UserLevel = 0 Then
MsgBox "Incorrect LoginID or Password"
Me.txtLoginID.SetFocus
Else

'MsgBox "WELCOME"
DoCmd.Close
If UserLevel = 1 Then
DoCmd.OpenForm "AdminPage"
Else
DoCmd.OpenForm "frmTempLabelsToPrint"
End If
End If

 

[siddnave2]

Hi arnelgp,

UserSecurity feild is Text field in Table.

I have tried the code suggested by you still same "runtime error 13 type mismatch" in same line.

 

[arnelgp]

so just change our code to text:

Private Sub Command1_Click()
Dim UserLevel As String

If IsNull(Me.txtLoginID) Then
MsgBox "Please Enter LoginID", vbInformation, "LoginID Required!"
Me.txtLoginID.SetFocus
Exit sub
End If
If IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required!"
Me.txtPassword.SetFocus
Exit Sub
End If

'Process the job
UserLevel = DLookup("UserSecurity", "tblUser", "UserLogin ='" & Me.txtLoginID.Value & "' And Password = '" & Me.txtPassword.Value & "'") & ""

If UserLevel = "" Then
MsgBox "Incorrect LoginID or Password"
Me.txtLoginID.SetFocus
Else

'MsgBox "WELCOME"
DoCmd.Close
If UserLevel = "1" Then
DoCmd.OpenForm "AdminPage"
Else
DoCmd.OpenForm "frmTempLabelsToPrint"
End If
End If

 

[siddnave2]

Hi arnelgp,

Now the code is not having error but by default for Admin & User it is opening same form. Please suggest

Thanks,
Naveen.

 

[siddnave2]

Hi,

Please suggest.

 

[sneuberg]

Other than the user actually having a UserSecurity value of "1" assigned to them I can't see from the code why that would be happening. I suggest you put in a break point near the beginning and single step through the code with the locals window open to see what's going on.

https://support.microsoft.com/en-us/kb/108438 covering various ways of debugging problems. It's really good stuff to know.

 

[siddnave2]

Hi,

Have checked and not able to understand where it going wrong .. after user level passed it is going into else directly.

DoCmd.Close
If UserLevel = "1" Then
DoCmd.OpenForm "frmTempLabelsToPrint"

Else
DoCmd.OpenForm "AdminPage"

 

[sneuberg]

Have checked and not able to understand where it going wrong .. after user level passed it is going into else directly.

What is the value of Userlevel when you step to the else? You can see it's value in the locals window or by hovering over it.

 

[sneuberg]

I also suggest you check the tblUser table directly and make sure the admin record has the value of "1" for UserSecurity