Validate User password entry using VBA

[markmcgookin]

Hi Folks, thanks in advance for your time.

I'm pretty new to Access VB so you will have to bear with me.

I have a table tblUser
(userID{PK}, userType, userPassword, userName, Status, numberOfJobs, skillID)

and I have an access form (frmLogin) which contains a Login button

and I am trying to get the form to read in from two text boxes (txtUN, textPW) and search tblUser for the UN (from the textbox), then compare the password to the userPassword, and if they are correct, navigate to one of two other forms depending on the userType.

Now my code theory is fine, but it is just trying to get this to connect to the DB which is never mentioned in these threads... Now I was wondering if one of ou would be kind enough to post some code here to help me, I have tried, unsuccessfully, to populate a recordset with a query (to search) or the =Dlookup() and I can't get wither to work.

Would someone be able to post ALL the code, not just appropriate lines, as I feel I am missing something integeral, yet insanely simple like CurrentDB=DAO.Database or something, so assume I have no globals as yet and am just looking at

Private Sub btnLogin_Click()
 
End Sub
 

Thanks people!

Mark

[nico5038]

The easy way will be to use the DLOOKUP("fieldname","tablename","where condition") function like:

IF IsNull(DLOOKUP("password","tblMembers","MemberName =" & chr(34) & me.txtMembername " chr(34))) then
' not found
msgbox "Invalid Membername"
end sub
else
if DLOOKUP("password","tblMembers","MemberName=" & chr(34) & me.txtMembername " chr(34)) <> Me.txtPassword then
' wrong password
msgbox "Wrong password"
end sub
else
' OK
endif
endif

The chr(34) is used to neutralize quotes in membernames like O'Neily.

Getting the idea ?

Nic;o)

[markmcgookin]

I keep getting a

"Compile error:

Expected: List separator or )"

Error, something I kept getting myself when I tried.

Any Ideas?

The code has only been changed to suit my tables/values

Private Sub btnLogin_Click()
 
IF IsNull(DLOOKUP("userPassword","tblUser","UserID=" & chr(34) & me.txtMemberName " chr(34))) then
 
' not found
MsgBox "Invalid Membername"
End Sub
Else
 
if DLOOKUP("userPassword","tblUser","UserID=" & chr(34) & me.txtMemberName " chr(34)) <> Me.txtPassword then
 
' wrong password
MsgBox "Wrong password"
End Sub
Else
 
' OK
End If
End If
 
End Sub
 

The easy way will be to use the DLOOKUP("fieldname","tablename","where condition") function like:

IF IsNull(DLOOKUP("password","tblMembers","MemberName =" & chr(34) & me.txtMembername " chr(34))) then
' not found
msgbox "Invalid Membername"
end sub
else
if DLOOKUP("password","tblMembers","MemberName=" & chr(34) & me.txtMembername " chr(34)) <> Me.txtPassword then
' wrong password
msgbox "Wrong password"
end sub
else
' OK
endif
endif

The chr(34) is used to neutralize quotes in membernames like O'Neily.

Getting the idea ?

Nic;o)

[markmcgookin]

NB: If you are (really) interested the .mdb can be found here:

http://www.cmdclan.co.uk/Temp/jr_system.mdb

And it is the frmLogin we are working on.

Mark

[markmcgookin]

My code now reads

Private Sub btnLogin_Click()
 
If IsNull(DLookup("userPassword", "tblUser", "UserID=" & Me.txtMembername)) Then
 
' not found
MsgBox "Invalid Membername"
End Sub
 
Else
If DLookup("userPassword", "tblUser", "UserID=" & Me.txtMembername) <> Me.txtPassword Then
 
' wrong password
MsgBox "Wrong password"
End Sub
 
Else
' OK
 
Text6.Text = "Login ok"
 
End If
End If
End Sub
 

and I am getting no errors when I click the button, but nothing is happening with correct or incorrect data being input, the button just clicks with no actions

Help pls!

[nico5038]

OK, try this checked coding:

Private Sub btnLogin_Click()
 
If IsNull(DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34))) Then
   ' not found
   MsgBox "Invalid Membername"
   Exit Sub
Else
   If DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34)) <> Me.txtPassword Then
      ' wrong password
      MsgBox "Wrong password"
      Exit Sub
   Else
      ' OK
      DoCmd.OpenForm "frmForMenu"
   End If
End If
 
End Sub
 

Mistake was a " where a & should be used and the END should have been an EXIT.

BTW best to use the password mask on the password field, thus it will show like "*****" when the password is entered, but the value can be used in the code.

Nic;o)

[NeoPa]

I keep getting a

"Compile error:

Expected: List separator or )"

Error, something I kept getting myself when I tried.

Any Ideas?

The code has only been changed to suit my tables/values

There were a few typo probs with Nico's code.
Here's a modified version :

Private Sub btnLogin_Click()
    Dim strPW As String
 
    strPW = Nz(DLookup("userPassword", _
                       "tblUser", _
                       "UserID='" & me.txtUN & "'"), _
               "EmptyPassword")
    If strPW = "EmptyPassword" Then
        'User not found
        MsgBox "Invalid Username"
    ElseIf strPW <> txtPassword Then
        'Invalid Password
        MsgBox "Wrong password"
    Else
        'All OK
        Call DoCmd.OpenForm("frmForMenu")
    End If
End Sub

PS. Just seen Nico's next version so I nicked his DoCmd in the OK bit and retro-fitted it.
Knowing Nico, his code should work anyway.

[markmcgookin]

HAHA!

Worked perfectly! Thanks very much!

Ur a legend (*HUGS*)

My specialaties are Java, .ASP and MySQL (in Oracle) if you need any help give me a sou anytime!

Mark

OK, try this checked coding:

Private Sub btnLogin_Click()
 
If IsNull(DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34))) Then
   ' not found
   MsgBox "Invalid Membername"
   Exit Sub
Else
   If DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34)) <> Me.txtPassword Then
      ' wrong password
      MsgBox "Wrong password"
      Exit Sub
   Else
      ' OK
      DoCmd.OpenForm "frmForMenu"
   End If
End If
 
End Sub
 

Mistake was a " where a & should be used and the END should have been an EXIT.

BTW best to use the password mask on the password field, thus it will show like "*****" when the password is entered, but the value can be used in the code.

Nic;o)

[NeoPa]

Good for you Mark.
Your question was a pleasure to look at, the question having all the relevant information to hand in the original post :).

[omozali]

There were a few typo probs with Nico's code.
Here's a modified version :

Private Sub btnLogin_Click()
    Dim strPW As String
 
    strPW = Nz(DLookup("userPassword", _
                       "tblUser", _
                       "UserID='" & me.txtUN & "'"), _
               "EmptyPassword")
    If strPW = "EmptyPassword" Then
        'User not found
        MsgBox "Invalid Username"
    ElseIf strPW <> txtPassword Then
        'Invalid Password
        MsgBox "Wrong password"
    Else
        'All OK
        Call DoCmd.OpenForm("frmForMenu")
    End If
End Sub

PS. Just seen Nico's next version so I nicked his DoCmd in the OK bit and retro-fitted it.
Knowing Nico, his code should work anyway.

could you plz explain this code for me?

[markmcgookin]

could you plz explain this code for me?

(Lines with * should be deleted, they are just comments, not even VB comments ( ' ) and they refer to the line of code above)

Private Sub btnLogin_Click()
*The Subroutine to be performed when my button, called btnLogin is clicked*

Dim strPW As String
*Declaring the variable PW of type "String"*

strPW = Nz(DLookup("userPassword", _
"tblUser", _
"UserID='" & me.txtUN & "'"), _
"EmptyPassword")

*I dont know what the "NZ() does here, but the rest of the code uses Dlookup to search the database for a vaule of userPassword, from tblUser table, where the UserID is the same as the value in the text box on the same form (hence me.txtUN) txtUN, and I am assuming that EmptyPassword is the vaule it is forced to return if it is null*

If strPW = "EmptyPassword" Then
'User not found
MsgBox "Invalid Username"
*If the returned value is EmptyPassword then display a msgbox saying Invalid Username*

ElseIf strPW <> txtPassword Then
'Invalid Password
MsgBox "Wrong password"
*If is does not equal the value in the table (<> means not eequal to) then show a message box saying Wrong password*


Else
'All OK
Call DoCmd.OpenForm("frmForMenu")
*Otherwise, if both fields match, open then form called frmForMenu*
End If
End Sub

*End the IF statement, and end the Sub routine*

Hope I got it right, and hope it helps!

[nico5038]

Looks OK to me, the NZ(<string>,<value used when string is Null>) does indeed work as assumed.

Nic;o)

[Gari]

Hello,

I have used this code for implementing password on the access database I am building and it works well except for one situation:

If I input a correct username, but I leave the password Textbox empty, it still performs the programmed action as if the username/password were correct and matching.

How come ? Any Idea ?

Thank you for your answer and best regards,

G.

[Gari]

Re,

I have resolved my issue by inserting those lines (bold):

Private Sub Login_Password_Click()
 
     If IsNull(txtPassword) Then
    txtPassword = "*"
    End If
 
    Dim strPW As String
 
    strPW = Nz(DLookup("Password", _
                       "tblUsername", _
                       "Username='" & Me.txtUsername & "'"), _
                       "EmptyPassword")
    If strPW = "EmptyPassword" Then
 
        MsgBox "Invalid Username"
 
    ElseIf strPW <> txtPassword Then
 
        MsgBox "Wrong Password"
    Else
 
        Call DoCmd.RunMacro("DELETE_OK")
    End If
 
End Sub

But is there any other better way to do it ?

And btw, is the password case-sensitive here?

Thank you for your replies.

Best regards,

G.

[markmcgookin]

Re,

I have resolved my issue by inserting those lines (bold):

Private Sub Login_Password_Click()
 
     If IsNull(txtPassword) Then
    txtPassword = "*"
    End If
 
    Dim strPW As String
 
    strPW = Nz(DLookup("Password", _
                       "tblUsername", _
                       "Username='" & Me.txtUsername & "'"), _
                       "EmptyPassword")
    If strPW = "EmptyPassword" Then
 
        MsgBox "Invalid Username"
 
    ElseIf strPW <> txtPassword Then
 
        MsgBox "Wrong Password"
    Else
 
        Call DoCmd.RunMacro("DELETE_OK")
    End If
 
End Sub

But is there any other better way to do it ?

And btw, is the password case-sensitive here?

Thank you for your replies.

Best regards,

G.

Hey thanks very much for that, I actually had never tested that with a null value, and had never noticed the error!

And no, I dont think access is case sensitive

[Gari]

Most welcome and thank you for the answer about case sensitiveness.

I have inserted those lines, being the best way I found to get rid of the problem. But I guess this is just a "trick". So, if someone knows a better way of coding that...

Best regards,

G.