Access user login Form and verify password

This Access tutorial demonstrates how to create user login Form, verify password, create user level / privilege

You may also want to read:

Access Form mask Password Text Box

Auto Run Access Form using AutoExec

Access user login Form

There are many tutorials in Google about making user login Form, some of them make a lot of error handling and conditions. The one I am going to demonstrate must not be the best, but will fulfill your basic requirements.

Step 1 – Create a Table

Create a table that store the verification information. User_Type is to navigate to different Form for different people.

Step 2 – Create a login Form

Create a Form called login. In the Design View, you just need to make three Control:

– two Text Box, one is for login ID and the other is for password.

– login Button to verify password and navigate to the next page

Below is an example of a login Form. I name the User ID Text Box as tb_ID, password as tb_pwd.

Step 3 – Login Form Load Event

Insert the below code in Load Event of Form. Everytime you load the login Form, login ID and password are cleared.

The insideHeight and insideWidth is to set Windows size for Pop Up Form.

Private Sub Form_Load()
    InsideHeight = 4000
    InsideWidth = 6000
    Me.tb_ID = Null
    Me.tb_pwd = Null
    Me.tb_ID.SetFocus
End Sub

Step 4 – Login Form Button Click Event: Check Null login ID or password

When the Login button is clicked, the first thing we want to check is whether the user has not input any value. This step is optional because you will verify the password anyway.

The below code will prompt user a MsgBox if either password or login ID is Null.

    If IsNull(Me.tb_ID) Or isNull(Me.tb_pwd)  Then
      MsgBox "You must enter password or login ID.", vbOKOnly + vbInformation, "Required Data"
      Me.tb_ID.SetFocus
        Exit Sub
    End If

Step 5 – Login Form Button Click Event: Verify password

To verify password and login ID, the simplest way is to use DLookup Function. DLookup is quite similar to VLookup in Excel except that DLookup is used in Table or Query. The most difficult part of DLookup is to join a variable from Text Box in the criteria argument. Criteria argument is a Text Form, therefore you need to double quote the whole argument.

For example, “[Empl ID] = 001”

However, since data type of 001 is a Text, you need a single quote inside double quote.

“[Empl ID] = ‘001’”

The below code tests whether the password in login ID and tb_pwd combination is correct, returning TRUE or FALSE.

Me.tb_pwd.Value = DLookup("[Login_Pwd]", "login_tbl", "[Empl ID]='" & Me.tb_ID.Value & "'")

Make use of the above checking, add the followings

– If verification returns FALSE, prompt Msgbox

– If verification returns TRUE, the login Form is closed and open another Form called “user_menu”

    If Me.tb_pwd.Value = DLookup("[Login_Pwd]", "login_tbl", "[Empl ID]='" & Me.tb_ID.Value & "'") Then
        DoCmd.Close acForm, "login", acSaveNo
        DoCmd.openForm "user_menu"
    Else
      MsgBox "Password or login ID incorrect. Please Try Again", vbOKOnly + vbExclamation, "Invalid Entry!"
      Me.tb_pwd.SetFocus
    End If

If you want to open different Form depending on User_Type, add one more checking with DLookup. The complete code in Button Click Event is summarized below.

Private Sub frm1Btn_Click()    
    If IsNull(Me.tb_ID) Or IsNull(Me.tb_pwd) Then
      MsgBox "You must enter password and login ID.", vbOKOnly + vbInformation, "Required Data"
        Me.tb_ID.SetFocus
        Exit Sub
    End If

    If Me.tb_pwd.Value = DLookup("[Login_Pwd]", "login_tbl", "[Empl ID]='" & Me.tb_ID.Value & "'") Then
        If DLookup("[User_Type]", "login_tbl", "[Empl ID]='" & Me.tb_ID.Value & "'") = "Admin" Then
            DoCmd.openForm "admin_menu"
            DoCmd.Close acForm, "login", acSaveNo
        Else
            DoCmd.openForm "user_menu"
            DoCmd.Close acForm, "login", acSaveNo
        End If
    Else
      MsgBox "Password or login ID incorrect. Please Try Again", vbOKOnly + vbExclamation, "Invalid Entry!"
      Me.tb_pwd.SetFocus
    End If
End Sub

Instead of opening different Form for different user, you may also use Navigation Form and hide specific tab.