Creating Login Security using Access VBA

First, let me state that Access is not the most secure platform. If you really need strong security on a database, you are better off using a more secure back end like SQL Server. But if you don’t need high level security, you can secure your database using VBA. I’ll be using a fairly simple example for this article, but it should give you enough to expand on for more complex needs.

The first step is to setup the tables needed to drive the security. Two tables are needed:

tblUser

UserID (PK) Autonumber
FName Text
Lname Text
Password Text
PWReset Yes/No
AccessLevelID Long Integer

tluAccessLevel

AccessLevelID (PK Autonumber
AccessLevel Text

The second table is a lookup for the AccessLevelID in tblUser. For this example we will have four Access levels:

AccessLevelID AccessLevel
1 Developer
2 Administrator
3 Editor
4 Reader

You can use whatever and how many levels you want or need depending on your application. The above is provided as a basic example. You can also add more fields to tblUser if needed.

Once you have the tables setup, you can create the Login form. This will be an unbound form with two controls; a combobox to select the user and a textbox to enter the password. You will want to restrict this form by turning off the Min, Max and Close buttons. Also set the Navigation Bar and Record Selector properties to No. I suggest the name frmLogin for the form, cboUser for the combobox and txtPassword for the textbox.

For the combobox, you will set the ColumnCount to 5, the ColumnWidths to 0;1;0;0;0 and the RowSource property to the following SQL statement or create a named query:

SELECT tblUser.UserID, [Lname] & “, ” & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID
FROM tblUser
ORDER BY tblUser.LName, tblUser.FName;

The user will select their name from the combobox and then the password in the textbox. Now we get into the VBA that drives the security. This code will be entered in the AfterUpdate event of the password textbox.

The first task the code performs is to make sure a user has been selected. If, not focus is returned to the cboUser combo. The next step is to check if the password entered matches the stored password. If it doesn’t, focus is returned to the txtPassword control. If there is a match, then the Reset column in the RowSource is checked to see if the user is required to set a new password If it is, a form is opened to enter a new password. If Reset is false, then the main menu is opened. The login form is then hidden. This is so the cboUser control can be referenced in other parts of the application. Alternatively you can save the value in a global variable and close the form.

Private Sub txtPassword_AfterUpdate()
‘Check that User is selected
If IsNull(Me.cboUser) Then
MsgBox “You need to select a user!”, vbCritical
Me.cboUser.SetFocus
Else
‘Check for correct password
If Me.txtPassword = Me.cboUser.Column(2) Then
‘Check if password needs to be reset
If Me.cboUser.Column(4) Then
DoCmd.OpenForm “frmPasswordChange”, , , “[UserID] = ” & Me.cboUser
End If
DoCmd.OpenForm “frmMainMenu”
Me.Visible = False
Else
MsgBox “Password does not match, please re-enter!”, vboOkOnly + vbExclamation
Me.txtPassword = Null
Me.txtPassword.SetFocus
End If
End If
End Sub
The Password Reset form (frmPasswordChange) is bound to tblUser. So when it’s opened, it’s filtered for the selected user.  The user then enters the new password twice to confirm it. Then open the main menu.

The final piece to this is using the AccessLevel to restrict access to various parts of the application. This is done by checking the Access level in one of two ways. You can reference the column in the combobox by using the expression:
Forms!frmLogin!cboUser.Column(4)
or using the expression:
Dlookup(“[AccessLevel]”,”tblUser:,”[UserID] = “ & Forms!frmLogin!cboUser)

Once you have retrieved the Access level, you can use it to restrict access to forms and controls based on the access level assigned to the user. Following are some examples of this:

Case 1: Restricting access to a form only to administrators
In the On Open event of the form, you place code like:
If Forms!frmLogin!cboUser.Column(4) <> 2 Then
MsgBox “You are not authorized to open this form!”, vbOkOnly + vbExclamation
Cancel = True
End If

Case 2: Making a form read-only for Readers
In the On Open event of the form you place code like:
If Forms!frmLogin!cboUser.Column(4) = 4 Then
Me.AllowEdits = False
Me.AllowAdditions = False
Me.AllowDeletes = False
Else
Me.AllowEdits = True
Me.AllowAdditions = True
Me.AllowDeletes = True
End If

Case 3: Restricting records on a form to ones assigned to the current user
In the On Open event of the form you place code like:
Me.Filter = “[UserID] = “ & Forms!frmLogin!cboUser
RunCommand acCmdApplyFilterSort

These are just three possibilities. There are many other ways to use these techniques to restrict what a user can access in your application.

I hope you find these tips useful.

You can download a sample of this at:

http://www.diamondassoc.com/sampleapps/loginexample.zip

The zip file contains versions for both Access 2007 and 2002-2003. The password for each user is welcome.

© 2010 Scott B. Diamond — Diamond Computing Associates

Advertisements

327 Comments

  1. Frankly I would not recommend it. First, if the user has to provide a password, then you have to create a table to store them. So you make it easier for the user to select their account. So there is really no reason to require them to type in their user name.

    Second, using a combo box makes it easier to pull the Access Level info when you need to test whether they are allowed to access certain parts of your application.

    But, if you want to use a text box, you can. The only real difference is that you would have to use a DLookup, to pull the Access level from the Users table instead of referencing the Column property of the combo.

    Scott

    • I’d like to use a textbox as well because I expect to have several hundred users and a dropdown doesnt seems appropriate. The database doesnt need to be secure per se, but I want users to have access to particular forms based on thier role in the orginization. (and to not have easy access to supervisor forms) I dont really know Visual Basic, could you possibly post some code?

      • Several hundred users should NOT be a problem. Using Autoexpand, the user just needs to start typing their name or they can still type it in fully. The big advantage of using a combo is that you are pulling the full record so you can get other parameters. Otherwise you will have to use DLookups to get the data from the table.

      • Hi! I am a student and i’m preparing my bechalor thesis. I’ve created my log in form with a combo box named username and a text box Password(The data is already in a table under the field names “Username” and “Password”). I’ve also created a button but i have some trouble with the vba code 😦 .

        my database contains data of university’s library so i want to access it only to kind of users students and admin(people who work in the library). I’ve also created two main menu (for the two kind of users).

        I have no knowledge of programming in vba and i really need any help from you!!!
        please write me here or in algoritmi@live.com. Really need a response because i don’t have much time…

  2. So I’m working in access 2007 I built The database so tables, relationships between them, queries.Also some simple forms for student to search the books (stored in my database tables) and two forms for the managers of the library in which they can add new books to the database or to add new members (students). My problem is the log in form , exactly the Visual Basic code .

    I worked with C++, Java, HTML, Javascript so I m not a beginner in programming . I need some help or any procedure how to manage this problem. I will graduate after 3 days and all my thesis i ready only this log in form that I don’t know how to make it functional for this two kind of users.

  3. But what problems do you have with the code? The blog continues specific code snippets to use. You have to tell me what you want your login form to do, what code you have tried to make it do it, what happens when you use that code. I can’t provide any assistance without that. Telling me “the problem is the login form” is not specific, its vague and I can’t help without knowing specifics.

  4. Until now I have managed to build this code but does not run 😦

    Private Sub Command8_Click()

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

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

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

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

    ‘Check value of password in tblUsers to see if this
    ‘matches value chosen in combo box

    If Me.Password.Value = DLookup(“Password”, “tblUsers”, “[id]=” & Me.Combo21.Value) Then
    Dim myid As Integer
    myid = Me.Combo21.Value

    ‘Close logon form and open splash screen

    DoCmd.Close acForm, “login_form”, acSaveNo
    DoCmd.OpenForm “meny_administrator”

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

    ‘If User Enters incorrect password 3 times database will shutdown
    Dim intLogonAttempts As Integer
    intLogonAttempts
    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

  5. Alright, now we are getting somewhere, but this forum is really not suited to this. So I’m going to ask that you post this last post on askmehelpdesk.com in the Access forum. I will see it there and be able to respond.

    You also need to tell me what doesn’t work about this code. If it errors out what error message. If it offers a Debug choice, what line is crashing. It looks correct with one exception. Don’t close the form until after you open the new form. Also the 3 strikes code isn’t connected

  6. Sorry for my english but I’m not an english speaker so I will try to explain my problem again.
    I’ve a table named tblUsers that have this fields:
    UserName—–Text
    Password——Text
    Access——–Number(1 for student and o for admin)
    ID—————-Autonumber
    I’ve also created a login form witch contain a combo box named Combo21 that displays all the UserNames , a textbox named Password and a button named Login.
    That I want my code to do is to control
    1- if the username is entered
    2- if the password is entered
    3- to control if the password entered match with the username and only if this condition is true to open Form Student_Menu if the [Access].[tblUsers]=1 or open Form Admin_Menu if the [Access].[tblUsers]=0.
    My problem is with the VBA syntax or smth else because I have a clear idea of what to do but i cant express it in VBA code.
    Hope again that my explanation was understandable!!!!
    I tested the code published in the article but it did not run also I’ve download it but cant understand how it works because when I make Username and Password nothing happen.

  7. Your english is not the problem here. I want to help you, but you aren’t given me enough info to do so. I’ve also asked that we move this to a more appropriate forum.

    Telling me it doesn’t work doesn’t help me help you. I need to know exactly what doesn’t work? Does it not accept a password? does it open the necxt form whether the password matches or not? You have to be VERY specific for me to try and figure out what is going wrong.

    You are not using my code examples, but someone else’s. If you try my code examples substituting your table, field and object names, it will make it easier to help.

    What is the Rowsource of Combo21? What is the Bound column for Combo21? Do you know how to step through the code to see where it errors out.

  8. I’ve had a good look at your login, hope you dont mind me using it for my project? I have reasonable knoweldge using Excel 07 but not VBA, when I select name and enter password, select the correct box from the main menu, I receive a pop up message saying “access granted”!

    What I need to do, when the appropriate employee enters their name and password, when they click their category, I would like to know how to open up their own specific switchboard

    Can you help?

    • I’m not sure what you are asking here. As I indicated Access is not the most secure platform. This method is meant to control people who aren’t trying to break in. If the the app opens when you close the Login form, then it wasn’t set up right. The ON Close event should close the dialog.

      Scott

  9. Scott I read in your post that you can use the user name as global stored value that can be used in the application. I have a database with 4 field for record tracking. One group is for date and user added the next group is modified date and user. I would like to be able to take the user name from the login and write it in the addby and modby fields. Can you please assist?

  10. I have used your code and the Login works correctly. My question is on my database it has multiple tables, queries, and forms. It has a main menu that opens after you login. There are a group that has administrator access, one with input/editing access, and one group that needs to be able to run queries. This database was built in Access 2003 with a workgroup file and user permissions and converted to 2010. I need to be able to restrict what each group is able to access and do in the database. With the 2010 changes what suggestions do you have on this? Thank you for your help.

    • I discuss this in the blog. Each user is assigned an Access level. When a menu choice is selected, code is run to check the user’s access level. If the Access level is restricted from accessing the function, then an error message is displayed. However, to allow this to work, you need to be able to restrict users from seeing Access’ menus. This means you have to control what options users can exercise.

      • I have created the Login Form and it is working. After you login you come to a Directory Page that has buttons to take you to additional forms and queries. I can’t seam to get the security check to work correctly. This being where it looks to the user table to get the security level for clearance. I also have buttons on the Directory Pages that run queries. What code would I use to check the above security before running the query? I need to have the queries to only open the logged in users information by their userID and not be able to make changes to the query information. Thank you in advance for your help.

  11. There is sample code in the blog.

    First, you have to make sure that you are hiding the login form and leaving it open and that the combo where the user selects themselves is returning the right info. Then you check the Access level to see if they are authorized for that button. You can use the returned value to filter the query.

    If you show me what code you are using I can critique it.

    • My code is below. If they are not an administrator it tells them they are not authorized. Then it let’s them in. Please let me know if you have any questions. Thanks

      Private Sub btnQueryDirectory_Click()

      If Forms!frmLogin!cboUser.Column(4) 2 Then
      MsgBox “You are not authorized to open this form!”, vbOKOnly + vbExclamation
      Cancel = True

      If Forms!frmLogin!cboUser.Column(4) 2 Then
      DoCmd.CancelEvent
      End If
      End If

      On Error GoTo Err_Btn_btnQueryDirectory_Click
      Dim stDocName As String
      Dim stLinkCriteria As String

      stDocName = “Query Directory”
      DoCmd.OpenForm stDocName, , , stLinkCriteria

      Exit_Btn_btnQueryDirectory_Click:
      Exit Sub

      Err_Btn_btnQueryDirectory_Click:
      MsgBox Err.Description
      Resume Exit_Btn_btnQueryDirectory_Click

      End Sub

  12. Scott, I have used the above to add security to my database. Everything is working for me except the access level verification. When I added in the code to a cmd button for a current form. I am getting no response, no error. Nothing happens. Here is the code that I entered in the On click – event procedure.

    Option Compare Database

    Private Sub cmdAdmin_Click()
    If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 2 Then
    MsgBox “You have Administrator access!”, vbOKOnly
    Else
    MsgBox “You do not have Administrator access!”, vbOKOnly
    End If
    End Sub

    Private Sub cmdDev_Click()
    If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 1 Then
    MsgBox “You have Developer access!”, vbOKOnly
    Else
    MsgBox “You do not have Developer access!”, vbOKOnly
    End If

    End Sub

    Private Sub cmdEditor_Click()
    If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 3 Then
    MsgBox “You have Editor access!”, vbOKOnly
    Else
    MsgBox “You do not have Editor access!”, vbOKOnly
    End If

    End Sub

    Private Sub cmdReader_Click()
    If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 4 Then
    MsgBox “You have Reader access!”, vbOKOnly
    Else
    MsgBox “You do not have Reader access!”, vbOKOnly
    End If

    End Sub

    Private Sub cmdCreateEntries_Click()

    End Sub

    I would like for it to open up the “frmCreateOptions” if access level is granted. What am I missing?

  13. I have set this code up on my System and it seems to be working good. The one thing I changed is to Not allow certain forms to be loaded by certain users so I adjusted the code as follows:

    Private Sub Form_Open(Cancel As Integer)
    If Forms!fLogin!cboUser.Column(4) = 3 Or 6 Or 7 Then
    Me.AllowFormView = False
    MsgBox ” You are not authorized to view this form.”
    Cancel = True
    Else
    Me.AllowFormView = True
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
    End If
    End Sub

    This works great but instead of the message coming up I get the following error:
    Runtime error ‘2136’:
    To set this property, open the form or report in design view.
    What can I do to prevent this?

    Another question is how would I code so that a specific button cboLogin is hidden depending on the AccessLevelID.

    • The AllowFormView property doesn’t mean that it allows you to view the form. It means it allows the user to switch to form View from Datasheet or some other view. If you don’t want the user to view the form use this code:


      Private Sub Form_Open(Cancel As Integer)
      If Forms!fLogin!cboUser.Column(4) = 3 Or Forms!fLogin!cboUser.Column(4)= 6 Or Forms!fLogin!cboUser.Column(4)=7 Then
      MsgBox ” You are not authorized to view this form.”
      Cancel = True
      DoCmd.Close acForm, "formname", acSaveNo
      Else
      Me.AllowEdits = True
      Me.AllowAdditions = True
      Me.AllowDeletions = True
      End If
      End Sub

      You would use similar code in the On Current event of the form. Just set the buttons Visible property according to the Access level.

      Scott

  14. Thank-you that code worked great.
    I should use the same coding for the On Current event of a button right?
    Another thing I just found is that I have text boxes that pull values from subforms which I hid using Me.Visible=false but the text boxes still show the values, how doI hide this or atleast tellit not to calculate?

  15. Wow this blog helped me soo muchhh but I have a problem ……

    how do I actually control for example there’s a list of options for menus ie developer access
    admin access

    how do I actually create forms that only let each acess level in
    like the admin acess should only allow data entry for example…but allow them stuff the developer has access to

    need to know how to create the menus and link from that screen where they are listed

    • Glad you found this useful. The blog describes how to control access in the three Case example at the end of the article. There are a number of ways that you can control access. For example, you might use a menu form and hide buttons that are not applicable to the current user’s access level. Or you could use code in the ON Open event of a form that checks for the access level, and closes the form with a message if they aren’t allowed or restricts what they can do or see based on access level.

      See the examples in the three Cases and feel free to ask if you need more help.

      Scott

  16. Hey scott,

    I have a db with Front end and back end.
    in BE I have a table called users
    in the table there are id(autonumber), username, password, and admin fields.

    at the open of the database, the switchboard launches. At its open, it opens the login form – the form contains the Username and Password textboxes.

    I have gotten the validation for login working, however I would like to implement an access level check for various switchboard items.
    at the launch of the “administration” switchboard item, I would like it to check which user has the approporiate level “Admin” access as set in the table.

    can you help? here is my current code. I have the switchboard Item call a function “AccessLevel”, the following is the function’s code. ‘

    Function AccessLevel()

    Dim db As Database
    Dim rs As Recordset
    Dim strSQL As String
    Dim strUserName As String
    Dim userAccessLevel As String

    Set db = CurrentDb
    strUserName = [ap_GetUserName]
    userAccessLevel = “admin”

    strSQL = “SELECT Admin FROM sUser WHERE username =’ & strUserName & ‘”
    Set rs = db.OpenRecordset(strSQL)

    If rs.Fields(0) userAccessLevel Then
    Result = 0
    Else
    Result = 1
    End If

    If Result = 1 Then
    AccessLevel = 1
    Else
    AccessLevel = 0
    End If
    End Function

    • My blog article explains how to do this, though in a different way. Have you looked at the instructions in the article to adapt to your situation?

      I’m not following what your users table structure is. What do you mean by “admin fields”? In my solution, I have an Accesslevel field in the user’s table. I check the value of that field and then allow or disallow access depending on the value and the function to be accessed.

      Scott

  17. Hi Scott 🙂

    Quick Question

    I have set up a login menu but I want to display the picture on file as an attachement in the database how do I get the field to update after the username has been selected ie user enters username then the attachment ‘picture’ is updated’

    Alex

  18. Scott Gem I am creating an Inventory Database at work and this code for Username/Password is right up the alley of what I was trying to do. So I would attach this event to the “On Open” or “On Load” (what exactly does the afterUpdate do) or is that just the name you specifically used and not any internal method/function?

    • Which code are you referring to? The After Update event is an event property that is valid for input controls on a form or a form itself. For a control its fired after the value in the control is edited, for a form, its triggered after the record is updated. If the blog states that code should go in the After Update event of a control, then that’s where it should be placed.

      The blog describes a system that can be used to secure a database. The system allows a user to login and be assigned a level of access. What happens after the initial login is up to the developer. It can be used to restrict access to forms, menus etc.

      Scott

  19. Dear Scott

    I have downloaded the example…

    i have logged in as the developer clicked the button and says ok ur the delvoper how to i get to to go on from there do i need to create forms for the developer how do i say to go to this after?

    Alex

    • The example is only for identifying the user and their access level to the application. The blog article describes how to use the access level to control access to forms or other areas of YOUR application. But you need to apply these techniques to your application.

      If you want to test, you can create a form and use the On Open event to test the Access Level (as mentioned in the blog and determine what access to allow.

      Scott

  20. Pingback: Login Form | DEVelopers HUT

  21. Hi Scott
    I work on a network and i would like to use the login and the password of the network (windows) to check the user. Is it possible? I found several examples to retrieve the windows user, but thats not what i need. I want that the Access DB (2007) to check the user name to the password of the network. Note that the user login in to the DB can be different from the user that logged in the computer (some computers are turn on at start of the day by one user, used along the day by several users and turn off at the end of the day).
    Thanks for any advice

  22. Once someone has logged into the network, their login is confirmed. If you want to have them use the same password in your Access app, you will need to be able to connect to the Active Directory. While theoretically possible, I’ve not found a practical way of doing it.

    It is just because some computers are public, that I use my own login rather then the Windows login.

    • Hi
      Thanks for your help.
      I was able to use a function made in Oracle to check the user name and password. In Access i use a function that generates a string with the user and the password, and sends that string into sql format to the oracle server. If the password is correct, the function returns true, and if its not returns false.
      Best regards

  23. HI (again)
    I would like to add a message (a ballon) so that, when the user moves to the password box, if Caps Lock is on, such ballon pops up warning the user, much like the way it works when we log in windows.
    Is it possible?
    Thanks

  24. Scott-
    I have followed your security login blog to the tee….or so I thought. Everything seems to work great except once my final form is opened, it is still capable of being edited even with Read only access. I think my problem is not knowing exactly where the following should be located……

    The final piece to this is using the AccessLevel to restrict access to various parts of the application. This is done by checking the Access level in one of two ways. You can reference the column in the combobox by using the expression:
    Forms!frmLogin!cboUser.Column(4)
    or using the expression:
    Dlookup(“[AccessLevel]”,”tblUser:,”[UserID] = “ & Forms!frmLogin!cboUser)

    Is this on the Login, MainMenu or the Form?

    If you have an email, I can just email you my DB so you can see what I’m referring to easier.
    Thanks for any assistance!!

    • Jeannie,

      The key here is that once login is successful, the login form is hidden (Visible property set to False). So their access level can be retrieved using either of those two expressions. Where you use the expressions depends on your needs. For example, lets say you have a form and you want to restrict people with Reader level access from editing. So you could put code like the following in the on Open event of the form:

      If Forms!frmLogin!cboUser.Column(4)=4 Then
      Me.AllowEdits = False
      Else
      Me.AllowEdits = True
      End If

      So I can’t tell you exactly where to put those expressions. Generally it would go in the On Open or On Current events of the form to set the form up the way you want it.

      Scott

  25. Scott – Thanks for posting this – it’s help me put security on a database I am working on for my user group I manage. I’ve run into a problem when testing resetting a user’s password though, and when I execute the Login form on a user with the PWReset field set to Yes, I get a window asking me to “Enter Parameter Value” with the identifier listed in the dialog box being the value of the UserID that should transfer to the Password reset form.

    This problem has been driving me crazy and I can’t figure out what is wrong with the code. I went so far as to re-create your example above in my database just to see if it worked, and unfortunately it didn’t (same dialog box asking for the parameter value.

    Here’s the code I’m working with (modified to fit my database and field names) and I’m wondering if you have any idea as to why the UserID value isn’t being picked up. It only happens when the code executes the DoCmd.Open form line and the Password Reset form’s source is the same table that the LoginUser combo box is sourced off of. Many thanks for checking into this!

    References for field names
    LoginUser = cboUser
    LoginPassword = txtPassword
    BCMUserID = ID

    The Code
    ‘Check that User is selected
    If IsNull(Me.LoginUser) Then
    MsgBox “You need to select a user!”, vbCritical
    Me.LoginUser.SetFocus
    Else
    ‘Check for correct password
    If Me.LoginPassword = Me.LoginUser.Column(2) Then
    ‘Check if password needs to be reset
    If Me.LoginUser.Column(4) Then

    DoCmd.OpenForm “frm_PasswordChange”, , , “[BCMUserID] = ” & Me.LoginUser

    End If
    DoCmd.OpenForm “frm_Start”
    Me.Visible = False
    Else

    MsgBox “Password does not match, please re-enter!”, vboOkOnly + vbExclamation

    Me.LoginPassword = Null
    Me.LoginPassword.SetFocus
    End If
    End If

      • The exact prompt is as follows: “Enter Parameter Value” and the identifier or expression that’s displayed within the box is the userid that corresponds to the combo box on the Login form. It’s displaying the value that it is essentially asking for, and for some reason not relaying to the password change form.

        Looking at the Password Change form it’s bound to the tbl_BCM (same as the login form’s userid field. The new password field is UNBOUND and the confirm password field is bound to the tbl_BCM.BCMPassword field.

        I found this Microsoft article regarding a similar problem, but I can’t derive any solution from it:
        http://office.microsoft.com/en-us/access-help/why-does-access-want-me-to-enter-a-parameter-value-HA010274377.aspx

        In comparing my code and setup to another database that has it’s Login/Password Forms working properly, I can’t find any difference, but something’s not right since mine is not work. Thanks in advance for the help.

      • If you get an Enter Parameter Value prompt, it means that Access can’t find the object being referenced. So if its prompting you, for example, for Forms!frmLogin!cboUser, then its not finding it. This could be because frmLogin is not open or there is a mispelling somewhere.

        Scott

      • So if Access is executing the code,

        DoCmd.OpenForm “frm_PasswordChange” , , , “[tbl_BCM.BCMUserID] = ” & Forms!frm_Login!LoginUser

        it’s calling the LoginUser field from the form “frm_Login” (which is the form that’s open), and when I press cancel on the Enter Parameter Value prompt, I go into Debug mode in VBA and as I mouse over the “Forms!frm_Login!LoginUser” the window that appears tells me that that value for LoginUser is equal to “WM44147” so I don’t understand how Access can’t find the object being referenced when it says it has a value… Does that make sense at all?

        Is Access looking to the form being called (frm_PasswordChange) for that value or the current form that the code is executed from (frm_Login)? The Login form is unbound with the “LoginUser” field bound to the tbl_BCM table setup the way you described above. Is there something wrong with the syntax of the DoCmd line?

      • Okay, so after giving it some time, I took another look at the code and have a question in the DoCmd OpenForm line…

        You say that “The Password Reset form (frmPasswordChange) is bound to tblUser,” so when you reference [UserID] in DoCmd.OpenForm “frmPasswordChange”, , , “[UserID] = ” & Me.cboUser, does UserID refer to the field on the Password Change form, or on the Login Form?

  26. Hi Scott,

    Thank you for the detailed directions. I have my login security working now. However, I’m using Access ’10 and have some users on Access ’07 that are now unable to open my database (they were able to get in before). Adding the login security was the main change I made to the database, though I’m looking for other possible problems as well. Do you know if any of the features you use in the login security would affect the backward compatibility? I haven’t had to deal with these types of issues until now.

    Thanks,
    Andrea

    • I have used this code in both 2007 and 2010. However, there can be issues if you compile an app in 2010 for 2007 users. I would open the app in 2007, Compile the code then save it.

      If that doesn’t work, let me know exactly what problems the 2007 users encounter.

      Scott

  27. Hi Scott,

    Thanks for the great code and instructions on setting it up. I am wondering if it is possible to set it up so that the password expires after x amount of time? I have set things up pretty much exactly as you have in your example

    Cheers,

    Andy

    • Andy,
      Sure, I would add an effective date to the users table. At each login check to see if that effective date was prior to your expiration period. If it is, force the password reset to true so the next login would require them to change it.

      if you need some help coding that let me know.

      Scott

  28. Hi .. i tried your code.. but the part of code If Me.txtPassword = Me.cboUser.Column(2) , is not correctly working.
    txtpassword has value “qa” qhich is masked. Me.cboUser.Column(2) also has value “qa” masked. But when the code is run, Me.cboUser.Column(2) is shown as null. any thought

  29. Scott, your example is amazing and worked perfectly.

    I previously had a login that only required a user to select their name (no security) but kept that user active throughout my forms.
    The login cmd button set the user to current user using the macro builder –
    “SetTempVar” = [cboCurrentUser] and then opened form “home” where at the top I had “I am:” [cboCurrentUser] and as its default value was
    =[TempVars]![CurrentUserID])
    Finally my question is, could you show me how to use the [cboUser] from “frmLogin” in your example to replace my current active user on my forms. I’ll be linking this user at times to tables such as if a goal is completed by an employee, this current user will enter the completed goal in the record (if that even matters).

    Thanks for any help and for the great example you’ve already provided.

    • As it says in the blog, you reference the value using the syntax:
      Forms!frmLogin!cboUser
      Since frmLogin gets hidden after the password is authenticated, you can reference the value stored in the combo at any time. Since the combo also includes other info about the user like their access level, you can reference those values as well, using the Column property.

      • I was able to reference forms!frmlogin!cbouser easy enough and now it shows the current user. I just had to change from combo to text box, however changing to text box and changing the control source is now keeping me from linking the current user to a table so they’re recorded as the “entered by” in the record. I know this is off topic but would you know how I could overcome this one issue? I’ve tried putting the cboUser in the default value and changing back to a combo and referencing cboUser in the row source while “Entered By” remains the control source and I get a #Error

  30. I’m a little confused here. What control did you change from a combo to a text box? Not the one on the Login form?

    If you need to pick up the logged in user to store in a table, then you have to do this in code or use the Default value.

    The way I have things setup, cboUser on frmLogin stores the EmployeeID. To capture the user I would use EmployeeID as a foreign key in any table that needs to capture the user. So I would set the control source of a control to the EmployeeID FK. And I would populate that control either by setting the default value to:

    =Forms!frmLogin!cboUser

    or use a code event to assign the value:

    Me.FKcontrol = Forms!formLogin!cboUser.

    Doesn’t matter what type of control FKcontrol is. if you want to display the Employee name, then I would make it a combo and use a RowSource like:
    SELECT EmployeeID, Lastname & “, ” & Firstname AS Fullname
    FROM tblEmployee.
    Then hide the first column.

    Scott

    • Thanks, that worked, again something simple and me just overcomplicating it. I shouldn’t have changed the row source that I had, just the default value to what you stated (doh). I have been self-teaching Access for several months and some days I take a leap forward, others a leap back. A very exciting and frustrating program to work with. I appreciate all your help though, makes a nice and easy user access add-in.

    • Scott, I’m playing with your loginexample and I’m trying to add code to direct the user to different forms based on their access level instead of directing to “frmMainMenu” for all. For example, I added separate blank forms “administrator”, “editor”, “reader”.

      Is there a way I could amend the login code or will I have to create a navigation form (frmMainMenu) like you did with separate buttons and navigate from there using something like below:

      Private Sub cmdAdmin_Click()
      If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 2 Or 1 Then
      DoCmd.OpenForm “administrator”
      Else
      If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!frmLogin!cboUser) = 3 Or 4 Then
      DoCmd.OpenForm “reader”
      End

      Which actually is giving me an error of “Block If without End If”

  31. Scott, I have downloaded your login example and tried to use it. Since I don’t know the password of each user, I close the form “frmLogin” and edit the password in tblUser. Then, I re-opened the frmLogin, chose the User and keyed in the password, then press “enter”. But nothing happened afterwords. The curser just went back to the User combo box. I would like to know how can I operate this database. Many thanks!

    • What should happen is it should open the main menu form or give an unmatched error. I just tested it and it works fine. The blog tells you that the password for each user is welcome. Not sure why you are having a problem. I would try extracting it again from the zip file and just using welcome.

      Scott

  32. I have a question about frmPasswordChange. I had trouble using the form the way it was set up in the example. To get it to work, I added in the UserID as a text box on the form and had to make both the UserID text box and the PWReset check box visible in order for the changes to take effect (for the password to change for that user, and to make PWReset set to “False”). When I made these changes, everything works fine. However, I still want to be able to hide these two objects. Does it sound like I may have missed a step? I haven’t worked much with the Visible property before.
    Thanks!

  33. Scott, this blog is great. My question is: how do you require mixed character (1 upper case, 1 special characters and 1 number x 12 characters long) passwords in Access 2010 and be able to verify? Thanks boss.

    • Interesting question, Tony. The first thing I would do is test the length:

      If Len(Me.Password)<12 Then
      MsgBox("Password must be at least 12 characters")
      Me.Password = Null
      Me.Password.SetFocus
      Exit Sub
      End If

      Next I would loop through the password, using the ASC() function to examine each character. Something like this
      For i = 1 To Len(Me.Password)
      intChar = ASC(Mid(Me.Password,i,1))
      SELECT CASE intChar
      Case 48-57
      intCheck=intCheck+1
      Case 65-90
      intCheck=intCheck+1
      etc.
      Case Else
      intCheck = intCheck+0
      Next I

      You then check that intCheck is at least 3. That would mean it detected at least 1 upper case, 1 number and 1 special character

      Disclaimer: this is aircode off the top of my head and untested. But should give you the idea.

      Scott

  34. Hello Scott,I am just a beginner in access and I’m using Access 2007. I happened to check your blog and found it great. I downloaded your examples here and they are working fine. but when I modify it to my requirement I am getting a run-time error message for this following code from frmMainMenu :

    If DLookup(“[AccessLevelID]”, “tblUser”, “[UserID] = ” & Forms!FrmLogin!cboUser) = 2

    it says,
    Run-time error ‘2450’:
    microsoft access cannot find the referenced form ‘FrmLogin’.

    could you pls help me on this?
    Thanks in advance.

  35. Not sure I follow the question. The purpose of the Accesslevel is to allow different users different access to the app. So some people can only view records, others can edit. Some can do management tasks etc. So the Access level would need to be assigned to each user. If there is no need to restrict access to certain users, then you can just eliminate any code that checks the Access level.

    Scott

  36. Scott, I know this isn’t the correct form to ask this question, but I don’t know where else to ask. I have created a table and a word document with linked merge fields in it. My question is how do I write a code so that when a button is clicked it will show a) a print preview and b) print the report using the word document. Again I’m sorry for using this blog, can you point me in the right direction? Thanks

  37. Tony,
    You need to use Office Automation code to open your Word document. However, this is not my strong suit. I would suggest using answers.micr0soft.com or utteraccess.com to ask for more help with this.

    Scott

  38. In my database homepage, i got several forms liked with buttons. Every user is linked with some buttons and when the user login, they should be able to access the buttons defined for them. How to do this?
    I want to keep their accesslevels in the central database, so that i can add & remove access with out changing the users frontend database.

    • Well first, all (except maybe temp tables) tables should be in the back end. So yes, the Access levels should be in a user table in the back end.

      The article touches on how to do this. When you open the form, you test the user’s access level. In the example listed in the article, the Access level is part of the User combo Rowsource so its read from the combobox. You can then set the visible property of each control depending on the Access level. You probably want to use a Select Case to set each control according to Access Level.

      Scott

  39. Got it!!! i tried and its working, but the issue is, i have given the access level and the next time i want to add, change or delete the access level, how to control it with out affecting the frontend database, this will help, otherwise every time i modify the access, i got to give the new front end database to all the users.

  40. Scott,

    I have a Login Form that upon successful completion will open up the main form consisting of Faculty Records. I need some code to filter the main form to the record that belongs to the Faculty Member that just logged in.

    I have been searching for weeks on how to get this piece but no luck – I apologize if something similar has been asked and I did not see it. Thanks.

  41. I’m so close (and thank you for that!)…I am using Option 3, above (restricting records on a form to ones assigned to the current user). Between the frmEntryForm and the frmLogin, I have a Customer Search Dialog form (Unbound) that will pull up all records in the Table, with this in the record source (forgive the lack of naming convention):
    SELECT [Name Search Query].txtInsured, [Name Search Query].[txtTypeofInsurance], [Name Search Query].[dteInception], [Name Search Query].[dteExpiration], [Name Search Query].cmbStatus, [Name Search Query].[txtCertNumber], [Name Search Query].dteAuditDate, [Name Search Query].ID FROM [Name Search Query] ORDER BY [Name Search Query].[txtInsured];
    I would like for a list of only those records to appear in this Dialog Box that are assigned to the Reviewer/User. If there are none, the list returns blank and I have a button to enter a new one.
    I think I’ve gotten myself confused. Is this enough information to help me?
    Thank you,
    LBinGA

    • Is there a field in Name Search Query that identifies the user? Does it match the value stored in cboUser on frmLogin?

      That’s what you need. So lets assume its called userID, you would add a WHERE clause to your SQL statement:

      WHERE UserID = Forms!frmLogin!cboUser

      Scott

  42. Thank you, Scott. The Name Search Query now looks like this:
    SELECT tblIRAuditInput.txtInsured, tblIRAuditInput.txtTypeOfInsurance, tblIRAuditInput.cmbStatus, tblIRAuditInput.dteInception, tblIRAuditInput.dteExpiration, tblIRAuditInput.txtCertNumber, tblIRAuditInput.ID, tblIRAuditInput.dteAuditDate
    FROM tblIRAuditInput, tblUser
    WHERE (((tblIRAuditInput.txtInsured) Like [Enter the first character(s) to search by: ] & “*”) AND ((tblUser.UserID)=[Forms]![frmLogin]![cmbAuditor]))
    ORDER BY tblIRAuditInput.txtInsured;

    And the Row Source of my Customer Search Dialog Box now looks like this:
    SELECT [Name Search Query].txtInsured, [Name Search Query].cmbAuditor, [Name Search Query].dteInception, [Name Search Query].dteExpiration, [Name Search Query].cmbStatus, [Name Search Query].txtCertNumber, [Name Search Query].dteAuditDate, [Name Search Query].ID, tblUser.UserID FROM [Name Search Query], tblUser WHERE (((tblUser.UserID)=[Forms]![frmLogin]![cmbAuditor])) ORDER BY [Name Search

    I’m still missing something though…or doing something wrong. It keeps returning 0 records. Do I need to show UserID on the frmEntryForm?

    The vba I have running the Name Search Dialog Box is as follows. It keeps returning an error on the SyncCriteria:
    Private Sub OK_Click()
    ‘Dimension variables.
    Dim formname As String, SyncCriteria As String
    Dim frm As Form, rs As DAO.Recordset

    ‘Set the formname to “IRF Input form,” the form that will be
    ‘synchronized (opened).
    formname = “frmEntryForm”

    ‘Check to see if the Main form is open. If it is not open, open it.
    If Not SysCmd(acSysCmdGetObjectState, acForm, formname) Then
    DoCmd.OpenForm formname
    End If

    ‘Define the form object and Recordset object for the Main form.
    Set frm = Forms(formname)
    Set rs = frm.RecordsetClone

    ‘Define the criteria used for the synchronization.
    >>> SyncCriteria = BuildCriteria(“ID”, dbLong, Me.Like_Search.Column(7))

    ‘Synchronize the corresponding record in the Products form to
    ‘the current record in the subform.
    rs.FindFirst SyncCriteria

    ‘If a record exists in the Products form, find the matching record.
    If rs.NoMatch Then
    MsgBox “No match exists!”, 64, formname
    Else
    frm.Bookmark = rs.Bookmark
    End If
    DoCmd.Close acForm, “Customer Search Dialog”
    Exit_ReportList_Click:
    Exit Sub
    Set rs = Nothing
    End Sub

  43. Also, this code is asking the user to change the password every time they do a search for a record. I know I’m just right there at it, but I’m having trouble closing the deal! Thank you for any help you can provide.
    LB

  44. Hi Scott: Yes, this option: limit the customers that can be selected to those for the current user. I want only the current Users records to appear on the Name Search Form AND for the current user to only have access to “their records” as a Reviewer, unless they are Admin level. Admin level gets access to all records.
    Thank you!
    LB

  45. OK, by restricting the RowSource to show only customers associated with that user, you effectively limit the form to showing those. But it would be more secure to limit the Recordsource as well.

    Where I think you are going wrong is you need to have a UserID field in your data tables. For example; tblIRAuditInput would have to have a UserID field to identify the User associated with that record. That’s the field you need to compare with the cboUser on frmLogin.

    Scott

  46. I have a field called cmbAuditor that matches the cboUser. Thinking that maybe having a FName & LName was throwing it, I combined both of those into UserName on tblUser and changed the Row Source for cboUser on frmLogin to:
    SELECT tblUser.UserID, [UserName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID FROM tblUser ORDER BY tblUser.[UserName];

    I then changed the Name Search Query to this:
    SELECT tblIRAuditInput.txtInsured, tblIRAuditInput.cmbAuditor, tblIRAuditInput.cmbStatus, tblIRAuditInput.dteInception, tblIRAuditInput.dteExpiration, tblIRAuditInput.txtCertNumber, tblIRAuditInput.ID, tblIRAuditInput.dteAuditDate
    FROM tblIRAuditInput
    WHERE (((tblIRAuditInput.txtInsured) Like [Enter the first character(s) to search by: ] & “*”) AND ((tblIRAuditInput.cmbAuditor)=[Forms]![frmLogin]![cboUser]))
    ORDER BY tblIRAuditInput.txtInsured;

    When the Login Form comes up and the name & password are entered, both the Enter Parameter Value dialog box appears and the Enter New password box appears, every time. I click through the parameter box to see all records for that user, but none appear in any case. I reset the password each time as well.

    Do I need to set up a relationship? Did I put these items in the wrong place? Can you even tell at this point? 🙂 I thought it would be a breeze to modify your code to work with a filtered form, but I’m apparently having a hard time with so many components.

    Thanks for whatever help you can give.
    LB

    • Ok, First, there was nothing wrong with your Rowsource for cboUser or your Users table. You should return those to the way I described them. The problem is you were using the wrong filed as the Left side of your WHERE clause. You should have been using cmbAuditor, assuming that contains the same value as UserID.

      Second, I don’t understand why you have the Parameter prompt in your Name Search query.

      Third, the reason why the parameter comes up is you are using it as the Recordsource for the form you call when the password is entered. You shouldn’t be using a parameter prompt for that. The WHERE clause matching cmbAuditor will restrict the form to only the user’s customers. If you want to find a specific customer, add a search combo to form.

      Fourth, The reason the change password form comes up is you are not resetting the PWReset field once the user enters their own password.

      Scott

  47. Is there a way that Access can require the user to change their password? What I mean is, the administrator creates a username,initial password and assigns an access level. After the user logs on for the first time, have Access require them to change their password. After a preset amount of time, Access will require them to change their password and not allow repeats or modification of their current password (as well as verify complexity and length)? Am I trying to do too much? Thanks.

    • Not at all. About 75% of programming is IF…THEN…ELSE or looping. You test a condition and take an action based on the condition.

      So, to expire a password, you need to capture the date the password was changed or assign an expiration date. Every time the User logs on, you test the date against the current date and, if equal or past you require a change. If you want to prevent reusing passwords, you need to maintain a separate table with a history of passwords used. But frankly, it seems like more work for little value. Access is not very secure and any security you use will not deter a determined person. What value do you think you get from imposing such security?

      Scott

      • I think what I’m trying to accomplish is to create a Oracle/SQL security environment for a small organization without having to pay the Oracle/SQL price tag. I know Oracle offers a express version but it’s not as user friendly as Access and SQL also isn’t as easy to create forms,reports,… as Access. I thought about using either of those for the backend but I’m not versed enough to put it all together.

  48. Scott, a short time back I asked “how would you write code to require two-factor authentication at log on?” and you answered “Basically, I would use a nested IF. If the password matches, then display the second factor and test for the correct answer. Very similar to the code that is there now”. I forgot to define my question a bit.
    For the security question I wanted the second factor to be chosen from a list of questions (drop-down), where after the initial password is entered and verified, the user will select a question from a drop-down list and answer it correctly before being allow to gain access to a switchboard. This is where the access level will be applied. For instance, the user logged on (username, password), selected second factor question – answered and is now allowed to access the switchboard. At this point the user wants to access a person’s membership record but due to the access level applied at logon, they can select the desired record but can only view and print. Sorry that was a long winded explanation. It’s the creation (coding) for the drop-down second factor question/answer routine that I am looking for assistance with. Thank you

    • First, you would need a table with the canned questions. This table would be the RowSource of your combobox for them to select a question. You then add 2 fields to your Users table. One to hold the QuestionID from the selected question, the other to hold the Answer.

      As I said in reply to the other question, you then need to use IF statements to test that the entered answer matches, then proceed based on whether it does or not.

      But I’ll also repeat what I said above about overkill. If you really need a high level of security, you might want to use a different product or, at least, put your data in SQL Server.

      Scott

  49. Tony,
    You can use SQL Server Express as the back end. You can still use Access for the front end. But the question still becomes why do you think you need such a high level of protection? Corporate users should know that they shouldn’t mess with corporate data or software.

    Scott

    • Scott,
      I guess for the same reason why they invented locks; they keep honest people honest. It’s for those that aren’t. Our secretaries maintain personal information (SSN, DOB, Marital Status …), information that is private and not everybody needs to know. There are always people that want to pry into other peoples affairs. Would that connection be made through ODBC? I tried to export to it and it wasn’t very successful (can’t access the SQL DB).

  50. So I’m adding this to a database as an added layer of security but the code for verifying the username and password keeps giving me a compile errir: Syntax Error. I have the EXACT code as you have, but it wont work… Any Thoughts?

  51. Got it! Thanks, Scott! It all came together with your suggestions. I actually started from scratch, adding UserID to tblAuditInput and corrected the Row Source. I made a one to many relationship between it and the UserID in tblUser. The user can now login and only see and more as importantly, search, those records that apply to him/her only.
    One little hiccup to overcome. When the logged in user creates a new record, their UserID is not automatically assigned that record. I think what I need is a junction table between tbleUser & tblAuditInput, creating a many-to-many relationship here. Is that on track? Is that enough info to go on?
    The Row Source of the Like Search is as follows:
    SELECT [Name Search Query].txtInsured, [Name Search Query].txtTypeofInsurance, [Name Search Query].dteInception, [Name Search Query].dteExpiration, [Name Search Query].cmbStatus, [Name Search Query].txtCertNumber, [Name Search Query].cmbAuditor, [Name Search Query].ID FROM [Name Search Query] WHERE ((([Name Search Query].UserID)=[Forms]![frmLogin]![cboUser])) ORDER BY [Name Search Query].txtInsured;
    There are two hidden fields on frmEntry: User ID & ID
    frmEntry’s record source is: qryForm and the Criteria in UserID is:
    [Forms]![frmLogin]![cboUser]
    Thank you for any insight you can provide.
    LB

  52. “When the logged in user creates a new record, their UserID is not automatically assigned that record. I think what I need is a junction table between tbleUser & tblAuditInput, creating a many-to-many relationship here. Is that on track? ”

    No I don’t believe that’s the way to go. It would only be the way to go if there are multiple users assigned to a record. If that’s not the case, then you need a UserID field in tblAuditInput and you need to set its default value on the form to reference cboUser on frmLogin.

    Scott

  53. Great info!! I’ve been using this code for about three years and no problems so far. But to expand on the idea, I was wondering about something.
    Is it possible on the login form to have a hidden button so that a developer level user (myself) could login, (with hard coded user info in the VBA, which would be password protected).
    In case the administrators delete the developer account and admin accounts by error (I’ve added a disable/enable bypass code for the Shift key).

    • Glad you have found this useful.

      I’m not sure I understand what you are looking for. If you have instituted a way to disable/enable the bypass key, then you can get into the app that way. If the someone deletes the all the accounts assigned as Admin level, you can restore from backup. So I don’t see what value this button would have.

      Scott

      • Let me explain a little further. The disable/enable button is on a form that only the admin’s have access. I created the data base to track personnel/student arrival and departures. I will no longer be working for this division and I’m transferred approx 3000 km away.
        I would like to put in a safety button (hidden) so that if anything goes wrong (ie: the admins delete or change there access) then via a cloud server or a old fashion copy to a stick and send it to me, I’ll be able to get in and save the day. I’ve mentioned to them t make a back up copy any time that they make changes. But unfortunately you can’t always count on humans.
        The hidden button opens up a different login form, what I’m trying to accomplish is when I enter the login details (to which would be saved in VBA only) that it will open up the DB and enable the bypass key. (Or even just enable the bypass key.)
        And just imagine, as I was writing this reply, I figured out how to do it. I’ve simply added the enable bypass code when I click on enter. Here is what I got:
        Private Sub ByPassEnter_Click()
        On Error GoTo Err_bEnableBypassKey_Click
        Dim strInput As String
        Dim strMsg As String
        Beep
        strMsg = “Do you want to enable the Bypass Key?” & vbCrLf & vbLf & “Please key the programmer’s password to enable the Bypass Key.”
        strInput = InputBox(Prompt:=strMsg, Title:=”Disable Bypass Key Password”)
        If [Forms]![zfrmByPassLogin]![ByPassNM] = “USERNAME” And [Forms]![zfrmByPassLogin]![ByPassPasse] = “PASSWORD” Then
        SetProperties “AllowBypassKey”, dbBoolean, True
        Beep
        MsgBox “The Bypass Key has been enabled.” & vbCrLf & vbLf & “The Shift key will allow the users to bypass the startup options the next time the database is opened.”, vbInformation, “Set Startup Properties”
        Exit Sub
        End If
        Exit_bEnableBypassKey_Click:
        Exit Sub

        Err_bEnableBypassKey_Click:
        MsgBox “Runtime Error # ” & Err.Number & vbCrLf & vbLf & Err.Description
        Resume Exit_bEnableBypassKey_Click

        End Sub

        Thanks a great deal for people like yourself that take the time to assist others who are less DB savvy. Going through the history of this forum, and your still helping 4 1/2 years after your lent this code. Thank-you again!!

  54. Pingback: Access 2010 - Security and Login

  55. plz help to make a form for student management system with have all EDU functions. the form will be at least 6 i.e. one login form, another search, another student_info, another form which do CRUD function or any other search function form etc with vba code

    • Chrissy,
      What help are you looking for? My blog article shows you how to do a login form and use it to apply security to parts of your application. If you need help with implementing it, please ask a specific question. If you need help with other parts of your application, I would suggest posting on sites like utteraccess.com, answers.microsoft.com or askmehelpdesk.com. The first specializes in Access help, the other two have forums specific to Access. I monitor all three.

      If you are looking for fee based help, let me know and we can discuss that.

      Scott

  56. Hi Scott, I’ve just discovered this site and have seen a lot useful tips here that will help me with a program I’m working on. My VB skills are still at beginner level and I’ve taken longer than usual with this program. I need your help. My application (Access 2010 database with data entry forms) should have a login form which takes me to a switchboard from which the user can only access the forms that they’ve been authorised to access. First problem, my data entry form (PaediatricForm) has an Input box which uses a subform (PaediatricSubform) to search for records based on [TripID] and [PatientID]. At first it was working. It would search as I typed but now, typing in the box gives an error 424 “object required” on the 6th line of code. This is the code which I have. what is wrong it?! Your help is greatly appreciated.

    Private Sub cmdSRCHBOX_Change()
    If Len(cmdSRCHBOX.Text & vbNullString) = 0 Then
    PaediatricSubform.Form.Filter = vbNullString
    PaediatricSubform.Form.FilterOn = False
    Else
    PaediatricSubform.Form.Filter = “[TripID] Like ‘*” & cmdSRCHBOX.Text & “*’ OR [PatientID] Like ‘*” & cmdSRCHBOX.Text & “*'”
    PaediatricSubform.Form.FilterOn = True
    End If
    End Sub

    • Can you show exactly which line is erroring out is it the line after the Else or before the End If? I’m also not clear exactly what you are trying to do. Are you trying to create a filter in the subform as you type each character? Isn’t that a drain on the system?

      Scott

      • I recently realised that on this site, each comment has a reply button so one can go back to it and provide update. This issue is now resolved. I’d created my subform with a different program name and forgot to change it to PaediatricSubform that is why I kept getting error 424. S’all good now. Thanks!

  57. Hi Scott, thanks for your response. It was the ELSE part but I’ve fixed it now. I’d forgotten to change the subform’s program name to PaediatricSubform. Also, I didn’t make the subform, the Record source for the control on the main form. Hope that makes sense but anyway that one is fixed now.

    I have about 10 data entry forms (representing 10 categories) to be used by 6 physicians. Most of the physicians will have exclusive access to one form each but some will have exclusive access to more than one form. In summary, whatever form the physician accesses, he’ll have exclusive access to. Now they will ALL have the same Access Level but In this case, to ensure that a physician does not access unauthorised forms, how do I adapt your code to achieve this? I’m thinking I should give them individual accesslevel ID…?

    my login form is called frmLogin.
    I have created the User Table and AccessLevel table as well.
    combobox – cmbUser
    text box – txtPassword

    Private Sub Form_Open(Cancel As Integer)
    If Forms!frmLogin!cmbUser.Column(4) 5552 Then
    MsgBox “You are not authorized to view this form”
    Cancel = True
    DoCmd.Close acForm, “PaediatricForm”,acSaveNo
    Else
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
    End If
    End Sub

  58. hey Scott I’m having a bit of trouble with the Row source query for my Login form. In Query design I have
    col1 Col2 Col3
    UserID Expr1:[LastName] & “,” FullName: &[FirstName]
    tblUser tblUser tblUser

    but it says that my expression has invalid syntax. Could you point out what I’m doing wrong here please? I can’t seem to pick it out. Maybe been staring at the screen for too long.

    The UserID combo box only shows the LastName with the comma

  59. Hi Scott, it worked. many thanks for your help.
    I did not use a modal dialogue form to create my login form and I have the below code but I’m not sure in which event to put it…?

    DoCmd.ShowToolbar “Ribbon”, acToolbarNo

  60. sorry I didn’t see your response to my AccessLevel question until now. Yes each form represents only one category and there are six physicians. Four of them have access to one category each but two physicians each have access to three categories – making a total of 10 forms/categories.

    • You didn’t quite answer, Is the Only difference between the forms, what category they display? Are are there other differences? If the only difference is the category, then I would create a filter for each physician. So I would have a field in the Users table for categories. So lets say each category had an ID and Dr A can see 1, 3, 5 While Dr B only 2.

      So behind the button that opens the form, I would put code like:

      Dim strFilter As String

      strFilter = “CategoryID IN(” & DLookup(“[Categories]”,”tblUsers”,”LoginID = ‘” & Forms!frmLogin!cboUser & “‘”)

      DoCmd.OpenForm “formname”,,,strFilter

      this should open the form allowing the user to only see the catyegories assigned to them.

      Scott

      • Yes each form is totally different from the other – i.e. category heading and content. Right now I have a column in my User table where each user has a unique accesscode. It works, sort of, but I think down the line maintenance might be a hassle i.e. when employees move on. Your way sounds more professional. I think I’ll need to create a Category table for this and then have the Category ID as a field in the User table. I wouldn’t want to have more than one column in the user table for this. Is it possible to have multiple values in a column?

      • You should have had a Category table in the first place. While you can have multiple values in a field, its better to use a child table.:

        tjxUserCategory
        UserID
        CategoryID

        This can then be used to determine if a User has access to a category.

        Scott

      • besides worrying about reading multiple values in a column, how can I put this code into the switchboard button?! In design view there is a generic button with label, Item text. It represents ALL the buttons of the switchboard not just the ones that opens each form?

      • Scott the switchboard(s) are already built and with the manager. However, if I go to Design view, the buttons are represented by only one generic control button and it has a generic label. Anything thing done to this button will apply to ALL the items on the switchboard including the “Go Back” button. Each category is represented on the secondary switchboard by a button. My question is:
        How do I put code in the button when it represents more than the categories i.e. including the EXIT and GO BACK buttons??

      • The Switchboard Manager in 2010 uses Macros not VBA code, so its harder to modify. the Switchboard form itself is a Continuous Form that is populated from the SwitchboardItems table. As I recall, the code behind the button is a series of IF statements which test the current record to see what command should be executed.

        I would recommend NOT modifying the code there, but doing your validation in the command being called for that option.

        Scott

  61. I tried to adapt your code for one of the forms but I am getting error code 2450 “database cannot find the referenced form ‘frmLogin'”

    I gave each user a separate accesslevelID from the others so for this particular user, his is 5552 but in VB the 1st line of code is highlighted in yellow. Could you advise what I’m doing wrong please?

    If Forms!frmLogin!cmbUser.Column(4) 5552 Then
    MsgBox “You are not authorized to view this form”
    Cancel = True
    DoCmd.Close acForm, “PaediatricForm”, acSaveNo
    Else
    Me.AllowEdits = True
    Me.AllowAdditions = True
    Me.AllowDeletions = True
    End If
    End Sub

  62. Thanks Scott!!
    I set Me.Visible to false in the LostFocus event of the frmLogin but I find that when I double click on the form (in the navigation pane) it still has my login name and password showing. How do I clear this form after I login?

      • Just seen your replies. Many thanks for your tips Scott. I have to ‘show’ this application in about 30 minutes (just a preliminary gathering) and since I wasn’t sure about having multiple columns, I left the below code. It was working before but now, it is driving me bonkers. Both users with correct accesslevelID and incorrect accesslevel ID are being refused access to the forms. I will change to the method you suggested later on but since I’ve got only a few minutes to spare before my meeting, could you tell me what is wrong with this code??? I need to show that this working.

        Private Sub Form_Open(Cancel As Integer)
        DoCmd.Close acForm, “Switchboard”, acSaveNo
        If Forms!frmLogin!cmbUser.Column(4) 5552 Or Forms!frmLogin!
        cmbUser.Column(4) 5551 Then
        MsgBox “You are not authorized to view this form”
        Cancel = True
        DoCmd.Close acForm, “ObstetricsForm”, acSaveNo
        Else
        Me.AllowEdits = True
        Me.AllowAdditions = True
        Me.AllowDeletions = True
        End If
        End Sub

      • hi Scott, thanks for your response. The RowSource of cmbUser is the query you’d helped me with:

        SELECT UserID, LastName & “, ” & FirstName AS FullName, strPassword, AccessLevelID, PWReset
        FROM tblUser
        ORDER BY Lastname, Firstname;

        Question:
        1) according to this RowSource query, column 4 is the AccessLevel ID but I find that it does not grant authorisation to open the form even though the User has the right login/accesslevelID BUT when I use
        If Forms!…..!cmbUser.Column(“3”) “=” 5552. It grants access. Does column count start from “0” or “1” ?

        2) does VB not recognise “”? I find that even when I use it with column 3
        If Forms!…..!cmbUser.Column(3) “” 5552. It refuses access to the form

        I greatly appreciate your help

      • No, The Column # count starts with 0 so your column #s would be:

        UserID-0
        Fullname-1
        Password-2
        Accesslevel-3
        PwReset-4

        That could be the source of your problems.

        Not sure what you are asking in the second part. You have to use a comparison operator in your IF statement.

        Scott

  63. Scott, on my form I have a combo box cmbMgSO4 with values “Yes”; “No”
    If the user selects Yes, then the text box txtExtra must not be null. The user must provide the dose. For this, I have the validation rule:

    IIf([More MgSO4?]=”Yes”, Not IsNull([extra dosage]), IsNull([extra dosage]))
    This works until you violate it. Then after you correct the error, the validation text message wouldn’t go away.

    I understand that it is better to put the validation rule on the form using VB but I need assistance with the code please. I wrote this code in the AfterUpdate event of [extra dosage] – i.e. the textbox txtExtra but it has bugs. Could you tell me what is wrong with it?

    Private Sub txtExtra_AfterUpdate()
    If [More MgSO4?] = “Yes” Then
    If Len([extra dosage].Text & vbNullString) = 0 Then
    MsgBox “Please provide the extra dose!”, vbOK + vbExclamation
    Else
    [extra dosage].Text = Me.txtextra.Text
    Len([extra dosage].Text) > 0
    End If
    Else Len([extra dosage].Text) = 0
    End If
    End Sub

  64. Hi Scott, thanks for the sample supplied earlier. I am a newby to VBA and would like a simple fix if you could supply??
    If I use the “frmMainMenu” can I then set up separate forms for each level of user with the database. They will only get access to what is on the form. What code would I need and where to place it?

    • Hi Collin,
      I’m not sure if you mean separate menus or separate forms. Can you clarify? For example you could have frmMenu1, frmMenu2, fremmenu, frmMenu4.

      In the code just before you open the menu form, I would add a line:

      strMenu = “frmMenu” & Forms!frmLogin!cboUser.Column(4) (note I think that’s the correct column)
      DoCmd.OpenForm strMenu …

      So this appends the Access Level to the form name and uses a variable to open the form. Similarly, if you have one menu form, but want to open different forms based on Access Level you could do the same thing. The blog has code to show you how to check the Access level and customize a form based on it.

      Scott

  65. Hello I am so glad I found your site, I really found you by mistake, while I was
    looking on Digg for something else, Anyhow I am here now and would just like to say many thanks for a marvelous post and a all round entertaining blog (I also love
    the theme/design), I don’t have time to read it
    all at the moment but I have book-marked it and also added
    your RSS feeds, so when I have time I will be back to read
    a lot more, Please do keep up the fantastic
    job.

  66. Hello there, just became alert to your blog through Google, and found
    that it is truly informative. I’m gonna watch out for brussels.

    I’ll be grateful if you continue this in future.
    Numerous people will be benefited from your writing.
    Cheers!

  67. Hi Scott, I have several sizeable DBs currently running as mdb’s in MSA 2010. These were originally made in MSA2003 and make extensive use of ULS to guide and restrict users. With the advent of MSA2013 I am now looking at kntting a User Security system similar to yours (adding User Groups).

    The policy with all the mdb applications is similar: Guide and restrict users appropriate to their group. Prevent an amateur accessing tables & queries directly. Accepting that a really knowledgeable person will get in somehow, still make it difficult. Using ULS with a custom mdw has met these requirements so far.

    The stumbling blocks I have in building a new security system are:

    a) How to prevent a user making a new accdb and simply attaching the tables from the back end and importing all front end objects from the User db. This user would, of course, have read/write permissions to the relevant folders both locally and on the server.

    b) I can implement a new security system easily enough on most front end objects. But there typically around 1,000 querydefs in these mdb’s. Many of the mdb restrictions are implemented by ULS on the qdefs, which has been convenient and effective. In theory, I could use VBA to move qdef permissions to forms they serve, but I can see this becoming complex and messy, given the number of nested querys that are referenced in many places – not only forms, but DAO recordsets, DLookUps, etc.

    Any thoughts?

    Regards
    Tim

    • See my article on Securing Back Ends. That will help prevent just linking the tables. Another thing, is unless your users do development, do theu need A full Access license. Deploying only the runtime will inhibit this (and save money).

      I’m not sure I follow the issue here. All interaction with data should be through forms. Nothing wrong with using qdefs, but they should be behind the scenes and not exposed to users.

      Finally, if you feel you need stronger security, then move your back ends to SQL Server.

      Scott

      • Thanks for your response.

        Unfortunately some of my client’s users know that they can skip over the ‘Transvers Only’ folder by typing the full path into a windows explorer dialog. All three of the back ends in question will sometime be moved to SQL Server. This will require quite a bit of work however and clients don’t want to pay for it yet – the mdb back ends have been stable and reliable for years.

        Runtime Access would be fine except that my busiest current client has full Access on the desktops because another db (not mine) needs it.

        My users never see a querydef – all interaction is through Forms. Each Form (120-170 of them) calls library functions from its OnOpen & OnCurrent events. These functions inspect the form’s RecordsetClone.Updatable and .RecordCount properties, setting common controls etc accordingly. Thus, by simply setting ULS permissions on a form’s data source query, user access to the form and it’s data is ‘automatically’ controlled. I haven’t heard of anyone else using this strategy, but it has served me well for 15+ years.

        Otherwise I guess I will automate the setting of home-grown permissions on forms based on the query’s updateabilty and RecordCount then run the code 7-10 times after logging in as a member of each ULS Group.

        Any further thoughts very welcome…..

        Tim

  68. Hi Scott thank you very much this helped alot. Do you have any information on scheduling. Specifically I am working with MSAccess 2010 and I need to know how to write the VBA code for getting the 1st or 2nd or 3rd or 4th, Mon-Fri of the month for the next two years. Any information would be greatly appreciated.

    • I’m not sure I’m following what you need. Do you want to produce a table that shows each date for 2 years and where in the month it falls out? Or do you want to be able to identify the day of the week and its occurrence within the month and return the date? Or What?

      Scott

  69. I have a form that I place a start date and it calculates a weekly or bi weekly date, the a query captures the information and places it into a table. I need it to calculate the first Thursday of the month or Second Tuesday of every month, which ever I select.
    This is a scheduling database for activities. Im basically coming up with dates, such as every Monday for a particular group. Then I assign clients to the activities I have created.

  70. Public Sub Assign_Groups_Click()

    Dim IntInterval As Integer
    Dim NumLoops As Integer
    Dim Series As String
    Dim DateCounter As Date
    Dim Counter As Integer
    Dim TopicID As Integer
    Dim HolidayCount As Variant

    If Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Activity Frequency] = “Daily” Then
    IntInterval = 1
    ElseIf Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Activity Frequency] = “Weekly” Then
    IntInterval = 7
    ElseIf Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Activity Frequency] = “Bi Weekly” Then
    IntInterval = 14
    ElseIf Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Activity Frequency] = “Monthly” Then
    IntInterval = 28
    ElseIf Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![Activity Frequency] = “Yearly” Then
    IntInterval = 52 * 7
    Else
    Exit Sub
    End If
    ‘num loops for topics
    Series = Forms![frmScheduleActivities]![FrmScheduleActivitiesSelectSub]![TblActivityType ID]

    Select Case Series
    Case “4”
    NumLoops = 6
    Case “3”
    NumLoops = 1
    Case “2”
    NumLoops = 26
    Case “1”
    NumLoops = 10
    End Select

    DateCounter = Me.StartDate
    Me.txtDateCounter = DateCounter

    Do While DateCounter <= Last_Date
    TopicID = 1
    Counter = 1
    Me.TopicIDCounter = TopicID
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QryAddActivityDate"
    For Counter = 1 To NumLoops – 1
    DateCounter = DateAdd("d", IntInterval, DateCounter)
    Me.txtDateCounter = DateCounter

    TopicID = TopicID + 1
    Me.TopicIDCounter = TopicID
    DoCmd.OpenQuery "QryAddActivityDate"
    Next Counter
    DateCounter = DateAdd("d", IntInterval, DateCounter)
    Me.txtDateCounter = DateCounter
    Loop

    DoCmd.SetWarnings True
    Me.FrmScheduleActivitiesSUB.Requery
    Me.FrmScheduleActivitiesSUB.Visible = True

    End Sub
    This is the code I use

    • First, I would use a Select Case rather than the ElseIfs. Each ElseIf is testing the same control. So a Select Case will be much cleaner.

      Second, I would do this in a loop.

      Dim booFound As Boolean
      Dim dteDate As Date, dteCurrDate As Date

      dteCurrDate = DateSerial(Year,Month,1)
      booFound = False

      Do While booFound = False
      If Weekday(dteStartDate) = 5) Then
      dteDate=dteCurrDate
      booFound = True
      Else
      dteCurrDate = dateAdd(“d”.1.dteCurrdate)
      End If
      Loop

      If you need to look for the second occurring day of the week, ad an inner loop to count the occurrences.

      Scott

  71. I’m not sure either. I’m not 100% clear on what your code is doing. The code I gave you (which is really beyond the scope of this blog), was offhand code to loop through the days of a specified month and determine what date meets the criteria of the x occurrence of a specific day of the week. It was meant to be totally separate from your other code, though it could be added to the same module.

    If you need more help with this, there are other Q&A sites that support Access, like utteraccess.com or answers.microsoft.com

    Scott

  72. Hi Scott,
    I really appreciate your help making this sample loginForm. It helped me a lot doing my database,
    I have a question though. Is it possible when i log in with my name into the database, that name to be auto populated in a specific form?
    To be more specific: I created a database for check requests with different levels of security access (1. Admin, 2. User). The user access can only see/fill the check_request form and the Admin plus everything else, but to be more secure i want every time when somebody (it doesn’t matter user or admin) log in to the database to see/fill a check_request form his name to be auto populated in a section called requested_by.
    Thanks again for your help!

    • Sure, In the control bound to the Requested_by field set the default value to:

      =Forms!frmLogin!cboUser

      This will pull the logged in user ID into that control. If you want it to display a user name instead, you can make the control a comobobox with a Rowsource that includes the user name so the name displays, not the ID.

      This is part of the point of keeping the Login form open but hidden. You can reference cboUser to pull any of the info in its columns.

      Scott

  73. Hi Scott. Thanks for sharing the code…. I have a Switchboard with a Main Menu that contains several options; Orders, Reports, Settings, Admin Menu. What I want to do is require a login whenever someone clicks “Settings” or “Admin Menu.” Is it possible to do this at the switchboard level? It make sense to do it here rather than applying the security to every form.

  74. Hello Scott,
    I will be creating an ActiveDirectory group which will have access to this Access database. How can I make sure that only those users can login, and not any other. How can I bring in the users AD account login information and log that in the Access DB whenever accessed?
    Thanks
    Ronnie

    • Hi Sylvanus,

      There is no “everyone” here. This isn’t a public message board, its my personal blog. You can use the techniques I have written about to create a very flexible login system. If you have any questions about that you can ask here or in the Access forum at askmehelpdesk.com. If you have other general questions you can also use that site or the Access topic at answers.microsoft.com or the forums at utteraccess.com.

      Scott

  75. Sir, 1st of all I’d like to thank you for excellent tutorial.
    I am experiencing some problems w/VBA, when I debug it following message appears:

    “Compile Error: Method or data member not found” pointing to
    “.cboUser” in line:

    If IsNull(Me.cboUser) Then

    What could be the problem?

  76. Hi Scott,
    thanks for sharing! This is exactly what I was looking for.
    I have set up a combo box (so that the name is actually displayed, not the number)
    [CODE]
    SELECT tblUser.UserID, [AccessLevel] & “, ” & [Fname] AS [Name&Description] FROM tblUser INNER JOIN tluAccessLevel ON tblUser.AccessLevelID = tluAccessLevel.AccessLevelID ORDER BY tblUser.LName, tblUser.FName;
    [/CODE]
    that displays on the main menu form who’s logged in. Next to it I created a small button which should give the user the option to log out without closing the Database, but rather closing the main menu form and switching back to the login form, clearing the stored password.
    I was going to ask you how to implement that, but decided to google that quickly and what I’ve found seems to be working (though I don’t know if there’s a better way of doing that).

    Private Sub cmdLogOut_Click()
        On Error GoTo Err_cmdLogOut_Click
     
        DoCmd.RunCommand acCmdSaveRecord
        DoCmd.Close
        DoCmd.OpenForm "frmLogin"
        Forms!frmLogin!txtPassword.Value = Null
    Exit_cmdLogOut_Click:
        Exit Sub
        
    Err_cmdLogOut_Click:
        MsgBox Err.Description
        Resume Exit_cmdLogOut_Click
        
        
    End Sub
    

    Now, one question still remains: Would there be a way to not show the drop down arrow on the combo box that tracks the user that’s logged in? Disabling it still leaves it there… I thought making it not visible and creating another unbound textbox and entering =[txtUser] would be a way, but that turned out to not work at all, as it gives me the error Access cannot find the referenced from ‘frmLogin’, or upon reopening the database it gives me the UserID number again… so any help on that would be greatly appreciated!
    Many thanks

    • I’m not sure I’m following. is the user not selecting their name in the combobox? Don’t you need then to be able to do that? Are do you just want to disable it after they select the user?

      My method is to hide frmLogin once the user has been authenticated so the current user can be retrieved any time. You can have a logout option that clears the combo and password box. So I’m just not sure why you are trying to suppress it.

      Scott

      • No, I do understand they way you did it and why and I left it unaltered. It’s after a user has logged in and the MainMenu opens. There I have added the combo box that shows who’s logged in with the select query I posted above. That being a combo box, users tend to click on the arrow, although no other user can be selected, I wanted to get rid of that.
        Maybe it was wrong to use a combobox for user tracking in the first place, but I can’t figure out how to show the name and not a number in a textbox.
        [img]http://i58.tinypic.com/qrwuog.gif[/img]

  77. Ahh, Now I understand, What you can do is use a textbox with a ControlSource of:

    =Forms!frmLogin!cboUser.Column(1)

    This should display the same value the user sees when they select their name from the combo on frmLogin.

    Scott

  78. Hi Scott :

    Thanks for your post, it has been very helpful. However, I have some problems with the user level. What I need is that once I put my username and password, only when I login with the username called “ADMIN” will the database´s vba code enable Access Special Keys, Navigation Pane, Full Menus and Short Cut Menus. But if I enter with any other username then it will disable all of this. Can you show me the code for this? It will really help me.

  79. Hello Scott – I have just found your Blog and login code and I would like to say thank you for it, I have been trying to do this for a while without success.
    I have a question, I am using Access 2010 with a Navigation form with form Buttons to open each form. I have the login code working and then it directs each user to the navigation form. But I am not able to get the code correct to restrict users from certain forms when selecting a form button located on the navigation form. I have tried many suggestions listed above but It seems I am missing something,this is very frustrating. Thank you again, Nick

  80. Hi Scott:

    I need your help to understand one thing. You have set the column count to 4 and used in below query for :

    SELECT tblUser.UserID, [Lname] & “, ” & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID
    FROM tblUser
    ORDER BY tblUser.LName, tblUser.FName;

    According to this the password column will be column number 3 but when I have read your vba code you was using the below code:

    ‘Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then……

    I am new to this login creation. Please guide me why we have used column(2) in vba instead of column(3)

  81. Hi Scott,
    I am new to Access and found your blog very useful to prepare a login form and access rights in Access 2010. However i am facing a problem with one of your codes. I am using the code below
    If Forms!LOGIN!cboUser.Column(4) 2 Then
    MsgBox “You are not authorized to open this form!”, vbOkOnly + vbExclamation
    Cancel = True
    End If
    End Sub

    This is giving me an error as below

    Compile error:
    Syntax error.
    Would appreciate if you could let me know what i am doing wrong.

    Regards
    Anup

  82. Thanks Scott, yours code really helpful

    but i still need your help.
    i created autoexec macro that run
    module function

    I dont know how to stop that. I want that stoped in some AccessLevelID.

  83. Howdy! I am so glad I found this, I think it will be a huge help. I am running in to one issue that I cannot seem to resolve. I am using Access 2013, and when I put the SQL statement in to the Row Source for cboUser, Access is bracketing the quotation marks, which is causing the combobox to not work correctly:

    SELECT tblUser.UserID, [Lname] & [“], [”] & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID
    FROM tblUser
    ORDER BY tblUser.LName, tblUser.FName;

    Is there a way around this? I cannot seem to work it out. Ultimately when you click in the CBO, it asks for parameters for ” and “, and then only lists the last name.

    I appreciate any help!

  84. Hi Scott,
    Thanks for the post, I’m trying to implement similar thing to my project using yours but I do find out a bug.

    If you want to update the password BUT you dont change any values first (e.g just press enter twice in the New Password form and after that you fill in the fields), the new password will be recorded as a new row in tblUser, not changing the actual user’s password!

    Do you have advise for this issue? Thanks

    • Well that is an example of a programmer (me) thinking users will not do silly things like press enter twice instead of typing in a new password.

      You can block that by using the Before Update to check to make sure the user enters a value in the New Password control.

      Scott

  85. Hello Scott
    Awesome post. Please I jumped from 2003 to 2013 access. I need to be able to lock down the access programme (without locking myself out) in order to reap the full benefits of ur code. No use having a password checker if users can waltz into developer mode via ‘Privacy Options’ on file menu. While I apologise for posting this on the wrong forum, I really need assistance on this and am hoping you would help. Thanks.
    I am new to Access development.

  86. Hello Scott, Following your helpful suggestions to me on the Access 2007 Community Forum regarding security login, I have attempted to follow your example here in a new database, but it’s not working so far. When I select my record and enter my password and press Enter, my cursor simply returns to the Login combo box entry. I’ve copied my Volunteer table to the new database and renamed it tblUser. Then, I have adapted the procedure to use your other tables, combo box, lookup tables, etc., but I haven’t gotten past the password entry. If you would prefer that I provide more detail, such as screen shots via the community forum, just let me know and I will do that or I will follow your lead on another option.

    • Doesn’t sound like you got it.

      “When I select my record and enter my password and press Enter, my cursor simply returns to the Login combo box entry. ”

      This is not what I’m suggesting. My First point stated:
      “1) The app should open to the login page. The user should enter a user ID, or, better yet, have them select their ID from a combobox. then enter a password. I describe how this should work in my blog on Login Security.”

      So if you are selecting the record FIRST, then logging in, you are not doing it properly. Please reread the blog again. The user logs in first, and the login form is hidden. I’m not clear what record you are selecting, but if its the Volunteer’s record, then I don’t understand why. In my point 2, I see no reason to verify the info with each login.

      Please review the 5 points I posted on Answers and the instructions in my blog article.

      Scott

      • Hello Scott,

        Again, I’m not very clear in my description. I have open a new database in Access 2007. Then, I have copied your frmLogin, frmPasswordChange, frmMainMenu, etc. Then, I have copied my Volunteer table from my old database and renamed it tblUser and renamed necessary fields to match your procedure. Next, I have created the cboUser combo box and txtPassword text box and I believe I have followed your property specification guidance and coding to match your procedure. Finally, I open frmLogin and select my record (my Fullname) from the pulldown list and enter my password in the textbox. When I press Enter, the cursor moves back to the combo box space which still has my Fullname in that box and stops. It does not hide frmLogin nor open frmMainMenu nor display any error message. Incidentally, I thought the Row Source coding indicated that my password should be in Column 3 of cboUser, so I changed the coding in Sub txtPassword accordingly, but still got the same result as before. So that coding has been restored. I appreciate your patience with my struggling explanation.

  87. OK, I open frmLogin at EventProcedure AfterUpdate — this opens Private Sub Password_AfterUpdate(). I left-click next to the first line, which is the comment: Check that the user is selected. I click on Debug. I get a beep, but no action. When I choose F8, nothing happens.

  88. OK, I have your recommended security code working the way I believe it’s intended. However, I’m a little confused. The way I interpret the cboUser SQL coding, it appears that password is Column (3) and PWReset is Column (4). But, only when I modify txtpassword such that password is left as Column (2) and PWReset is changed to Column (3) does the Sub work correctly. Finally, even though the Sub is working without error, when I try to Debug by positioning the “dot” opposite a clearly executable line of code and choose Run to Cursor (Ctrl+F8), I get an error “Line is not an executable statement.” I change to other executable lines and get the same error. If I move the dot to a different line and select F8, I get a beep, but nothing else happens.

    • I think the thing you are missing is that the Column property starts with 0, not 1. So if you used the RowSource I listed:
      SELECT tblUser.UserID, [Lname] & “, ” & [FName] AS Fullname, tblUser.Password, tblUser.PWReset, tblUser.AccessLevelID

      UserID is column 0, Fullname 1, Password 2, PwRest 3 and Access LevelID 4.

      That’s why it works correctly with those column #s.

      As for using Run to Cursor, I never use that. The error could be because you are starting to run at a non executable line. The idea of setting a break point is so code stops during normal execution of the code. F8 only works when the code is being executed.

      Scott

  89. Hi Scott!
    I recently downloaded your sample and I esealy adapted it to a database.
    Your idea is very good and both forms works fine.
    But I have a problem with frmPasswordChange because after runs the code in the Procedure I have two other forms that I need to open in Popup / Modal way. This forms always send to back the frmPasswordChange. Because security reasons the other two forms are all commands disable i.e.: close, maximize, minimize, etc.
    How can I solve this problem? There are any way to bring to front the frmPasswordChange when I set to in tblUsers?
    Thank You.
    Best regards

  90. Hello Scott, I have used your Login security procedure and have it working well. Thanks. However, I’m using it also to learn more about Access VBA – slowly. I’ve noted your warning that Access is not the most secure application, but password security would seem fundamental to me. In any case, what I have found is that when I set up a password (Me.cboUser.Column(2)), then when I enter Me.txtpassword, the IF comparison is True regardless of case-sensitivity. I have written a Public Function which compares two passwords character-by-character and that function returns False, if even one character doesn’t match. I would appreciate your comment and guidance in using this in my Login security procedure.

    • If you want to require stronger passwords with case sensitivity, I see no problem in using a function to compare the passwords. Instead of this line:
      If Me.txtPassword = Me.cboUser.Column(2) Then
      you could use a function:
      If fPWMatch(Me.txtPassword,Me.cboUser.Column(2)) Then

      Your function could then compare the two passwords passed to it and return a True or False if they match.

      I may even write a sequel to my blog on doing something like that.

      Scott

      • Thank you so much, Scott. I really appreciate all your help. I followed your example, except my function named VerifyPassword, is as follows:
        Public Function VerifyPassword(Password1 As String,Password2 As String) As Boolean
        VerifyPassword = False
        Dim LenPW1 As Integer, LenPW2 As Integer, ICount As Integer
        Dim intCharPW1 As Long, intCharPW2 As Long
        LenPW1 = 0
        LenPW2 = 0
        intCharPW1 = 0
        intCharPW2 = 0
        ICount = 0
        LenPW1 = Len(Password1)
        LenPW2 = Len(Password2)
        If LenPW1 = LenPW2 Then
        GoTo Compare
        Else
        GoTo Bad
        End If
        Compare:
        ‘ Password lengths OK; valid so far
        For ICount = 1 To LenPW1
        ‘ Compare each character of passwords
        intCharPW1 = Asc(Mid(Password1, ICount, 1))
        intCharPW2 = Asc(Mid(Password2, ICount, 1))
        If intCharPW1 = intCharPW2 Then
        GoTo NextChar
        Else
        GoTo Bad
        End If
        NextChar:
        Next
        ‘ All characters compare; passwords verified
        VerifyPassword = True
        GoTo Finish
        Bad:
        MsgBox “Passwords ” & Password1 & ” and ” & Password2 & ” do not match.”
        VerifyPassword = False
        Finish:
        End Function

  91. Hi Gem, I’m learning Access and I would really appreciate it if you please can help me in this.

    I set a table of all “tblEmployees” and “tluAccessLevel” too. I then created 3 queries: Admins, Expats and Staff from “tblEmployees”with a criteria under “AccessLevelID” to equal: 1, 2, or 3. I then created 3 new forms from these queries and gave them the names frmAdmins, frmExpats and frmStaff.

    I need to create a form whereas a user logs in to, choosing his . her username from cboUser, type his/her password and if it matches (they can get the Reset checkbox) then … AfterUpdate, they get either of the forms opened depending on their AccessLevel.

    I don’t know how to write the code correctly, something goes wrong everytime I try and I’m sure that this is because I don’t have your experience.

    Could you please advise

    Many thanks in advance,

      • Gemz,

        I used the If … then .. Else … End If function and it worked out, drove me mental but eventually done it.

        I need to create Purchase Order form where details of products I need to buy listed in the form. Created PO table with a relationship to a Product table – it’s driving me mental, can’t seem to figure out how to get the products list on the PO form – should I use a sub-form for this purpose? If yes, could you please advise or refer me to a post where you perhaps posted on this?

        Many thanks indeed,
        Mark

  92. Hi Scott,

    I am having trouble getting the form “frmPasswordChange” to update the user password and set the PWReset to false. I have the form bounded to tblUser but it still won’t update.

    I have used the following code in frmLogin

    If Me.cboUser.Column(3) = True Then
    DoCmd.OpenForm “frmPasswordChange”, , , “[UserID] = ” & Me.cboUser
    End If

    and the following code in frmPasswordChange

    If Me.txtPassword = Me.txtConfPW Then
    MsgBox “Password updated!”, vbOKOnly
    Me.PWReset = False
    DoCmd.Close acForm, “frmPasswordChange”
    Else

    The check seems to work fine and the frmPasswordChange form opens and prompts me to update the password. Everything seems to execute with no errors, but after entering the password and the confirm password I do not see the password and PWReset fields updated in tblUser.

    I have also made sure to set the frmPasswordChange form Record Source to “tblUser”. I have also set the txtConfPW control source to Password.

    Any ideas on what might be causing this problem?

      • That is not how it is setup in your loginexample file. Rather is the way I have it, which is that txtPassword is unbound and txtConfPW is setup with a control source of “Password”. I actually got it to work but setting the Tab Index on txtConfPW from 1 to 2. I’m not sure why this makes a difference. Can you explain?

      • Hmm, haven’t look at the sample in a while. But if I did it that way, then the problem is the data wasn’t being committed before closing the form. Where you checking the table before or after you closed the form? If before, then it makes sense that it might not have been updated. But if it after closing the form, then the data should be there.

        I’ll double check the sample.

        Scott

  93. I not to mention my pals were actually viewing the great tips from your web blog and then all of the sudden I had an awful suspicion I had not thanked the site owner for those techniques. These guys became as a result joyful to read through them and have in effect unquestionably been taking pleasure in these things. Thank you for being very helpful and also for figuring out this kind of fantastic useful guides millions of individuals are really needing to discover. Our honest apologies for not expressing gratitude to you sooner.

  94. Scott, I’ve got the authentication working and I’d like to say thank you for providing the script. I also saw above how to display the current logged in user using a text box. I’ve got that working as well.

    However, sometimes I need to make code changes and someone is still logged into the database. Is there any way to make a report of who is actively logged into the database? That would be super helpful.

    • Tim, First, this should never happen. Your database NEEDS to be split between the backend (tables) and a front end (everything else). The backend is stored on a network share and each user should have their own front end on their local drive. You make code changes in your development copy and when the changes have been tested, you deploy them to the users. I use AutoFEUpdater to automatically deploy updates. But there are many examples of utilities that will perform an update of your front ends.

      Second, I use this tool: http://www.utteraccess.com/forum/Who-s-Logged-Who-s-Con-t1897146.html to check who is currently logged in to the back ends. Though AutoFEUpdater does that for me as well.

      Third, you could have a log table that logs enters and exists. what I use is a table like this:
      tblAccessLog: AccessLogID (PK autonumber), EmpID (FK), LogType (In or Out), Logtime

      After the user has been authenticated I use this code:
      CurrentDB.Execute “INSERT INTO tblAccessLog (EmpID, LogType, Logtime) VALUES(” & Me.cboUser & “, ‘In’, #” & Now() & “#);”, dbFailOnerror

      this will add a record that the user logged in and when, In the On close event of the login form use:
      CurrentDB.Execute “INSERT INTO tblAccessLog (EmpID, LogType, Logtime) VALUES(” & Me.cboUser & “, ‘Out’, #” & Now() & “#);”, dbFailOnerror

      So to find those logged in you can look for Ins with No Outs.

      Scott

  95. Scott, I’m getting back into Access programming after a long time away. There seem to be few books on the subject of building secure Access apps in a post 2007 environment (without ULS). And, there are few things written about Access these days not focused on SharePoint. Do you know of any good recent books/code samples covering the various approaches to building decent desktop apps that keep newbies from breaking things and from seeing into your privates? I’m hoping for good coverage on securing objects, menus, the navigation pane, etc. Learning to build secure apps with SharePoint in mind would be great too, but it seems that functional user roles/security is never the focus, with or without SharePoint. Thanks. -Tony.

  96. Fantastic Scott. Perfect for what I am trying to put together. I am creating a simple lead tracker.
    Basically someone who lookes after our website has leads that come in. These leads are typically emailed out to sales managers who inturn assign that lead to a sales person.
    I need it when a user opens a form it shows only the leads assigned to them.
    Case 3 in your example is perfect. However I get sysntax error when opening the form. All my tables etc are identical in name to yours as i downloaded the code. I copied the code listed in case 3 and applied it to the On Open Event. Am I missing something?
    The way I see it, its looking at the open (hidden form) of the user logged in and filtering for any records that finds that UserID? In this case the sales manager.
    I could do this by query and that works fine but would prefer that sales managers can only view their leads not other peoples.

    • Yes, it would be possible. The KB article you linked to uses a function to hash the password. So what you do is pass the entered password to these functions to hash and unhash the password. So nothing changes in my process, you just add the function to hash/unhash.

      I didn’t bother to do anything like that because the type of security we are dealing with is not going to stop a determined hacker. So what I wrote about is sufficient to protect from the casual user.

      Scott

  97. Agreed, which is why i’m using your excellent structure, my only concern is that there are multiple Admins, and dont want them to see each other’s raw passwords is all, as they may be used in our Active Directory system.

    How would i go about modifying the code to utilize that hash?

  98. I modified the logon form code as follows, and it works to hash the password and compare it.

    If IsNull(Me.cboUser) Then
    MsgBox “You need to select a user!”, vbCritical
    Me.cboUser.SetFocus
    Else
    Hold = Me.txtPassword
    ‘Check for correct password
    If KeyCode(CStr(Hold)) = Me.cboUser.Column(2) Then
    ‘Check if password needs to be reset
    If Me.cboUser.Column(3) = True Then
    DoCmd.OpenForm “frmPasswordChange”, , , “[UserID] = ” & Me.cboUser
    End If
    DoCmd.close acForm, “Main Menu”, acSaveNo
    DoCmd.OpenForm “Main Menu”
    Me.Visible = False
    Else
    MsgBox “Password does not match, please re-enter!”, vbOKOnly
    Me.txtPassword = Null
    Me.txtPassword.SetFocus
    End If
    End If
    End Sub

    This is if i enter the hash directly into the table first though. However if someone was to change their password, i have to manually hash it with the VBA Immediate Window and store that returned value. I’m having trouble modifying the password change form to store the hash from the function…

  99. Hi Scott,

    I am having an issue with the password reset form.

    After I enter the password and hit enter I get a pop up window with the users id and a balnk field below it so it looks like the matching from the 2 fields is not working.

    I did have to modify the matching on the on click of the manin menu buttons to the following for the match to work
    “[XeroxCanada_EmpNo] = ‘” & Forms!Logon_Form!cboUser.Column(0) & “‘”

    and the matching for the password reset step iof the login form is currently DoCmd.OpenForm “frmPasswordChange”, , , “[XeroxCanada_EmpNo] = ” & Me.cboUser

    Can’t figure out how to get the match to work.

    Can you help?
    I can email you directly if you like with screen shots etc

  100. this is what I have on the after update of the Logon_form. Right now it pops the password reset form and the main form at the same time putting the mainform at the front. Would be nice if it popped the password reset form and then on update closed it and opened the main form

    Option Compare Database

    Private Sub txtPassword_AfterUpdate()

    ‘Check that EE is selected
    If IsNull(Me.cboUser) Then
    MsgBox “You need to select a user!”, vbCritical
    Me.cboUser.SetFocus
    Else
    ‘Check for correct password
    If Me.txtPassword = Me.cboUser.Column(2) Then
    ‘Check if password needs to be reset
    If Me.cboUser.Column(3) = True Then
    DoCmd.OpenForm “frmPasswordChange”, , , “[XeroxCanada_EmpNo] = ‘” & Me.cboUser & “‘”
    Me.Visible = True
    End If
    DoCmd.OpenForm “frmMainMenu”
    Else
    MsgBox “Password does not match, please re-enter!”, vboOkOnly
    Me.txtPassword = Null
    Me.txtPassword.SetFocus
    End If
    End If
    End Sub

  101. Thanks sir I search this for long time. I use your code its work perfectly. But I used txtbox rather than cmbbox so please help me sir how can I used dlookup to set different permission .I have 5 typeof user as admin ,Class teacher, teacher, student and guest user.i want to set full access to admin , aloow addition and Edit to class teacher, and read only to rest of .

    • I would strongly suggest switching to a combobox. First it ensures that only listed users can get into the database. Second, it provides more easily accessible info about the logged in user.

      But if you need to retrieve other info about the user you can use DLookups, which a slower performers.

      DLookup(“[fieldname]”,”tblUsers”,”UserName = ‘” & Me.txtUser & “‘”)

      Where fieldname is the name of the field whose value you want to retrieve, tblUsers is the name of your user table, Username is the field for the userID they are entering into the text box and txtuser, the name of the textbox.

      Also I would use a table for your user types so you can use the numbering to set your access. For example, if Admin = 1, Class Teacher = 2, Teacher = 3 and so on, you can use:
      If UserType>2 then
      Access is read Only
      ElseIf UserType = 2 then
      Access is allow additions and edits
      Else
      Access is full
      End If

  102. The sample includes code on changing a password. Having a forgotten password function is more complex. It means you need secondary means of confirming the user’s identity. The code to change the password is simple and its in the sample. But to verify the secondary identification requires retrieving the value and testing its accuracy.

    Who is the target audience for your app? Why do you think you need a feature like that.

  103. Target audiences are students in my class. So if they are forget their password then by this button allow to reset passaword.IAdmin already register user by registered form with security questions to reset password. So I think to create forgotten password form in which user add their username after confirm valid user want prompt questions and giving correct answer new password txtbox will visible and password reset without current password. Whatton is right code for it.

  104. I’m assuming this isn’t a class on Access. Is this database going to be accessed over a school LAN? Will they be allowed to take this database home? My point is I’m really not sure this extraordinary level of security is worth the effort for an Access database,

    But, you can look at the code in the sample to get an idea how this should work. I would have another form similar to the Password Change form where the user is prompted to enter the secondary identification. You then check the entered value against the stored info. If it matches, you unlock the controls to store a new password.

  105. Just a quick one: We currently have all databases in Access 2003 using ULC. Using your approach for creating a new database in 2010, we have implemented the logon screen but can you tell me where i can query the current logged in user. we would like to implement certain default data based on logon.

    • If you use my method exactly, the logon form remains open but hidden throughout the session. The Currently logged in userID can then be gotten using the syntax:

      Forms!frmLogin!cboUser

      This will be fine for what you described as it would appear you only want to affect the local user.

      Note: however, that this assumes the proper deployment for a multi-user application. Which is a shared backend on network share and a local front end on each user’s PC. If you are sharing a front end or don’t have a split app, then you need to change this ASAP.

      Scott

  106. kindly assist me with how to create a sort for a particular feild. assuming i have created a a drop down combo with a list of two networks. how do i get a filter for each network just incase i select one.
    i will love it if you reply through my mail

  107. Hi Scott,

    Thank you for this very useful login form setup. I have a simple question; if I restrict the access for viewers to my main form, which includes a subform of all open issues, it also disables the user from using the ‘quick filters’ (pre-made filter strings) I made with the use of a combo box, even though the user is able to apply his or her own filters using the Filter option under the Sort & Filter tab. Is there any way to circumvent this, so that the quick filters can still be used by viewers, and still disallowing edits to be made?

    • Thanks for the nice comment.

      Can you detail how you are restricting access and what you specifically mean by “quick filters”? It sounds like might be setting AllowEdits to No, which will lock out any Controls on your form. If that’s what you are doing another option would be to use something like http://allenbrowne.com/ser-56.html. I use this function where I want to enable controls like for filtering etc, but lock the controls for data entry.

      Scott

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s