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.

50 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 🙁

  8. lynich says:

    Agreed. This is extremely helpful and I’m curious about setting up the “frmUserinfo” and how it correlates to changing to a new password from their default, too. Any help here would be appreciated.

  9. Vsm says:

    Error coming saying method or data member not found

  10. Tlhogi says:

    Can you post a link to your access file with the code?

  11. Pdiddy says:

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

  12. Login form worked properly. How to link my db with login form.

  13. Melanie says:

    This is amazing – would have taken me so long to work out alone! With just a couple of changes I could adapt it to my already existing DB. Thank you

  14. idmrentals says:

    Do you actually have a control that has the name “txtUserName” ? Also you may want to actually ensure you have interactively specified the form_load() routine from the correct form you are using. You can do this by going to the properties of your form and selecting the Event tab on the property sheet. Select on load. Then go to code builder (not macro builder etc.) (note this applies to Access 2016.)

  15. idmrentals says:

    I think all you have to do is specify the form you want to be directed to. See code: If UserLevel = 1 Then ‘ for admin
    DoCmd.OpenForm “Admin Form”
    Else
    DoCmd.OpenForm “Navigation Form”
    End If

    So for your application, specify the form you want to open replace “admin form” with the name of your form or “Navigation Form” with the appropriate form etc.

    Unless I misunderstood your question.

  16. idmrentals says:

    Do you really need it? meaning just remove the logic that checks the password….if you need that password check, then yes create the form that will store the new password. In the new form force the user to change the password – follow the same logic and then direct them to the form they need to reach….

  17. S says:

    How do a track when users login to the database? I have tried multiple different vba coding configurations, and they do not work. If you have any insight that would be great.

  18. S says:

    Yes please do. I have tried with no luck.

  19. George A says:

    I’m getting same error on the line the is Me.txtUserName.SetFocus under Private Sub Form_Load () and the Error coming saying method or data member not found. Whomever replied it works, can you post the VBA code in whole? I did above step by step in new database before adding any company data, and no joy.

  20. ZUL says:

    same here..Error coming saying method or data member not found.. 🙁

  21. vardis says:

    I ve implement the above example and it works perfect. I have 2 questions. 1.Does it work on the web? 2. How do i setup specific users 2 view only their records ? (according 2 username and password they enter)

  22. V Kumar says:

    I typed the vba code but after ok nothing is happened. File error 2471 can help any please

  23. What do you want to do? To open another form after login?

    If that is the case, then

    DoCmd.OpenForm “Admin Form”
    Else
    DoCmd.OpenForm “Navigation Form”
    End If
    these two lines open the forms depending on user level

  24. Check the spelling of the naming of your textboxes

  25. Brewmaster says:

    The Password validation is not case sensitive. Any thoughts on fixing it?

  26. Brewmaster says:

    you have to create the forms. The logic is already there to trigger forms named “Admin Form” and “Navigation Form”.

  27. Brewmaster says:

    Macro for “QuitAccess” appears to have been changed to “Exit”.

  28. Lj says:

    This works like magic, for the Me.txtuser error, you have to customize the right cell name so the code will work. Thank you!

  29. Pongpanod says:

    I want link download this sample

  30. nemo says:

    its working but you can used the password of other users to log in

  31. d says:

    I am getting an error “Ambiguous Name Detected: Command1_Click when opening the form.

  32. Charles says:

    its not working for my access 2007

  33. Les says:

    “Create a Navigation and Admin Form area to reroute your users to the appropriate area of your database application”. what does this part of the guide mean?

  34. Paul says:

    The code works great. My question is how do you get the login screen to open without any login info pre-filled? Right now it opens right up with the admin login info prefilled. Pretty much defeats the purpose.

  35. Stew Williams says:

    I have noticed a problem that while it does require a password and username, they do no need to match.. I have noticed that as long as the password is in the table, it does not have to be the one associated with that particular user, it will still provide access. Any ideas why this is?

  36. Jamila says:

    thank you so much it works ,, how to get the username in other form ???

  37. Mike says:

    What is the purpose of the UserLogin field?

  38. Gedeon Ibhraime Priniel Diamos says:

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

  39. x says:

    thanks for this.. works great!

  40. db says:

    how would I retrieve that username to use on a form to display the current user ?

  41. Tanya says:

    Error coming saying method or data member not found

  42. Rui says:

    This is a very useful form and good explanation .
    But I have the same need as db: I need to use the username (UserName) on the header of the following forms, as well as have access to the user level (UserLevel) for further flow and access control. I think that these variables cannot be used outside of this macro because they are defined as Private. How can I have them available outside of this macro?
    Thank you.

  43. Besrat Mekonnen says:

    your the best

  44. eranka says:

    HI

    Could you please explain frmUserinfo form?

  45. Houcine says:

    Great work!!
    Do you have an example of drag and drop files from explorer to listbox
    Thank’s in advance

  46. Chad says:

    I have the same question

  47. boaz nyauma says:

    I am getting an error “Ambiguous Name Detected: Command10_Click when opening the form.

  48. same here! have you solved the problem?

  49. Error saying “method or data member not found” then highlight “Private Sub Form_Load()
    Me.txtUserName.SetFocus”… can anyone help me on this please? This is the vba code…

    +++++++++++++++++++++++++++++++++++++++++++++
    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 “Por favor ingrese Nombre de Usuario”, vbInformation, “Nombre de Usuario requerido”
    Me.txtUserName.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
    MsgBox “Por favor ingrese su clave”, vbInformation, “Clave requerida”
    Me.txtPassword.SetFocus
    Else
    If (IsNull(DLookup(“UserLogin”, “tblUser”, “UserLogin = ‘” & Me.txtUserName.Value & “‘ And UserPassword = ‘” & Me.txtPassword.Value & “‘”))) Then
    MsgBox “Nombre de usuario o clave inválida!”
    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 “Por favor cambie su clave”, vbInformation, “Clave nueva requerida”
    DoCmd.OpenForm “frmUserinfo”, , , “[UserLogin] = ” & UserLogin
    Else
    ‘open different form according to user level
    If UserLevel = 1 Then ‘ for admin
    DoCmd.OpenForm “Formulario de navegación”
    Else
    DoCmd.OpenForm “Formulario de navegación”
    End If

    End If
    End If
    End If
    End Sub

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

  50. Gijsbert says:

    To open the form frmUserinfo the filter in above script is not working in Access 2016/Office 365:
    DoCmd.OpenForm “frmUserinfo”, , , “[UserLogin] = ” & UserLogin

    Replace it with:
    DoCmd.OpenForm “frmUserinfo”, , , “[UserLogin] = ‘” & UserLogin & “‘”

    It will works like a charm. frmUserinfo will open with the correct user filtered

Leave a Reply