Access

Log in form

Ease of Use

Intermediate

Version tested with

2000 

Submitted by:

Brandtrock

Description:

This example begins with a log in form consisting of a combobox, a text box and a command button. After 3 unsuccessful attempts, the application shuts down. 

Discussion:

This log in example illustrates the steps to require users of an Access database file to login. This SHOULD NOT be mistaken for a security measure as it is easily bypassed by holding the shift key down or switching to design mode. In the case of an Access project (for example, an Access front end to a SQL Server database) this will be of more use than in the case of an Access database. Working with an Access project (.adp file) is very similar to working with an Access database (.mdb file). The process of creating forms, reports, data access pages, macros, and modules is virtually the same as that used to create an Access database. In this example, the data table is contained in an Access database as a remote connection is problematic. The data table consists of the username, the associated password, and the associated level of access. This data table would ordinarily reside on the server. Nothing in this example is done with the access levels. Additional coding could be implemented to disallow certain elements of the database to different levels of users. Three unsuccessful tries at the password result in the application shutting down. All passwords in the example file are: password. The passwords are not case sensitive. A userform/splash screen displays when a log in is successful. This splash screen may be replaced with a switchboard form or another form of the designer's choice. Changing the code to reflect the appropriate form name is required for this. 

Code:

instructions for use 

			
 '****************************
 '* code for module basMyEmpID
 '****************************
Option Explicit 
Option Compare Database 
 
Public lngMyEmpID As Long 
 
 
 '****************************
 '* code for Form_frmLogon
 '****************************
Option Explicit 
Option Compare Database 
Private intLogonAttempts As Integer 
 
Private Sub Form_Open(Cancel As Integer) 
     'On open set focus to combo box
    Me.cboEmployee.SetFocus 
    intLogonAttempts = 0 
End Sub 
 
Private Sub cboEmployee_AfterUpdate() 
     'After selecting user name set focus to password field
    Me.txtPassword.SetFocus 
End Sub 
 
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 "User Name is a required field.", 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 "Password is a required field.", 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 (could be Switchboard or another form instead)
         
        DoCmd.Close acForm, "frmLogon", acSaveNo 'substitute correct name if using
         'form other than frmLogon in the example.
         
        DoCmd.OpenForm "frmSplash_Screen" 'substitute correct name if using switch
         'board or other form.
         
    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 your system administrator.", vbCritical, "Access to Access is Restricted!" 
        Application.Quit 
    End If 
     
End Sub 
 
 
 '****************************
 '* code for Form_frmSplash_Screen
 '****************************
Option Explicit 
Option Compare Database 
 
 'The code in this form will be deleted if the form is deleted and a
 'switchboard style form or another form is used instead of the splash
 'screen.
 
Private Sub Form_Close() 
    DoCmd.SelectObject acTable, "tblEmployees", True 
End Sub 
 
Private Sub Form_Timer() 
     
     'When the Timer Interval is achieved, this form closes
    DoCmd.Close 
     
End Sub

How to use:

  1. The module BasMyEmpID declares a Public variable used in the login form.
  2. To add a module to an existing project, press Alt F11 to launch the VBE.
  3. Choose Insert>Module.
  4. Copy the code for the basMyEmpID from above.
  5. Paste it into the new module.
  6. To rename the module (if desired) type a new name next to Name in the Properties window (display this if not shown by going to View>Properties Window or pressing F4)
  7. The Logon form should be the same layout as in the example for the code to work properly. If designing a separate form, or applying the code to an existing form, change the names of the objects as needed.
  8. The Splash Screen form included in the example is there to illustrate what should happen upon a successful log in attempt.
  9. If the design of the project dictates use of a switchboard or some other form, refer to it in the code where indicated (see above).
  10. The employee names, passwords, and access level are included in a table.
  11. This table is used by the combo box to display a list of names to select from.
  12. This table is compared to the password entered by the user to determine if the login is successful or a failure.
  13. A table like the one in the example must be included in the project if this information is not already present.
 

Test the code:

  1. Open the zip file.
  2. Double click the database file.
  3. Press the Go button. Note that you are required to enter a username.
  4. Select a username from the drop down list in the first combo box
  5. Press the go button. Note that you are required to enter a password.
  6. The example file has the password set to (password) without the parentheses.
  7. Enter an incorrect password.
  8. Press Go.
  9. Press OK.
  10. Press Go.
  11. Press OK.
  12. Press Go.
  13. Press OK.
  14. Press OK.
  15. You have been kicked out of the database now.
  16. Double click the database file from the zip folder again.
  17. Select a username.
  18. Type password into the password box.
  19. Press Go.
  20. Notice that the splash screen displays.
  21. The database window displays after a brief delay.
  22. Close the file or amend the code to suit your needs.
 

Sample File:

login.zip 19.42KB 

Approved by mdmackillop


This entry has been viewed 578 times.

Please read our Legal Information and Privacy Policy
Copyright @2004 - 2020 VBA Express