By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
446,400 Members | 981 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 446,400 IT Pros & Developers. It's quick & easy.

Validate User password entry using VBA

markmcgookin
Expert 100+
P: 648
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.  
  3. End Sub
  4.  
Thanks people!

Mark
Dec 2 '06 #1
Share this Question
Share on Google+
15 Replies


nico5038
Expert 2.5K+
P: 3,072
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)
Dec 2 '06 #2

markmcgookin
Expert 100+
P: 648
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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.  
  3. IF IsNull(DLOOKUP("userPassword","tblUser","UserID=" & chr(34) & me.txtMemberName " chr(34))) then
  4.  
  5. ' not found
  6. MsgBox "Invalid Membername"
  7. End Sub
  8. Else
  9.  
  10. if DLOOKUP("userPassword","tblUser","UserID=" & chr(34) & me.txtMemberName " chr(34)) <> Me.txtPassword then
  11.  
  12. ' wrong password
  13. MsgBox "Wrong password"
  14. End Sub
  15. Else
  16.  
  17. ' OK
  18. End If
  19. End If
  20.  
  21. End Sub
  22.  
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)
Dec 2 '06 #3

markmcgookin
Expert 100+
P: 648
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
Dec 2 '06 #4

markmcgookin
Expert 100+
P: 648
My code now reads
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.  
  3. If IsNull(DLookup("userPassword", "tblUser", "UserID=" & Me.txtMembername)) Then
  4.  
  5. ' not found
  6. MsgBox "Invalid Membername"
  7. End Sub
  8.  
  9. Else
  10. If DLookup("userPassword", "tblUser", "UserID=" & Me.txtMembername) <> Me.txtPassword Then
  11.  
  12. ' wrong password
  13. MsgBox "Wrong password"
  14. End Sub
  15.  
  16. Else
  17. ' OK
  18.  
  19. Text6.Text = "Login ok"
  20.  
  21. End If
  22. End If
  23. End Sub
  24.  
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!
Dec 2 '06 #5

nico5038
Expert 2.5K+
P: 3,072
OK, try this checked coding:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.  
  3. If IsNull(DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34))) Then
  4.    ' not found
  5.    MsgBox "Invalid Membername"
  6.    Exit Sub
  7. Else
  8.    If DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34)) <> Me.txtPassword Then
  9.       ' wrong password
  10.       MsgBox "Wrong password"
  11.       Exit Sub
  12.    Else
  13.       ' OK
  14.       DoCmd.OpenForm "frmForMenu"
  15.    End If
  16. End If
  17.  
  18. End Sub
  19.  
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)
Dec 2 '06 #6

NeoPa
Expert Mod 15k+
P: 31,661
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.     Dim strPW As String
  3.  
  4.     strPW = Nz(DLookup("userPassword", _
  5.                        "tblUser", _
  6.                        "UserID='" & me.txtUN & "'"), _
  7.                "EmptyPassword")
  8.     If strPW = "EmptyPassword" Then
  9.         'User not found
  10.         MsgBox "Invalid Username"
  11.     ElseIf strPW <> txtPassword Then
  12.         'Invalid Password
  13.         MsgBox "Wrong password"
  14.     Else
  15.         'All OK
  16.         Call DoCmd.OpenForm("frmForMenu")
  17.     End If
  18. 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.
Dec 2 '06 #7

markmcgookin
Expert 100+
P: 648
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:
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.  
  3. If IsNull(DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34))) Then
  4.    ' not found
  5.    MsgBox "Invalid Membername"
  6.    Exit Sub
  7. Else
  8.    If DLOOKUP("userPassword", "tblUser", "UserID=" & chr(34) & Me.txtMembername & chr(34)) <> Me.txtPassword Then
  9.       ' wrong password
  10.       MsgBox "Wrong password"
  11.       Exit Sub
  12.    Else
  13.       ' OK
  14.       DoCmd.OpenForm "frmForMenu"
  15.    End If
  16. End If
  17.  
  18. End Sub
  19.  
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)
Dec 2 '06 #8

NeoPa
Expert Mod 15k+
P: 31,661
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 :).
Dec 3 '06 #9

P: 36
There were a few typo probs with Nico's code.
Here's a modified version :
Expand|Select|Wrap|Line Numbers
  1. Private Sub btnLogin_Click()
  2.     Dim strPW As String
  3.  
  4.     strPW = Nz(DLookup("userPassword", _
  5.                        "tblUser", _
  6.                        "UserID='" & me.txtUN & "'"), _
  7.                "EmptyPassword")
  8.     If strPW = "EmptyPassword" Then
  9.         'User not found
  10.         MsgBox "Invalid Username"
  11.     ElseIf strPW <> txtPassword Then
  12.         'Invalid Password
  13.         MsgBox "Wrong password"
  14.     Else
  15.         'All OK
  16.         Call DoCmd.OpenForm("frmForMenu")
  17.     End If
  18. 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?
Dec 3 '06 #10

markmcgookin
Expert 100+
P: 648
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!
Dec 3 '06 #11

nico5038
Expert 2.5K+
P: 3,072
Looks OK to me, the NZ(<string>,<value used when string is Null>) does indeed work as assumed.

Nic;o)
Dec 3 '06 #12

P: 41
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.
Jan 23 '07 #13

P: 41
Re,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Login_Password_Click()
  2.  
  3.     If IsNull(txtPassword) Then
  4.     txtPassword = "*"
  5.     End If
  6.  
  7.     Dim strPW As String
  8.  
  9.     strPW = Nz(DLookup("Password", _
  10.                        "tblUsername", _
  11.                        "Username='" & Me.txtUsername & "'"), _
  12.                        "EmptyPassword")
  13.     If strPW = "EmptyPassword" Then
  14.  
  15.         MsgBox "Invalid Username"
  16.  
  17.     ElseIf strPW <> txtPassword Then
  18.  
  19.         MsgBox "Wrong Password"
  20.     Else
  21.  
  22.         Call DoCmd.RunMacro("DELETE_OK")
  23.     End If
  24.  
  25. 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.
Jan 23 '07 #14

markmcgookin
Expert 100+
P: 648
Re,

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub Login_Password_Click()
  2.  
  3.     If IsNull(txtPassword) Then
  4.     txtPassword = "*"
  5.     End If
  6.  
  7.     Dim strPW As String
  8.  
  9.     strPW = Nz(DLookup("Password", _
  10.                        "tblUsername", _
  11.                        "Username='" & Me.txtUsername & "'"), _
  12.                        "EmptyPassword")
  13.     If strPW = "EmptyPassword" Then
  14.  
  15.         MsgBox "Invalid Username"
  16.  
  17.     ElseIf strPW <> txtPassword Then
  18.  
  19.         MsgBox "Wrong Password"
  20.     Else
  21.  
  22.         Call DoCmd.RunMacro("DELETE_OK")
  23.     End If
  24.  
  25. 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
Jan 23 '07 #15

P: 41
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.
Jan 24 '07 #16

Post your reply

Sign in to post your reply or Sign up for a free account.