MS Access Login Form With Permissions Tutorial

MS Access Login Form With Permissions Tutorial

ms access login form with permissions title slide

Say you want to give certain users certain privileges within your MS access application. Maybe you want your data to be hidden, or maybe you want to make different interfaces to work with your data depending on who in your company is accessing the access database. We can control these user interactions (somewhat) with a ms Access Login form with permissions applied. (Warning: We’ll use a bit of VBA code, but I’ll explain it in depth.) 

Also, this is the in-depth, fully explained version of this post. If you’re just here for the concepts and the code, feel free to follow the quick guide found here.

Let’s get started!

ms access login form with permissions - Login Interface
Example of workplace login interface

I’ll walk you through a real life working example of one I created in my workplace. And maybe it’ll give you ideas on how to implement it in your workplace as well.

So, let’s briefly go over why I wanted to create a user login form in the first place. I work in a manufacturing environment, and everything that is made isn’t perfect. Whenever things aren’t perfect they receive a ‘Defect’ which just means that they aren’t good enough for the customer to purchase. These defects are logged into an excel spreadsheet which can then be accessed by an access database application through linkage.

We wanted a way for users to be able to log in and only see their own defects – not everyone else’s too. But Supervisors needed to be able to see all the defects in their responsible area, and finally managers needed to be able to download a full overview of the plant’s defects.

So from this description, we need to create three separate interfaces for three different role types. We need to validate those role types to the user, so a login form makes perfect sense. So let’s get started in the tutorial!

  1. we need to tell Access what to expect from a form login attempt. So, we’ll need to create a list of users and passwords to check against whenever the login form is submitted. We’ll do this with a table.
MS Access login form with Permissions - Create a User Table
ms access login form with permissions - tblUser

First we’re going to have an ‘ID’ field. This is generated whenever you create the table. It’s just a unique number to identify all the different entries into your table. AutoNumber gives that uniqueness among users.

I want my users to be able to use their clock numbers to login to the system. I did this for the ease of not having to ask every employee to create a new username. I can just go pull a list of clock numbers from the HR department and input these in without needing to ask every operator a username, or creating them myself.

The ‘UserSecurity’ field is what I’ll use to determine the rights of the user once they login. Since I have three levels of access to my program, I just used the numbers 1,2, and 3 to determine the level of ‘power’ that they have. In other words, this is their permissions to access the data.

The UserPassword field is exactly what you think it would be – the user password. On this one, make sure that you go ahead and add an Input mask of ‘Password’ in the design view options:

ms access login form with permissions - Input Mask of Password

Lastly, go ahead and save your table. I called mine ‘tblUser’ if you want to follow along perfectly with my example.

MS Access login form with Permissions - The Login Form
MS Access login form with permissions - DefectLogin

I want the user to only input two fields into the form to gain access, their clock number and then a password. You’ll need to name both of these fields, because we’re going to reference them with some VBA code here in just a second.

I named my password form field ‘txtPassword’ and my clock number form field ‘txtUserName’. Below shows where you name the fields in the design view:

ms access login form with permissions - Username form field
ms access login form with permissions - Password Form Field

After that, you’ll need to create a button to execute code whenever it is clicked. First, name your button: I named mine ‘btnLogin’

You can see the property table I specified for the login button to the right.

ms access login form with permissions - login button
MS Access Login Form with Permissions - VBA Time!

Then, to write VBA code once your button is clicked, whenever you create your button, immediately exit the button wizard. Then, right click and select the option of ‘Build Event’ then VBA code.

Then you’ll have to input the following VBA code into the button click function.

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

It looks scary, but it really isn’t. 

Let’s go through each line to understand it.

The beginning lines use a command of ‘Dim’ – all this does it denotes that a variable is coming up and what that variables formatting will be. A string is just text, integers are just numbers, and so on.

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

Whenever the command ‘Me’ is used, it takes the currently active item. So by saying ‘Me.txtUserName’ we’re just saying “hey, check the form out that the user is on, and if the thing named ‘txtUserName’ IsNull (nothing there) then gives us a message box that says to the user to enter their username.

The same happens with the field ‘txtPassword’ which was our password form field. If both of these are not blank, then we move on to the next action using the ‘Else’ Command.

If (IsNull(DLookup(“ClockNumber”, “tblUser”, “ClockNumber = ‘” & Me.txtUserName.Value & “‘ And UserPassword = ‘” & Me.txtPassword.Value & “‘”))) Then

MsgBox “Invalid Username or Password!”

Else

Here, we’re saying that if we don’t return the same ClockNumber from tblUser that was input into the form’s txtUserName field, along with the same password that’s listed in the table, then return an error saying that the username & password combination isn’t valid. If it is, then we move on using the else statement.

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 & “‘”)

We’re assigning the TempID variable that we created above to the value that’s in the ClockNumber form field – which is verified as the one that’s also in the table. We do this because now we have a variable that we know is correct whenever accessing specific to that employee information.

 

Next, we pass a number that we will use as the security level access that the individual has. This will come directly from the table and you’ll have to input it into the system. I’ll show you how to default it to minimum access coming up.

Else

‘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

Last lines of code. What these are saying is if the UserLevel, or security access number that we assigned before, is 1, open the Admin form and then make the input form disappear so it’s not in the way.

The same with the number 2.

However, if the form is anything else, open up the default, lowest level of access in the form “Nav_Form_Employee”

So here, you could either put a 3 in the table, or nothing at all, and that employee will still have access to the lowest level form.

Now of course, you’ll have to make the forms that you want the different employee types to have access to, and replace these names with the names of your forms, but that’s the general idea.

If you have any questions, please let me know in the comments below and I’ll be more than happy to try to help you out. Thanks!

Enjoy this content? Join the Newsletter!

Leave a Reply

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