databasedev.co.uk - database solutions and downloads for microsoft access

How to create a User Login Form in Microsoft Access:

Question » In my Microsoft Access database I have a table of Users, each with a user name and password, and a login form set up. I want the user name and password entered into the login form to be compared against those stored in the Microsoft Access table. If a match is found the user is allowed to access to the system, if not the system should exit.

Answer » The following details one procedure that could be applied to this scenario. There are many more approaches to this and also other areas that will need to be secured within your database environment. You should also look into Disabling/Enabling the Shift Bypass Key.

On initially opening the Microsoft Access database you are presented with a database LogOn screen. This screen controls security access to the database and its objects. You should set the LogOn form to be the first form to open in the database start-up options.

The screen seen below is an unbound form containing an unbound drop -down list. This list is made up of users defined in the table - tblEmployees.

Table - tblEmployees, showing UserName and Password fields.

The other control on the form is an unbound text box. This control is where the user will enter their security password. This control also contains an input mask ensuring that their password cannot be seen: (***********)

The form then contains a command button labelled Go. There is visual basic code contained on the On Click event of this button that will ensure access to the database providing valid information is entered into the above controls. You can see the LogOn form below:

Database Log On Form.

The drop-down list sources the information using an SQL command:

SELECT [tblEmployees].[lngEmpID], [tblEmployees].[strEmpName]
FROM tblEmployees;

This returns the Employee ID number and the Employee name, however only the Employee name is displayed (using the column width's property of 0cm;1cm. The column bound to the database table is column 1 (lngEmpID).

On selection of a name from the list, there is an event procedure that runs on the After Update event of the combo-box. This event moves the focus (the cursor position) to the Password control:

Private Sub cboEmployee_AfterUpdate()
'After selecting user name set focus to password field
    Me.txtPassword.SetFocus
End Sub
                

The user will then need to enter their valid password, followed by clicking the command button to enter the database.

On clicking the command button (cmdLogin) the following code is attached to the buttons On Click event procedure:

Private Sub cmdLogin_Click()

'Check to see if data is entered into the UserName combo box

    If IsNull(Me.cboEmployee) Or Me.cboEmployee = "" Then
      MsgBox "You must enter a User Name.", vbOKOnly, "Required Data"
        Me.cboEmployee.SetFocus
        Exit Sub
    End If

    'Check to see if data is entered into the password box

    If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
      MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
        Me.txtPassword.SetFocus
        Exit Sub
    End If

    'Check value of password in tblEmployees to see if this
    'matches value chosen in combo box

    If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
            "[lngEmpID]=" & Me.cboEmployee.Value) Then

        lngMyEmpID = Me.cboEmployee.Value

        'Close logon form and open splash screen

        DoCmd.Close acForm, "frmLogon", acSaveNo
        DoCmd.OpenForm "frmSplash_Screen"

    Else
      MsgBox "Password Invalid. Please Try Again", vbOKOnly, _
            "Invalid Entry!"
        Me.txtPassword.SetFocus
    End If

    'If User Enters incorrect password 3 times database will shutdown

    intLogonAttempts = intLogonAttempts + 1
    If intLogonAttempts > 3 Then
      MsgBox "You do not have access to this database.Please contact admin.", _
               vbCritical, "Restricted Access!"
        Application.Quit
    End If

End Sub

If a selection has not been made the user will be presented with a message box informing that this is required data, clicking OK will send the focus to the combo box:

Error message stating that a User Name must be entered.

Once a selection is made the focus moves to the password entry control. Once again this uses a statement to check that an entry is made:

'Check to see if data is entered into the password box
If IsNull(Me.txtPassword) Or Me.txtPassword = "" Then
    MsgBox "You must enter a Password.", vbOKOnly, "Required Data"
    Me.txtPassword.SetFocus
    Exit Sub
End If

Error message stating that a User Password must be entered.

Once the required information is entered the following code runs to validate the entries made:

'Check value of password in tblEmployees to see if this
'matches value chosen in combo box

If Me.txtPassword.Value = DLookup("strEmpPassword", "tblEmployees", _
                                  "[lngEmpID]=" & Me.cboEmployee.Value) Then

    lngMyEmpID = Me.cboEmployee.Value

    'Close logon form and open splash screen

    DoCmd.Close acForm, "frmLogon", acSaveNo
    DoCmd.OpenForm "frmSplash_Screen"

Else
    MsgBox "Password Invalid. Please Try Again", vbOKOnly, "Invalid Entry!"
    Me.txtPassword.SetFocus
End If

If the information contained matches information found in tblEmployees the user will then be allowed access to the database, and the database splash screen will appear.

The final statement executed by the code will check to see if an incorrect password has been entered 3 times. If this is found to be true, the user is presented with a message box informing them of the situation and the database will close:

'If User Enters incorrect password 3 times
'database will shutdown

intLogonAttempts = intLogonAttempts + 1
If intLogonAttempts > 3 Then
    MsgBox "You do not have access to this database.Please contact admin.", _
           vbCritical, "Restricted Access!"
    Application.Quit
End If

Error message informing that access is denied.

Finally, you will need to create a module to store the following variable:

Public lngMyEmpID As Long

Please download the Microsoft Access 2000 database example file from the Microsoft Access Forms main page or from the Microsoft Access Downloads index.