Login Form Problem with VBA

[data808]

I have a login form that works really good except for one part of it. The security level works and the fact that I can create a new password if the first password I set it to is "password". However, there is one flaw that I don't seem to understand. If I have 5 users in my table that stores all the usernames and passwords, I will be able to use any of the 5 usernames with any of the 5 passwords listed. In other words it does not verify and match that the username and password must match together for the user to log in. The user can just use their username with any of the 5 passwords that are in this table. How do I change this code to make it work so that it will match up the username and passwords with each other before going any further in the database?

Private Sub cmdLogin_Click()
Dim UserLevel As Integer


Dim TempPass As String
Dim ID As Integer

If IsNull(Me.txtUsername) Then
MsgBox "Please Enter Username", vbInformation, "Username Required"
Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Password Required"
Me.txtPassword.SetFocus
Else
'process the job
If (IsNull(DLookup("Username", "tblUser", "Username='" & Me.txtUsername.Value & "'"))) Or _
(IsNull(DLookup("Password", "tblUser", "Password ='" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect Username Or Password"
Me.txtUsername.SetFocus
Else
UserLevel = DLookup("UserSecurity", "tblUser", "Username = '" & Me.txtUsername.Value & "'")
TempPass = DLookup("Password", "tblUser", "Username = '" & Me.txtUsername.Value & "'")
ID = DLookup("UserID", "tblUser", "Username = '" & Me.txtUsername.Value & "'")
DoCmd.Close
If (TempPass = "Password") Then
MsgBox "Please Change Password", vbInformation, "New Password Required!"
DoCmd.OpenForm "Change Password", , , "[UserID] = " & ID
Else
If UserLevel = 1 Then
DoCmd.OpenForm "Admin Main Menu", acNormal, , , acFormAdd
Else
DoCmd.OpenForm "LSTS Main Menu", acNormal, , , acFormAdd
End If
End If
End If
End If
End Sub

[pbaldy]

Personally I'd use a recordset rather than all the DLookup() functions. That said, you'd need your DLookup() to get the password for the user entered, and compare that with the password entered. Your first 2 DLookup's make no sense.

[Xipooo]

I agree with Paul, using a recordset would be better.

private sub CheckLogin()
dim db as database
dim rs as recordset
set db = currentdb
set rs = db.OpenRecordset("SELECT UserID, Username, UserSecurity FROM tblUser WHERE UserName='" & me.txtUsername & "'", dbOpenSnapshot, dbReadOnly)
if rs.eof then 
msgbox "Username Not Found!", vbCritical, "Ooops!"
me.txtUsername.setfocus()
Exit Sub
elseif rs!password <> me.txtPassword
msgbox "Incorrect Password!", vbCritical, "Ooops!"
me.txtPassword.setfocus()
Exit Sub
elseif rs!UserLevel = 1 then
DoCmd.OpenForm "Admin Main Menu", acNormal, , , acFormAdd
Exit Sub
Else
DoCmd.OpenForm "LSTS Main Menu", acNormal, , , acFormAdd
end if
End Sub

[data808]

That's what I was thinking earlier today. It needs to check the record and make sure the data for the username and password is coming from the same record to go further. How do I implement that into the code. Sorry I found this code online and tried to make it work for me. Its almost there.

Yeah maybe the first DLookup's are the culprit as to why it will take any password with any username. Its not specific enough to make it match what is in just one record. Can you assist me?

[Xipooo]

In your onLoad event just call CheckLogin.

[data808]

I tried putting your code into my Login button but there are a bunch of errors in your code. I tried messing with some of it to fill in the parts I thought would make it compile but you got (), rs.password (which it can't find), seems like its missing some "Then" statements, etc...

I also noticed you have it under "CheckLogin()". What event is that? What am I doing wrong?

[data808]

how do you call CheckLogin from On Load event?

[data808]

Anyone know why I get problems with this line from the code suggested above from Xipooo?

elseif rs!password <> me.txtPassword

I'm putting the code behind the login button but Xipooo has it under a CheckLogin() event which Xipooo says to call it under the OnLoad event but I have no idea how to do that. How do you "call" it?

[pbaldy]

What does "problems" mean exactly? What exactly is your code?

[Xipooo]

You changed the line from rs.password to rs!password right? I found my mistake and fixed it within about 10 seconds of the first time I posted it. Try copying it again.

You call the subroutine by just typing it where in your code you want it to run.

private sub cmdLogin_Click()
CheckLogin
end sub

[Xipooo]

What does "problems" mean exactly? What exactly is your code?

Initially I wrote the code with dot notation in my recordset. I fixed it within just a few seconds of posting it, but apparently he copied it before I fixed it.

I'm so used to using Entity Framework to get my data I sometimes make syntax errors with drilling down to my data.

[robrich22]

I posted a solution in the other Thread. But here is another one.

Dim valid As Integer: valid = DCount("Username", "[tblUsers]", "[Username] ='" & txtUsername & "' AND [Password]='" & txtPassword & "'")
    If valid = 1 Then
        ' Success.. Open Form Stuff
    Else
        MsgBox "Invalid Username or Password"
    End If

[data808]

The code is the one above from Xipooo.

private sub CheckLogin()

dim db as database
dim rs as recordset
set db = currentdb
set rs = db.OpenRecordset("SELECT UserID, Username, UserSecurity FROM tblUser WHERE UserName='" & me.txtUsername & "'", dbOpenSnapshot, dbReadOnly)
if rs.eof then

msgbox "Username Not Found!", vbCritical, "Ooops!"
me.txtUsername.setfocus()
Exit Sub

elseif rs!password <> me.txtPassword

msgbox "Incorrect Password!", vbCritical, "Ooops!"
me.txtPassword.setfocus()
Exit Sub

elseif rs!UserLevel = 1 then

DoCmd.OpenForm "Admin Main Menu", acNormal, , , acFormAdd
Exit Sub

Else

DoCmd.OpenForm "LSTS Main Menu", acNormal, , , acFormAdd

end if

End Sub

[data808]

Hey Xipooo,

yes I did notice that change too. I thought I was going crazy when I saw the "!" changed from "." I could have sworn I saw a period there. Haha

Anyway, yes I did make the change but still got an error from that line. I was also having problems with the setfocus commands. You have () after the setfocus and it would not allow it so I had to get rid of the parenthesis.

Other than that, its mostly having problems with that one line:
elseif rs!password <> me.txtPassword

Something about not having it in the collection. So I change the elseif to just else then drop down a line and put the if with no success. I even tried putting brackets and parenthesis around the password like this: rs!["password"] or rs!("password") or rs!"password", etc... but none of them worked. So I have no clue why it can't recognize that rs is the recordset and we are trying to pull from it with the ! just the password part of it since rs is set to all these fields: db.OpenRecordset("SELECT UserID, Username, UserSecurity FROM tblUser WHERE UserName='" & me.txtUsername & "'", dbOpenSnapshot, dbReadOnly).

I have no idea why its not working.

[data808]

Ok I will try it but I do not have my files right now so it will be later on tonight.

Are you sure nothing is wrong with this line though:
elseif rs!password <> me.txtPassword

It seems like something is not agreeing with the VBA editor in that line. Not sure why.