Username and password validation on Login Page in MS Access

**TheSmileyCoder** · Jan 31 '12, 10:37 AM

Code:

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** · Jan 31 '12, 10:40 AM

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** · Jan 31 '12, 10:58 AM

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:

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** · Jan 31 '12, 11:48 AM

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:

503 Service Unavailable

http://bytes.com/topic/access/insights/738863-debugging-vba-1-overview

**Midzie** · Feb 2 '12, 01:31 PM

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

**TheSmileyCoder** · Feb 2 '12, 02:02 PM

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** · Feb 2 '12, 02:43 PM

Hi TheSmileyCoder, thanks for helping me. I followed your instruction, I got what I wanted it's working already....than ks a lot. I'm glad that I am learning a lot here. God bless us all:-)

Code:

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** · Feb 2 '12, 03:00 PM

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** · Feb 3 '12, 09:38 AM

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

**Midzie** · Feb 6 '12, 05:27 AM

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** · Feb 6 '12, 06:19 AM

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** · Feb 6 '12, 06:30 AM

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

Code:

 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** · Feb 6 '12, 08:46 AM

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

Code:

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** · Feb 6 '12, 10:49 AM

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

Username and password validation on Login Page in MS Access

Username and password validation on Login Page in MS Access

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment