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

Username and password validation on Login Page in MS Access

Midzie
P: 25
Hi, anyone here could help me on my codes. I want to validate my username and password from my user table. If username is not found in user table a message should appear "Username Invalid", and If password is invalid a message should appear "Password is invalid." and if Username is correct and password is Invalid a messages should be "Password is invalid", if password is correct and username is wrong a message should appear "Password is invalid". Here's my code..I am using MS Access (VBA)ProgrammingThanks in advance


Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     Call Login
  3.  
  4. End Sub
  5.  
  6. Public Sub Login()
  7. On Error GoTo ErrorHandler:
  8.     If IsNull(txtUsername) = True Then 'Check Username
  9.         MsgBox "Username is required"
  10.         txtUsername.SetFocus
  11.     End If
  12.  
  13.     If IsNull(txtPassword) = True Then 'Check Password
  14.         MsgBox "Password is required"
  15.         txtPassword.SetFocus
  16.  
  17.      End If
  18.  
  19.      'If (txtUsername) = True And (txtPassword) = True Then
  20.  
  21.  
  22.     'Compare value of txtUsername with the saved EmployeeID in tblUser
  23.     If Me.txtUsername.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
  24.         strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
  25.         strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable
  26.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  27.         MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
  28.         'Close logon form and open Main page
  29.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  30.  
  31.  
  32.     Else
  33.         MsgBox "Invalid Username! Please try again.", vbOKOnly, "Invalid Username"
  34.         intLogAttempt = intLogAttempt + 1
  35.         txtUsername.SetFocus
  36.     End If
  37.  
  38.  
  39.     'Compare value of txtPassword with the saved Password in tblUser
  40.     If Me.txtPassword.Value = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
  41.         strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
  42.         strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable
  43.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  44.         MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
  45.         'Close logon form and open Main page
  46.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  47.  
  48.     Else
  49.         MsgBox "Invalid Password! Please try again.", vbOKOnly, "Invalid Password"
  50.         intLogAttempt = intLogAttempt + 1
  51.         txtPassword.SetFocus
  52.     End If
  53.  
  54.     'If the user enters incorrect password for 3 times database will shutdown
  55.     If intLogAttempt = 3 Then
  56.     MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
  57.         "Application will exit.", vbCritical, "Restricted Access!"
  58.         Application.Quit
  59.     End If
  60.  
  61. ErrorHandler:
  62. End Sub
Jan 31 '12 #1

✓ answered by TheSmileyCoder

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     Call Login
  3.  
  4. End Sub
  5.  
  6. Public Sub Login()
  7. On Error GoTo ErrorHandler:
  8.     'TSC: No need to compare with true.
  9.     If IsNull(txtUsername) Then 'Check Username
  10.         MsgBox "Username is required"
  11.         txtUsername.SetFocus
  12.         Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
  13.     End If
  14.  
  15.     If IsNull(txtPassword) Then 'Check Password
  16.         MsgBox "Password is required"
  17.         txtPassword.SetFocus
  18.         Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
  19.      End If
  20.  
  21.     'Compare value of txtUsername with the saved EmployeeID in tblUser
  22.     'TSC: Removed .Value, since .Value is the default property of a textbox, and as such is not needed. You can keep it if you want, I suppose its a matter of preference.
  23.  
  24.  
  25.     'TSC Completely rewrote following :Try to retrive password based on username
  26.     dim strPass as string
  27.     strPass=DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
  28.     'TSC Note the added & "" at the end. If the Dlookup returns a null (which could happen if there was a typo in the username, or if the username does not exist) then Null & ""="", so the null gets safely converted to an empty string.
  29.  
  30.     If strPass="" Then
  31.        'No match was found for username
  32.        Msgbox "Username not found. Please check that you typed your name correctly."
  33.        Exit Sub
  34.     End If
  35.  
  36.     if strPass<>txtPassword then
  37.       'Password does not match
  38.       MsgBox "The password you typed does not match the password in storage"
  39.       Exit Sub
  40.     End If
  41.  
  42.  
  43.  
  44.  
  45.    'If code has executed to this point it means that a valid username and password was supplied
  46.  
  47.     strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
  48.     strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") 'set the value of strRole declared as Global Variable
  49.  
  50.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  51.         MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
  52.         'Close logon form and open Main page
  53.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  54.  
  55.  
  56. ErrorHandler:
  57. End Sub
  58.  

Share this Question
Share on Google+
18 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     Call Login
  3.  
  4. End Sub
  5.  
  6. Public Sub Login()
  7. On Error GoTo ErrorHandler:
  8.     'TSC: No need to compare with true.
  9.     If IsNull(txtUsername) Then 'Check Username
  10.         MsgBox "Username is required"
  11.         txtUsername.SetFocus
  12.         Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
  13.     End If
  14.  
  15.     If IsNull(txtPassword) Then 'Check Password
  16.         MsgBox "Password is required"
  17.         txtPassword.SetFocus
  18.         Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
  19.      End If
  20.  
  21.     'Compare value of txtUsername with the saved EmployeeID in tblUser
  22.     'TSC: Removed .Value, since .Value is the default property of a textbox, and as such is not needed. You can keep it if you want, I suppose its a matter of preference.
  23.  
  24.  
  25.     'TSC Completely rewrote following :Try to retrive password based on username
  26.     dim strPass as string
  27.     strPass=DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
  28.     'TSC Note the added & "" at the end. If the Dlookup returns a null (which could happen if there was a typo in the username, or if the username does not exist) then Null & ""="", so the null gets safely converted to an empty string.
  29.  
  30.     If strPass="" Then
  31.        'No match was found for username
  32.        Msgbox "Username not found. Please check that you typed your name correctly."
  33.        Exit Sub
  34.     End If
  35.  
  36.     if strPass<>txtPassword then
  37.       'Password does not match
  38.       MsgBox "The password you typed does not match the password in storage"
  39.       Exit Sub
  40.     End If
  41.  
  42.  
  43.  
  44.  
  45.    'If code has executed to this point it means that a valid username and password was supplied
  46.  
  47.     strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
  48.     strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") 'set the value of strRole declared as Global Variable
  49.  
  50.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  51.         MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
  52.         'Close logon form and open Main page
  53.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  54.  
  55.  
  56. ErrorHandler:
  57. End Sub
  58.  
Jan 31 '12 #2

TheSmileyCoder
Expert Mod 100+
P: 2,321
A few extra things to note besides the code itself.

Storing passwords in plain text is not recommended. If anyone can get access to the tables they can view all the passwords of other issues. Knowing that users often reuse passwords, you might unintentionally not only compromise the safety of your own application but also of other applications.


Storing the values as global variables is a nice enough approach. You just need to be aware that ANY unhandled error anywhere in your database will result in those variables being reset to nothing.
Jan 31 '12 #3

Midzie
P: 25
Hi TheSmileyCoder, thanks for the reply. I still have an issue after implementing the program. I tried to supply a correct username and wrong password and the login page proceed. A message should appear "Invalid password" and login page should not proceed. Please help me again. Here's the revised code:
Expand|Select|Wrap|Line Numbers
  1. Public Sub Login()
  2. On Error GoTo ErrorHandler:
  3.     If IsNull(txtUsername) Then 'Check Username
  4.         MsgBox "Username is required"
  5.         txtUsername.SetFocus
  6.         Exit Sub
  7.     End If
  8.  
  9.     If IsNull(txtPassword) Then 'Check Password
  10.         MsgBox "Password is required"
  11.         txtPassword.SetFocus
  12.         Exit Sub
  13.      End If  
  14.  
  15.     'Compare value of txtUsername with the saved N_Number in tblUser
  16.     If Me.txtUsername.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
  17.         strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable to be displayed in Main Page
  18.         strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable to be displayed in Main Page
  19.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  20.         MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
  21.         'Close logon form and open Main page
  22.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  23.  
  24.  
  25.     Else
  26.         MsgBox "Invalid Username! Please try again.", vbOKOnly, "Invalid Username"
  27.         intLogAttempt = intLogAttempt + 1
  28.         txtUsername.SetFocus
  29.     End If
  30.  
  31.     Dim strPass As String
  32.     strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
  33.  
  34.     If strPass = "" Then
  35.     MsgBox "Username doesn't exist. Please try again."
  36.     Exit Sub
  37.     End If
  38.  
  39.     If strPass <> txtPassword Then
  40.     MsgBox "Invalid Password"
  41.     Exit Sub
  42.     End If
  43.  
  44.  
  45.     'If the user enters incorrect password for 3 times database will shutdown
  46.     If intLogAttempt = 3 Then
  47.     MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
  48.         "Application will exit.", vbCritical, "Restricted Access!"
  49.         Application.Quit
  50.     End If
  51.  
  52. ErrorHandler:
  53. End Sub
Jan 31 '12 #4

TheSmileyCoder
Expert Mod 100+
P: 2,321
Of course it does. You have re-arranged the code so that it opens the main form, BEFORE it actually checks the value of the provided password against the stored password.

Please read the following link:
guidelines: http://bytes.com/topic/access/answers/901142-how-ask-good-questions-read-before-submitting-question
And also this will give you some good info on how to debug your application:
http://bytes.com/topic/access/insigh...vba-1-overview
Jan 31 '12 #5

Midzie
P: 25
I'm sorry, what part of the code will I rearranged? Thanks.
Feb 2 '12 #6

TheSmileyCoder
Expert Mod 100+
P: 2,321
Look at the code I posted. Try to see the changes I made and give thought to why I did those changes. If there is something about the code I posted that you dont understand, return here with a question outlining the line of code causing you trouble.
Feb 2 '12 #7

Midzie
P: 25
Hi TheSmileyCoder, thanks for helping me. I followed your instruction, I got what I wanted it's working already....thanks a lot. I'm glad that I am learning a lot here. God bless us all:-)
Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdLogin_Click()
  2.     Call Login
  3. End Sub
  4.  
  5. Public Sub Login()
  6. On Error GoTo ErrorHandler:
  7.     If IsNull(txtUsername) Then 'Check Username
  8.         MsgBox "Username is required"
  9.         txtUsername.SetFocus
  10.         Exit Sub
  11.     End If
  12.  
  13.     If IsNull(txtPassword) Then 'Check Password
  14.         MsgBox "Password is required"
  15.         txtPassword.SetFocus
  16.         Exit Sub
  17.      End If
  18.  
  19.     Dim strPass As String
  20.     strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
  21.  
  22.     If strPass = "" Then
  23.         'No match was found for username
  24.         MsgBox "Username not found. Please check that you typed your name correctly."
  25.         Exit Sub
  26.     End If
  27.  
  28.     If strPass <> txtPassword Then
  29.         'Password does not match
  30.         MsgBox "Password you typed does not match the password in storage"
  31.         Exit Sub
  32.     End If
  33.  
  34.         strUser = Me.txtUsername 'Set the value of the strUser declared as Global variable to be displayed in the main page
  35.         strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'")
  36.  
  37.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  38.         MsgBox "Welcome to Main page!" & strUser, vbOKOnly, "Welcome"
  39.         'Close login form and open main page
  40.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  41.  
  42.     'If the user enters incorrect password for 3 times database will shutdown
  43.     If intLogAttempt = 3 Then
  44.     MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
  45.         "Application will exit.", vbCritical, "Restricted Access!"
  46.         Application.Quit
  47.     End If
  48.  
  49. ErrorHandler:
  50. End Sub
Feb 2 '12 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
I give up. You are obviously cutting and pasting code without bothering to try to understand what it does, or even trying to figure out what is going wrong yourself. If you want code written for you, hire someone.

If you want free advice, you are requested to do a certain amount of effort yourself, such as reading the articles I linked for you on debugging in VBA.

EDIT: Please disregard the above comments. It was my error, I must have been looking at the wrong post or something. I apologize.
Feb 2 '12 #9

Midzie
P: 25
Hi TheSmileyCoder, i still thank you for challenging me. Someday, I'll be a good programmer too, like you.
Feb 3 '12 #10

Midzie
P: 25
I rearranged the codes and removed the exit sub on condition if strPass = "" and if strPass<>, I used If elseif endif on it and added intLogAttempt = intLogAttempt + 1 on each condition so that it will call the condition "If user enters incorrect password for 3 times database will shutdown"
Feb 6 '12 #11

TheSmileyCoder
Expert Mod 100+
P: 2,321
Is it working like you want it to? Did you read the artivles i linked for you? Especially the one about debugging? There is a se tion in there about stepping through code that is very usefull (also) when you are new and trying to see what the code does.

Please post your revised code.
Feb 6 '12 #12

Midzie
P: 25
Yes, it is working already... I have read it, thanks. Here's the revised codes.

Expand|Select|Wrap|Line Numbers
  1.  Public Sub Login()
  2.  
  3. On Error GoTo ErrorHandler:
  4. 'Check if data is entered in the Username textbox
  5.     If IsNull(txtUsername) Or Me.txtUsername = "" Then
  6.         MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
  7.         txtUsername.SetFocus
  8.         Exit Sub
  9. 'Check if data is entered in the Password textbox
  10.     ElseIf IsNull(txtPassword) Or Me.txtPassword = "" Then
  11.         MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
  12.         txtPassword.SetFocus
  13.         Exit Sub
  14.     Else
  15.  
  16.         Dim response As String
  17.         Dim strPass As String
  18.         strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
  19.  
  20.         If strPass = "" Then
  21.          'No match was found for username
  22.              MsgBox "Username doesn't exist! Please try again.", vbOKOnly, "Invalid Username!"
  23.              intLogAttempt = intLogAttempt + 1
  24.              txtUsername.SetFocus
  25.  
  26.         ElseIf strPass <> txtPassword Then
  27.         'Password does not match
  28.             MsgBox "Invalid Password!", vbOKOnly, "Invalid Password!"
  29.             intLogAttempt = intLogAttempt + 1
  30.              txtPassword.SetFocus
  31.         End If
  32.  
  33.       'If the user enters incorrect password and username for 3 times database will shutdown  
  34.         If intLogAttempt = 3 Then
  35.             MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
  36.             "Application will exit.", vbCritical, "Restricted Access!"
  37.             Application.Quit
  38.      End If
  39.  
  40.     End If
  41.  
  42.          'Username and password are correct, system will open the Main page
  43.         strUser = Me.txtUsername 'Set the value of the strUser declared as Global variable to be displayed in the main page
  44.         strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") 'Set the value of the strRole declared as Global variable to be displayed in the main page
  45.  
  46.         DoCmd.Close acForm, "frmUserLogin", acSaveNo
  47.         MsgBox "Welcome to MainPage! " & strUser, vbOKOnly, "Welcome!"
  48.  
  49.         'Close login form and open Main page
  50.         DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
  51.  
  52.  
  53. ErrorHandler:
  54.  
  55. End Sub 
Feb 6 '12 #13

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi Midzie.

Its very nice to see you have worked on your code. Its clear that you have now understood some basic principles that were obviously lacking in your first attempts.

Keep working on it, and you will soon learn that VBA can perform wonders for your application.


If I should add one comment it would be in regards to your errorhandling. Its fine to exit without really doing anything, but in my oppinion you should as minimum inform the user of the error occuring. A simple modification at the bottom like so should suffice

Expand|Select|Wrap|Line Numbers
  1. Exit sub 'Add this line, otherwise the errorhandler code will run even when no error has occured.
  2. ErrorHandler:
  3.   MsgBox "An unexpected error has occured. Please contact an administrator" & vbnewline & _
  4.        Err.Number & " - " & Err.Description,vbokOnly+vbcritical,"Critical Error"
  5. End Sub
Feb 6 '12 #14

NeoPa
Expert Mod 15k+
P: 31,263
@Smiley - Did you see something in the post #8 code that was wrong? I got the impression Midzie was already happy by that point.

@Midzie - Good progress. It's always hard working in foreign languages, but you're putting things into practice and thinking logically. I notice you've even started using the [ CODE ] tags when posting code now. I see Smiley has been very helpful for you on this thread. You may like to select the post of his that you feel was the most directly responsible for answering the main question, and select the Best Answer button found with it. This enables others with a similar problem to find an answer more obviously and easily :-)
Feb 6 '12 #15

TheSmileyCoder
Expert Mod 100+
P: 2,321
Hi NeoPa, thanks for pointing that blunder out.

I think that perhaps when I followed the email link I received upon update of the thread with post #8 I accidentally looked at #4 and got the (wrong) impression that Midzie had not worked at all with the links provided, and thus got agitated and made my post #9.

To Midzie
I sincerely apologize for my unfounded outburst.
Feb 6 '12 #16

ADezii
Expert 5K+
P: 8,607
@Midzie:
One small point to mention is that, at least in my opinion, all Passwords should be Case-Sensitive. This will add another Layer of Security, and is actually something that most Users will expect and are accustomed to. The following Code will always make sure that only an 'Exact' Match will pass the Test:
Expand|Select|Wrap|Line Numbers
  1. Dim strPass As String
  2. Dim txtPassword As String
  3.  
  4. strPass = "Ren487deZvous"
  5. txtPassword = "Ren487dezvous"
  6.  
  7. 'Passwords will NOT MATCH because of the Lower Case 'z' in txtPassword
  8. If StrComp(strPass, txtPassword, vbBinaryCompare) Then
  9.   MsgBox "The Passwords are NOT an exact Match - Remember Passwords " & _
  10.          "are Case Sensitive."
  11.     Exit Sub
  12. End If
Feb 6 '12 #17

NeoPa
Expert Mod 15k+
P: 31,263
@Smiley - An unfortunate oversight. When you post as much as you do it's inevitable from time-to-time (I've made similar mistakes).

@ADezii - That's a good point. I would add to that, that passwords shouldn't be stored in plain text. That is to say that a 'processed' version should be stored in the table with the user details and any password being checked should also be 'processed' prior to the comparison. So :
Expand|Select|Wrap|Line Numbers
  1. 'strPass value from table - Stored pre-processed
  2. If StrComp(strPass, Process(Me.txtPassword), vbBinaryCompare) <> 0 Then
  3.     Call MsgBox("The Passwords are NOT an exact Match - " & 
  4.                 "Remember Passwords are Case Sensitive.")
  5.     Exit Sub
  6. End If
  7.  
  8. Private Function Process(strPW As String) As String
  9.     ' conversion / processing code here
  10. End Function
Clearly Process() is an unspecified function here, but it can be whatever you want it to be as long as it produces consistent results from the same input. Some links that may prove helpful for this are :
  1. AES Encryption Algorithm for VBA and VBScript.
  2. RC4 Encryption Algorithm for VBA and VBScript.
  3. SHA2 Cryptographic Hash Algorithm for VBA and VBScript.
Feb 6 '12 #18

Midzie
P: 25
Hi NeoPa/Smiley/ADezii - Thanks to all your efforts for helping me with my codes. More powers to all of you and to this website. To all beginners like me, let's keep on learning new things:-)
Feb 7 '12 #19

Post your reply

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