Username and password validation on Login Page in MS Access

[Midzie]

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

Private Sub cmdLogin_Click()
    Call Login
 
End Sub
 
Public Sub Login()
On Error GoTo ErrorHandler:
    If IsNull(txtUsername) = True Then 'Check Username
        MsgBox "Username is required"
        txtUsername.SetFocus
    End If
 
    If IsNull(txtPassword) = True Then 'Check Password
        MsgBox "Password is required"
        txtPassword.SetFocus
 
     End If
 
     'If (txtUsername) = True And (txtPassword) = True Then
 
 
    'Compare value of txtUsername with the saved EmployeeID in tblUser
    If Me.txtUsername.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
        strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
        strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
        'Close logon form and open Main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
 
    Else
        MsgBox "Invalid Username! Please try again.", vbOKOnly, "Invalid Username"
        intLogAttempt = intLogAttempt + 1
        txtUsername.SetFocus
    End If
 
 
    'Compare value of txtPassword with the saved Password in tblUser
    If Me.txtPassword.Value = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
        strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
        strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
        'Close logon form and open Main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
    Else
        MsgBox "Invalid Password! Please try again.", vbOKOnly, "Invalid Password"
        intLogAttempt = intLogAttempt + 1
        txtPassword.SetFocus
    End If
 
    'If the user enters incorrect password for 3 times database will shutdown
    If intLogAttempt = 3 Then
    MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
        "Application will exit.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
 
ErrorHandler:
End Sub

[TheSmileyCoder]

Private Sub cmdLogin_Click()
    Call Login
 
End Sub
 
Public Sub Login()
On Error GoTo ErrorHandler:
    'TSC: No need to compare with true.
    If IsNull(txtUsername) Then 'Check Username
        MsgBox "Username is required"
        txtUsername.SetFocus
        Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
    End If
 
    If IsNull(txtPassword) Then 'Check Password
        MsgBox "Password is required"
        txtPassword.SetFocus
        Exit Sub 'TSC: Added No reason to proceed with code when an error is discovered
     End If
 
    'Compare value of txtUsername with the saved EmployeeID in tblUser
    '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.
 
 
    'TSC Completely rewrote following :Try to retrive password based on username
    dim strPass as string
    strPass=DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
    '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.
 
    If strPass="" Then
       'No match was found for username
       Msgbox "Username not found. Please check that you typed your name correctly."
       Exit Sub
    End If
 
    if strPass<>txtPassword then
      'Password does not match
      MsgBox "The password you typed does not match the password in storage"
      Exit Sub
    End If
 
 
 
 
   'If code has executed to this point it means that a valid username and password was supplied
 
    strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable
    strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") 'set the value of strRole declared as Global Variable
 
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
        'Close logon form and open Main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
 
ErrorHandler:
End Sub
 

[TheSmileyCoder]

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.

[Midzie]

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:

Public Sub Login()
On Error GoTo ErrorHandler:
    If IsNull(txtUsername) Then 'Check Username
        MsgBox "Username is required"
        txtUsername.SetFocus
        Exit Sub
    End If
 
    If IsNull(txtPassword) Then 'Check Password
        MsgBox "Password is required"
        txtPassword.SetFocus
        Exit Sub
     End If  
 
    'Compare value of txtUsername with the saved N_Number in tblUser
    If Me.txtUsername.Value = DLookup("EmployeeID", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") Then
        strUser = Me.txtUsername.Value 'Set the value of the strUser declared as Global variable to be displayed in Main Page
        strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername.Value & "'") 'set the value of strRole declared as Global Variable to be displayed in Main Page
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to Main Page!" & strUser, vbOKOnly, "Welcome"
        'Close logon form and open Main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
 
    Else
        MsgBox "Invalid Username! Please try again.", vbOKOnly, "Invalid Username"
        intLogAttempt = intLogAttempt + 1
        txtUsername.SetFocus
    End If
 
    Dim strPass As String
    strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
 
    If strPass = "" Then
    MsgBox "Username doesn't exist. Please try again."
    Exit Sub
    End If
 
    If strPass <> txtPassword Then
    MsgBox "Invalid Password"
    Exit Sub
    End If
 
 
    'If the user enters incorrect password for 3 times database will shutdown
    If intLogAttempt = 3 Then
    MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
        "Application will exit.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
 
ErrorHandler:
End Sub

[TheSmileyCoder]

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

[Midzie]

I'm sorry, what part of the code will I rearranged? Thanks.

[TheSmileyCoder]

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.

[Midzie]

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:-)

Private Sub cmdLogin_Click()
    Call Login
End Sub
 
Public Sub Login()
On Error GoTo ErrorHandler:
    If IsNull(txtUsername) Then 'Check Username
        MsgBox "Username is required"
        txtUsername.SetFocus
        Exit Sub
    End If
 
    If IsNull(txtPassword) Then 'Check Password
        MsgBox "Password is required"
        txtPassword.SetFocus
        Exit Sub
     End If
 
    Dim strPass As String
    strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
 
    If strPass = "" Then
        'No match was found for username
        MsgBox "Username not found. Please check that you typed your name correctly."
        Exit Sub
    End If
 
    If strPass <> txtPassword Then
        'Password does not match
        MsgBox "Password you typed does not match the password in storage"
        Exit Sub
    End If
 
        strUser = Me.txtUsername 'Set the value of the strUser declared as Global variable to be displayed in the main page
        strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'")
 
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to Main page!" & strUser, vbOKOnly, "Welcome"
        'Close login form and open main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
    'If the user enters incorrect password for 3 times database will shutdown
    If intLogAttempt = 3 Then
    MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
        "Application will exit.", vbCritical, "Restricted Access!"
        Application.Quit
    End If
 
ErrorHandler:
End Sub

[TheSmileyCoder]

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.

[Midzie]

Hi TheSmileyCoder, i still thank you for challenging me. Someday, I'll be a good programmer too, like you.

[Midzie]

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"

[TheSmileyCoder]

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.

[Midzie]

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

 Public Sub Login()
 
On Error GoTo ErrorHandler:
'Check if data is entered in the Username textbox
    If IsNull(txtUsername) Or Me.txtUsername = "" Then
        MsgBox "Username is required", vbOKOnly, "Invalid Entry!"
        txtUsername.SetFocus
        Exit Sub
'Check if data is entered in the Password textbox
    ElseIf IsNull(txtPassword) Or Me.txtPassword = "" Then
        MsgBox "Password is required", vbOKOnly, "Invalid Entry!"
        txtPassword.SetFocus
        Exit Sub
    Else
 
        Dim response As String
        Dim strPass As String
        strPass = DLookup("Password", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") & ""
 
        If strPass = "" Then
         'No match was found for username
             MsgBox "Username doesn't exist! Please try again.", vbOKOnly, "Invalid Username!"
             intLogAttempt = intLogAttempt + 1
             txtUsername.SetFocus
 
        ElseIf strPass <> txtPassword Then
        'Password does not match
            MsgBox "Invalid Password!", vbOKOnly, "Invalid Password!"
            intLogAttempt = intLogAttempt + 1
             txtPassword.SetFocus
        End If
 
      'If the user enters incorrect password and username for 3 times database will shutdown  
        If intLogAttempt = 3 Then
            MsgBox "You do not have access to this database.Please contact admin." & vbCrLf & vbCrLf & _
            "Application will exit.", vbCritical, "Restricted Access!"
            Application.Quit
     End If
 
    End If
 
         'Username and password are correct, system will open the Main page
        strUser = Me.txtUsername 'Set the value of the strUser declared as Global variable to be displayed in the main page
        strRole = DLookup("Role", "tblUser", "[EmployeeID]='" & Me.txtUsername & "'") 'Set the value of the strRole declared as Global variable to be displayed in the main page
 
        DoCmd.Close acForm, "frmUserLogin", acSaveNo
        MsgBox "Welcome to MainPage! " & strUser, vbOKOnly, "Welcome!"
 
        'Close login form and open Main page
        DoCmd.OpenForm "frmMainPage", acNormal, "", "", , acNormal
 
 
ErrorHandler:
 
End Sub 

[TheSmileyCoder]

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

Exit sub 'Add this line, otherwise the errorhandler code will run even when no error has occured.
ErrorHandler:
  MsgBox "An unexpected error has occured. Please contact an administrator" & vbnewline & _
       Err.Number & " - " & Err.Description,vbokOnly+vbcritical,"Critical Error"
End Sub

[NeoPa]

@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 :-)

[TheSmileyCoder]

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.

[ADezii]

@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:

Dim strPass As String
Dim txtPassword As String
 
strPass = "Ren487deZvous"
txtPassword = "Ren487dezvous"
 
'Passwords will NOT MATCH because of the Lower Case 'z' in txtPassword
If StrComp(strPass, txtPassword, vbBinaryCompare) Then
  MsgBox "The Passwords are NOT an exact Match - Remember Passwords " & _
         "are Case Sensitive."
    Exit Sub
End If

[NeoPa]

@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 :

'strPass value from table - Stored pre-processed
If StrComp(strPass, Process(Me.txtPassword), vbBinaryCompare) <> 0 Then
    Call MsgBox("The Passwords are NOT an exact Match - " & 
                "Remember Passwords are Case Sensitive.")
    Exit Sub
End If
 
Private Function Process(strPW As String) As String
    ' conversion / processing code here
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.

[Midzie]

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:-)