How to Create a Login Form for MS Access

Before creating a Login Form, you need to set up a user table that can verify the login ID and password on the Login Form. The step of creating Login Form can be followed below:

Create a table tblSecurity with a SecurityID and SecurityLevel field and add admin for SecurityID =1 and user for SecurityID =2

securitystep1

Create a table tblUser with a UserName, UserLogin, UserSecurity and UserPassword fields.  Remember to add an input mask on the password field so that passwords are not blatantly visible in the table.

securitystep2

I choose to display the type of user rather than just the raw number of the User’s security level by using the Lookup options in the design view. This way the UserType field is a number corresponding to the User’s security level because it refers to the SecurityID in the tblSecurity but displays the user’s UserType in text (admin, user etc.). You can create the UserType field from the Lookup Wizard on the dropdown of Data Type column.

usertype

Create a Login Form from the Dialog form design. Then customize the form to your liking.

how-to-create-a-user-login-form-in-ms-access

Create two text boxes in the Login Form as txtUserName with label Login Name and txtPassword with label Password

create-a-login-form-ms-access-2016

Under the “On Click Event” of the Cancel button, add the Embedded Macro with a QuitAccess command to exit the program or Access application

how-to-creat-a-login-form-macro

Create a Navigation and Admin Form area to reroute your users to the appropriate area of your database application.

Under On Click Event of the OK button, add the VBA code below under the Event Procedure

Option Compare Database

Private Sub Command1_Click()
Dim User As String
Dim UserLevel As Integer
Dim TempPass As String
Dim ID As Integer
Dim UserName As String
Dim TempID As String


If IsNull(Me.txtUserName) Then
 MsgBox "Please enter UserName", vbInformation, "Username required"
 Me.txtUserName.SetFocus
ElseIf IsNull(Me.txtPassword) Then
 MsgBox "Please enter Password", vbInformation, "Password required"
 Me.txtPassword.SetFocus
Else
 If (IsNull(DLookup("UserLogin", "tblUser", "UserLogin = '" & Me.txtUserName.Value & "' And UserPassword = '" & Me.txtPassword.Value & "'"))) Then
 MsgBox "Invalid Username or Password!"
 Else
 TempID = Me.txtUserName.Value
 UserName = DLookup("[UserName]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLevel = DLookup("[UserType]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 TempPass = DLookup("[UserPassword]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 UserLogin = DLookup("[UserLogin]", "tblUser", "[UserLogin] = '" & Me.txtUserName.Value & "'")
 DoCmd.Close
 If (TempPass = "password") Then
 MsgBox "Please change Password", vbInformation, "New password required"
 DoCmd.OpenForm "frmUserinfo", , , "[UserLogin] = " & UserLogin
 Else
 'open different form according to user level
 If UserLevel = 1 Then ' for admin
 DoCmd.OpenForm "Admin Form"
 Else
 DoCmd.OpenForm "Navigation Form"
 End If

 End If
 End If
End If
End Sub

Private Sub Form_Load()
Me.txtUserName.SetFocus
End Sub

Set the Login Form to display immediately when the Access Database is opened. Go to File->Options and select the Current Database option from the list. Then set the Login Form as the display form.

set-a-display-form-for-when-access-first-opens

It should be noted that while this login procedure is helpful to have permissions and different roles withing your Access database and may be needed for our Remote Desktop Hosting – it is not necessary for any hosted Access Web App or Sharepoint Application since you can use the Sharepoint user roles and login procedures rather than handling it within the Access Database itself.

This entry was posted in Access 2016, Remote Desktop Hosting, Tutorial and tagged , , . Bookmark the permalink.

7 Responses to How to Create a Login Form for MS Access

  1. Liakat Biswas says:

    is there any video tutorial of this?

  2. slelgan says:

    This works for the most part, but there is an unexplained form “frmUserinfo” that needs to be created for it to work with a new user that has “password” for their default. Please add the details for this form.

  3. faza says:

    change userType into UserSecurity when you get an error

  4. WORKS LIKE A CHARM!!! THANKS

  5. electrifekt says:

    How do you link to a new form after login? Complete noob to this.

  6. Dave says:

    Can you explain the fix in more detail please?

  7. S says:

    Getting an error on the line the is Me.txtUserName.SetFocus under the if is null. please help 🙁

Leave a Reply