"Incorrect Username & Password" Although Correct?

Question

I have a MS Access 2016 database where the user logs into the database with a username a password. The code would then open a set of forms BUT there is an error in which I cannot find.

Private Sub btnLogin_Click()
If IsNull(Me.txtBoxUsername) Then
    MsgBox "Please Enter Username", vbInformation, "Username Required"
    Me.txtBoxUsername.SetFocus
ElseIf IsNull(Me.txtBoxPassword) Then
    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.txtBoxPassword.SetFocus
Else
    'proccess the job
    If ((IsNull(DLookup("Username", "Staff Table", "Username='& Me.txtBoxUsername.Value &'"))) Or _
    (IsNull(DLookup("Password", "Staff Table", "Password='& Me.txtBoxPassword.Value &'")))) Then
        MsgBox "Incorrect Username Or Password"
    Else
        MsgBox "Username & Password Correct"
        DoCmd.OpenForm "Branch Form"
        DoCmd.OpenForm "Customer Form"
        DoCmd.OpenForm "Item Form"
        DoCmd.OpenForm "Order Form"
        DoCmd.OpenForm "Staff Form"
    End If
End If
End Sub

The username and password for a member of staff is 'RJ1'. When I try to login with these credentials, the MsgBox "Incorrect Username Or Password" shows up.

Why is this happening?

*In return to HansUp's question 'what is the error message?'

SOLUTION:

Private Sub btnLogin_Click()

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSelect As String
strSelect = "SELECT Count(*) FROM [Staff Table]" & vbCrLf & _
    "WHERE Username=[pUser] AND [Password]=[pPWD];"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("pUser").Value = Me!txtBoxUsername.Value
qdf.Parameters("pPWD").Value = Me!txtBoxPassword.Value
If qdf.OpenRecordset(dbOpenSnapshot)(0) = 0 Then

End If

If IsNull(Me.txtBoxUsername) Then

    MsgBox "Please Enter Username", vbInformation, "Username Required"
    Me.txtBoxUsername.SetFocus

ElseIf IsNull(Me.txtBoxPassword) Then

    MsgBox "Please Enter Password", vbInformation, "Password Required"
    Me.txtBoxPassword.SetFocus

Else
    'proccess the job
    If ((IsNull(DLookup("[Username]", "Staff Table", "[Username] = '" & Me.txtBoxUsername.Value & "'"))) Or _
(IsNull(DLookup("[Password]", "Staff Table", "[Password] = '" & Me.txtBoxPassword.Value & "'")))) Then
        MsgBox "Incorrect Username Or Password"
    Else
        DoCmd.OpenForm "Branch Form"
        DoCmd.OpenForm "Customer Form"
        DoCmd.OpenForm "Item Form"
        DoCmd.OpenForm "Order Form"
        DoCmd.OpenForm "Staff Form"
    End If

End If
End Sub

Logic seems to be good. Are you positive that `Me.txtBoxUsername.Value` and `Me.txtBoxPassword.Value` are what you think they are? I.e. when you debug their values are `RJ1`? — Brad, Jun 17 '16 at 13:42
Alternatively you could use a parameterized recordset to confirm the user/password, It doesn't have the risk of a user entering `'` and dlookup failing. Also when you get the confirmation recordset you'll also have other columns that might be useful like permissions. — Brad, Jun 17 '16 at 13:51
It looks like I could enter my username and my coworkers password and it would let me in. Shouldn't the `DLookups` be searching for matching entries on the same row in "Staff Table"? — MoondogsMaDawg, Jun 17 '16 at 14:01
@Brad Yes, `RJ1` is the username and password. `Me.txtBoxUsername.Value` and `Me.txtBoxPassword.Value` are both the same for this member of staff. — FutureProgrammer, Jun 17 '16 at 14:28
@ChristopherD. I'm not sure if that'll work, although the error is persistent so it cannot be tested as of yet. — FutureProgrammer, Jun 17 '16 at 14:30
Re the update to your question: did you put my code in the button's click event procedure ... the code which follows `Private Sub btnLogin_Click()`? — HansUp, Jun 17 '16 at 14:58

score 5 · Answer 1 · edited May 23 '17 at 12:31

As Christopher pointed out, this If expression evaluates whether the username and password is found in Staff Table, but not necessarily found in the same row. So if you submit the name of one user with another user's password, this logic would consider those values to be a valid combination:

'proccess the job
If ((IsNull(DLookup("Username", "Staff Table", "Username='" & Me.txtBoxUsername.Value & "'"))) Or _
    (IsNull(DLookup("[Password]", "Staff Table", "[Password]='" & Me.txtBoxPassword.Value & "'")))) Then
    ' Note: quotes added .....................................^.............................^

Make sure you check whether the username and password exist in the same row:

Dim strCriteria As String
strCriteria  = "Username='" & Me.txtBoxUsername.Value & "' AND [Password]='" & Me.txtBoxPassword.Value & "'"
Debug.Print strCriteria '<- inspect in Immediate window; Ctrl+g will take you there
If DCount("*", "Staff Table", strCriteria) = 0 Then
    MsgBox "Incorrect Username Or Password"
End If

Although that change may be logically correct, it could break when either the username or password contains an apostrophe. A less brittle approach would be a Recordset based on a parameter query:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim strSelect As String
strSelect = "SELECT Count(*) FROM [Staff Table]" & vbCrLf & _
    "WHERE Username=[pUser] AND [Password]=[pPWD];"
Set db = CurrentDb
Set qdf = db.CreateQueryDef(vbNullString, strSelect)
qdf.Parameters("pUser").Value = Me!txtBoxUsername.Value
qdf.Parameters("pPWD").Value = Me!txtBoxPassword.Value
If qdf.OpenRecordset(dbOpenSnapshot)(0) = 0 Then
    MsgBox "Incorrect Username Or Password"
End If

That returns an error once the 'Login' button is clicked, regardless of the username and password. Also, do the missing brackets in `"WHERE Username=[pUser] AND [Password]=[pPWD];"` contribute to this error? — FutureProgrammer, Jun 17 '16 at 14:47
Which line triggers the error, and what is the error message? — HansUp, Jun 17 '16 at 14:50
I'm not certain of which line although line 5 has missing brackets at the start and end of `Username`, as they are visible on the word `Password`. — FutureProgrammer, Jun 17 '16 at 14:53
I bracketed `Password` because it is a [reserved word](http://allenbrowne.com/AppIssueBadWord.html#p). `Username` is not a reserved word, but you can bracket it anyway if you wish ... that shouldn't make a difference. The error you're facing is due to something else. — HansUp, Jun 17 '16 at 14:56
Ok, that's not the problem. I'm not sure what is. Ill further investigate. — FutureProgrammer, Jun 17 '16 at 14:58
Solved, I didn't place the code under the Login button procedure. — FutureProgrammer, Jun 17 '16 at 15:08

MoondogsMaDawg · Accepted Answer · 2016-06-17T14:03:21.110

1

I think your DLookups are failing due to bad syntax. Try:

If ((IsNull(DLookup("[Username]", "Staff Table", "[Username] = '" & Me.txtBoxUsername.Value & "'"))) Or _
(IsNull(DLookup("[Password]", "Staff Table", "[Password] = '" & Me.txtBoxPassword.Value & "'")))) Then

edited Jun 17 '16 at 14:03

answered Jun 17 '16 at 13:57

MoondogsMaDawg

1,704
12
22

Just saw that your double quotes weren't concatenating the string correctly either. I have edited my answer. – MoondogsMaDawg Jun 17 '16 at 14:03
Brilliant, that worked. Once `RJ1` was input into both username and password fields a message was shown stating that the username and password are correct which then opens all 5 forms. :D – FutureProgrammer Jun 17 '16 at 14:33

"Incorrect Username & Password" Although Correct?

2 Answers2

Linked