469,336 Members | 5,570 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,336 developers. It's quick & easy.

Validate User password entry using VBA

markmcgookin
648 Expert 512MB
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
15 7249
nico5038
3,080 Expert 2GB
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
648 Expert 512MB
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
648 Expert 512MB
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
648 Expert 512MB
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
3,080 Expert 2GB
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
32,182 Expert Mod 16PB
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
648 Expert 512MB
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
32,182 Expert Mod 16PB
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
omozali
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
648 Expert 512MB
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
3,080 Expert 2GB
Looks OK to me, the NZ(<string>,<value used when string is Null>) does indeed work as assumed.

Nic;o)
Dec 3 '06 #12
Gari
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
Gari
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
648 Expert 512MB
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
Gari
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.

Similar topics

3 posts views Thread by arktikturtle | last post: by
4 posts views Thread by Wysiwyg | last post: by
2 posts views Thread by shapper | last post: by
1 post views Thread by Kodiak | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
1 post views Thread by Marylou17 | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.