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.

access_login_form_02

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.

access_login_form

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.