DLookup Error

[WillM]

Hi folks, I have been trying to figure this out for hours and I cannot seem to find my error. It is a basic login vba that I have put together from looking at examples here and other places.

I used the dlookup to find the UserName, Password, and Password Checkbox values in the lutWSCStaff table. It should, should being the operative word, store those values into the variables and then run the comparisons below.

The problem I have is that it is defaulting to "Incorrect username" when the correct usernames are in the login box. (txtLogin on the form).

I am sure it is probably something very small I am missing, but I cannot seem to track down where the error is occurring.

Private Sub btnLogin_Click()
Dim logVar As Variant
Dim passVar As Variant
Dim pwdVar As Variant

'dlookups for password rest, login, and password
logVar = DLookup("UserName", "lutWSCStaff", "txtLogin = '" & txtLogin() & "'")
pwdVar = DLookup("Password", "lutWSCStaff", "txtPassword = '" & txtPassword() & "'")
passVar = DLookup("ResetPassword", "lutWSCStaff", "ResetPassword = -1")


'Checks that Username and password are not empty
    If IsNull(Me.txtLogin) Or IsNull(Me.txtPassword) Then
        MsgBox "You Must Enter a Username AND Password!", vbCritical
        Me.txtLogin.SetFocus
   'determines if the username is correct
    Else
       If (logVar <> txtLogin.Value) Then
           MsgBox "Invalid User Name. Please Re-Enter Your User Name."
           Me.txtLogin = Null
           Me.txtLogin.SetFocus
     
     'determines if password is correct
       Else
           If (logVar = txtLogin.Value) Then
               If (pwdVar <> txtPassword) Then
                    MsgBox "password incorrect"
                    Me.txtPassword = Null
                    Me.txtPassword.SetFocus
     
     'determines if password should be reset
           Else
               If (passVar = -1) Then
               MsgBox "please reset password"
               'DoCmd.OpenForm "frmPasswordChange", , , "[UserName] = " & Me.txtLogin
               Else
                  If (pwdVar = txtPassword) Then
                     Me.Visible = False
                     MsgBox "login successful"
                     
                
 End If
 End If
 End If
 End If
 End If
 End If
End Sub

 

[jdraw]

Please describe the purpose of the brackets following txtLogin() and txtPassword()

 

[WillM]

From the examples I saw, it appeared to hold the value from the txtboxes. However, removing them or adding does nothing to fix the problem and in debugging, it does not throw an error.

 

[jdraw]

Try:

Remove the brackets.

txtlogin is a textbox on the form, right?

If so, try

logVar = DLookup("UserName", "lutWSCStaff", "txtLogin = '" & Me.txtLogin & "'")
pwdVar = DLookup("Password", "lutWSCStaff", "txtPassword = '" & Me.txtPassword & "'")

For testing you could add a line to check the contents
MsgBox logVar & " " & pwdvar & " " & passvar

 

[WillM]

Okay it is not working with those changes, but I did add the msgbox.

It returned the very first person in the staff list, with the generic password and a -1, Which all are set to by default.

I went into the table and removed the checkmark in the password reset box, and the msgbox returned the exact same data.

So, I logged in with the first person's login information and it prompted me to change the password.

So the issue is that it is not searching through the table for the values listed in the txtboxes on the login form.

 

[GinaWhipp]

Hmm, so the very first thing I see is...

UserName
Password

...both are problems because Access uses them, they're Reserved. More so Password than UserName but UserName is still an issue. That said, that *might* be the root of the problem, Access is confused.

 

[jdraw]

Based on Gina's comment (reserved words), you could change these field names in your table and all usages. You could try putting them in square brackets
eg [username] [password] and try again.

Good luck and thanks Gina.

 

[WillM]

Okay, changed the names completely (LoginID and UserPass), moved the dlookups inside their respective if statements, and it still is not processing past the first lookup.

Private Sub btnLogin_Click()
Dim logVar As Variant
Dim passVar As Variant
Dim pwdVar As Variant



'Checks that Username and password are not empty
    If IsNull(Me.txtLogin) Or IsNull(Me.txtPassword) Then
        MsgBox "You Must Enter a Username AND Password!", vbCritical
        Me.txtLogin.SetFocus
   'determines if the username is correct
    Else
    logVar = DLookup("LoginID", "lutWSCStaff", "txtLogin = '" & Me.txtLogin & "'")
    MsgBox logVar & " " & pwdVar & " " & passVar
       If (logVar <> txtLogin.Value) Then
           MsgBox "Invalid User Name. Please Re-Enter Your User Name."
           Me.txtLogin = Null
           Me.txtLogin.SetFocus
     
     'determines if password is correct
       Else
           If (logVar = txtLogin.Value) Then
           pwdVar = DLookup("UserPass", "lutWSCStaff", "txtPassword = '" & Me.txtPassword & "'")
           MsgBox "2nd Attempt" & logVar & " " & pwdVar & " " & passVar
               If (pwdVar <> txtPassword) Then
                    MsgBox "password incorrect"
                    Me.txtPassword = Null
                    Me.txtPassword.SetFocus
     
     'determines if password should be reset
           Else
           passVar = DLookup("ResetPassword", "lutWSCStaff", "ResetPassword = -1")
           MsgBox "3rd attempt" & logVar & " " & pwdVar & " " & passVar
               If (passVar = -1) Then
               MsgBox "please reset password"
               'DoCmd.OpenForm "frmPasswordChange", , , "[UserName] = " & Me.txtLogin
               Else
                  If (pwdVar = txtPassword) Then
                     Me.Visible = False
                     MsgBox "login successful"
           
 End If
 End If
 End If
 End If
 End If
 End If
End Sub

 

[jdraw]

Can you post a copy of your database ---remove anything confidential? Will need a few names and passwords for testing.

 

[WillM]

I think I fixed it. I love when that happens, assuming it is correct! haha...(edit: it wasn't fixed.)

Okay the old line looked like this: [FONT=&quot]logVar = DLookup("LoginID", "lutWSCStaff", "txtLogin = '" & Me.txtLogin & "'")[/FONT]

The "fixed" version looks like this:
logVar = DLookup("LoginID", "lutWSCStaff", "LoginID= '" & Me.txtLogin & "'")

It looks, to me, like I had the criteria wrong when it was searching for the username.

Now, the weird thing is that the password look up works the same way and it is in the same table.

The old version:
pwdVar = DLookup("UserPass", "lutWSCStaff", "txtPassword = '" & Me.txtPassword & "'")

and the new version (which is still the old version):
pwdVar = DLookup("UserPass", "lutWSCStaff", "txtPassword= '" & Me.txtPassword & "'")


If I change the "txtPassword=" to "UserPass=" (which is the same thing I did in the UserID/txtLogin statement) it breaks the password look up to the lutWSCStaff table.

Any ideas on why that is? :banghead:

 

[WillM]

Also, the password reset isn't working...not as close as I had hoped. I will get a testDB up in a bit.

 

[WillM]

Here is the testDB...Thanks again for looking at it.

I had to remove quite a bit of info, so it is very stripped down.

 

[WillM]

Okay, this time I actually think I fixed it. Even with testing it, it appears to be working all the way through the way it is supposed to. I needed to wrap the dlookups in "Nz" and have it return a value for comparison in the logVar statement. Since it was returning a null value with an incorrect user login, it was not processing anything past that point. I also made some other adjustments in the code and have the following:

Private Sub btnLogin_Click()
Dim logVar As Variant
Dim passVar As Variant
Dim pwdVar As Variant

'Checks that Username and password are not empty
    If IsNull(Me.txtLogin) Or IsNull(Me.txtPassword) Then
        MsgBox "You Must Enter a Username AND Password!", vbCritical
        Me.txtLogin.SetFocus
   'determines if the username is correct
    Else
    logVar = Nz(DLookup("LoginID", "lutWSCStaff", "LoginID= '" & Me.txtLogin & "'"), "A")
           If (logVar <> Me.txtLogin) Then
           MsgBox "Invalid User Name. Please Re-Enter Your User Name."
           Me.txtLogin = Null
           Me.txtLogin.SetFocus
     
     'determines if password is correct
       Else
           If (logVar = Me.txtLogin.Value) Then
            pwdVar = Nz(DLookup("UserPass", "lutWSCStaff", "LoginID = '" & txtLogin & "'"), 0)
                 If (pwdVar <> txtPassword) Then
                 MsgBox "password incorrect"
                 Me.txtPassword = Null
                 Me.txtPassword.SetFocus
     
     'determines if password should be reset
           Else
             passVar = Nz(DLookup("ResetPassword", "lutWSCStaff", "LoginID = '" & txtLogin & "'"), -1)
                If (passVar = -1) Then
                    MsgBox "please reset password"
                    'DoCmd.OpenForm "frmPasswordChange", , , "[UserName] = " & Me.txtLogin
                Else
                    If (pwdVar = txtPassword) Then
                        Me.Visible = False
                        MsgBox "login successful"
 End If
 End If
 End If
 End If
 End If
 End If
End Sub

This works just fine as far as I can tell. If you guys see anything weird or wrong let me know!

 

[jdraw]

@Will

You must have modified the database. When I test it wants to reset the password even though the name/password were correct (as pr your table)???

 

[WillM]

@jdraw - the problem was that the way the comparison was working. Without the Nz wrap, the test of the checkbox would always equate to true, which would force a password change even if the password was not in need of change.

I had it return the (0) in the first statement so that it had an integer to compare to the value of the checkbox to see if the password was correct, or if it needed to be passed to the change password part of the code. The second section returns a (-1) if the password came back null, which allows a proper evaluation against the value in the checkbox.

...at least that is how I intended it to work and how I wrote it. It is quite possible that I arrived at a working state by sheer dumb luck.