MS Access Login and Permissions Quick Guide

MS Access Login and Permissions Quick Guide

MS Access Login and Permissions Quick Guide - Title
MS Access Login and Permissions Quick Guide

This is the quick guide, a shorter more concise version, of the post found Here. If you need more information on what’s going on, go check out that post. If you want the quick and dirty, you’ve found it. Bring a towel. 

What you need:

  1. Table to assign Users access and User Permissions
  2. Login Form 
  3. On click event on your login form (VBA Code Below)
  4. Navigation Forms for your different user levels.
  1. MS Access Login and Permissions Quick Guide –
    Table to check user access levels and validate login:
ms access login form with permissions - tblUser
  • Clock Number is what I used in place of a username here. Feel free to edit it to a username field.
  • User Security denotes the level of permission I want to give the user. I used numbers 1,2, and 3 to denote the three levels of permission my users needed.
  • User Password is to make sure the user logging in is actually the user linked to the clock number. Probably Want to put a password mask on this:
ms access login form with permissions - Input Mask of Password
2. MS Access Login and Permissions Quick Guide - Create a Login Form
MS Access login form with permissions - DefectLogin

Give easy to remember names for the two fields on your login form.

ms access login form with permissions - Username form field
'txtUserName' was assigned to the clock number input field
ms access login form with permissions - Password Form Field
'txtPassword' was assigned to the password form field

Next, Create a button. Right click and build an event. Exit the button wizard, then right click again and select ‘Code VBA’

3. MS Access Login and Permissions Quick Guide - VBA Event on Login Button Click

Private Sub btnLogin_Click()

Dim User As String

Dim UserLevel As Variant

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(“ClockNumber”, “tblUser”, “ClockNumber = ‘” & Me.txtUserName.Value & “‘ And UserPassword = ‘” & Me.txtPassword.Value & “‘”))) Then

MsgBox “Invalid Username or Password!”

Else

TempID = Me.txtUserName.Value

UserLevel = DLookup(“[UserSecurity]”, “tblUser”, “[ClockNumber] = ‘” & Me.txtUserName.Value & “‘”)

TempPass = DLookup(“[UserPassword]”, “tblUser”, “[ClockNumber] = ‘” & Me.txtUserName.Value & “‘”)

ClockNumber = DLookup(“[ClockNumber]”, “tblUser”, “[ClockNumber] = ‘” & Me.txtUserName.Value & “‘”)

‘open different form according to user level

‘ open the Admin navigation whenever userlevel of the user is == 1

‘ open the Lead Navigation menu whenever the userlevel of the user is == 2

‘ open the Employee Navigation menu whenever the userlevel of the user is == 3

If UserLevel = 1 Then ‘ for admin

DoCmd.OpenForm “Nav_Form_Admin”

Me.Visible = False

ElseIf UserLevel = 2 Then ‘ for Lead

DoCmd.OpenForm “Nav_Form_Lead”

Me.Visible = False

Else

DoCmd.OpenForm “Nav_Form_Employee”

Me.Visible = False

End If

End If

End If

End If

End Sub

4. MS Access Login and Permissions Quick Guide - Change your action depending on the User Security

In my above code, I have three three different user security levels I created going to three different navigation forms. I only put options of the navigation forms that I wanted to users to be able to access. This isn’t entirely fool proof – but nothing with access is ever going to truly be secure. Hide the navigation pane and everything will likely be alright.

If you want more security whenever it comes to user interactions, you’ll have to get away from Access applications. Perhaps venture into the cloud with AWS solutions. 

Enjoy the Content? Sign up for the Newsletter!

Leave a Reply

Your email address will not be published. Required fields are marked *