Part 2 – Setting up User Authentication in Access

After designing the Ultimate Login Form, you need to create a user table for setting up user authentication to function. To get the functionality of the login form, you have to create a second form like the main dashboard which contains a logout functionality so the user can redirect to the login form after logging off.

Steps to Setting Up User Authentication

1. Create a User Table

Create a User table with these fields:

• First of all, create a table in the design view from “Create Menu”
• Use data type “Lookup Wizard” for the drop-down menu of Field Name “Gender”
• The first ID field must contain a Primary Key for a unique identifier

Fill data with some demo records.

2. Dashboard Form with Logout

Create a Main Dashboard demo form with a Logout button. The user will be redirected to the Login Form after successful logoff.

Hence, the Main Dashboard form will be the first appearance for the user after login, you can create a Multi-User with separate dashboard forms. For example, the Admin Dashboard will be open for Admin accounts and the User Dashboard will be open for Users. Later on, you can create User-level authentications as well. Refer to the Expense Management Project to assign a Multi-User System.

3. Login Button Functionality

Before Login Button On click event code, you need to make a label for “Incorrect Username or Password” and hide it from the Format properties.

Login Button On Click event Code

Private Sub btn_login_Click()
If DCount("uusername", "usertable", "uusername = '" & txt_username & "' and upassword = '" & txt_password & "'") = 0 Then
    Me.Lbl_incorrect.Visible = True
        Else
            DoCmd.Close acForm, "LoginForm"
            DoCmd.OpenForm "MainForm"
End If
End Sub

The login button must be working fine to open the Dashboard Form if the Username and Password are correct. Please make sure that the name of the form is “MainForm”.

4. Logout Button Functionality

Logout Button Code to close the Main Form and open the Login Form:

Private Sub btn_logout_Click()
responce = MsgBox("Do you really want to logout?", vbYesNo + vbQuestion, "| Logout Confirmation |")
    If response = vbYes Then
        DoCmd.Close acForm, "MainForm", acSaveNo
        DoCmd.OpenForm "LoginForm"
            Else
                Cancel = True
End If
End Sub