Password protecting a surname combo box on a form in access with VBA

[dholdaway]

I wonder if anyone is able to assist with this. I am trying to password protect a combo box so only certain people can select data. I want to be able to click in the combo box have a subform appear for a person enter a user name and password. The form will then close on the click of a button and they will then be able to select there name from the combo box list and update the record. I have the user name and password details stored in a table called [STAFFDETAILS]

Does anyone have some example code that may do this or be able to give some advice.

Many thanks in advance.

[zmbd]

dholdaway: Keep in mind that anything you do in Access can be bypassed by a fairly knowledgeable person.

There are a few ways to accomplish your goal; however, you do not mention which version of Office/Access you are using.

Generally, I use the before_update event of the control. Simplest would be to prompt for the password static password and then the code to check for a valid entry - using the me.undo and setting the update Cancel equal to true if the entry isn't valid.

You can get more complex from there... having tables that hold the user name and plain text (not advised) password to tables that have the user name and hashed (explanation of SHA2 hash)(better) password to tables that have the user name, possibly hashed, and password hashed with a random salt (best?) - along with forms and code that handle the user rights.

As for code and examples, that's not really what we do here; however, you might find TwinnyFo's treatment of user permissions to be interesting. I personally use a slightly different version of this; however, in the end, it's all the same.

[jforbes]

First I want to reinforce Zmbd's statement of:

Keep in mind that anything you do in Access can be bypassed by a fairly knowledgeable person.

This couldn't be truer.

I also want to point out that there is an alternative to Passwords and a Password Dialog and that is looking to the OS to get the Logged in Windows User and then Enabling and Disabling Buttons and other Controls based on the Users permissions. The advantages are:

• There is no login process. Which is a minor thing, but helps a lot with the flow of the application as the user just launches it.
• There is no Password maintenance. There is still maintenance because someone as an administrator will need to grant the rights to the different objects. But Passwords are not saved in the database, so people can't inadvertently find out someone else's Windows login through the use of password sharing. (Or a frisky user hacking the database)

To accomplish this, you will need a table to store whatever user based scheme of security you come up with, a function to get the User Name from Windows, place code at the form level enable/disable objects based on the currently logged in User's Permissions.

To get the User Name from Windows, place this function in a VBA Module. This piece of code is all over the Internet in quite a few different variations. This version is the one I've been using for the last couple years, but here is another version from MMcCarthy :

Declare Function GetUserName Lib "advapi32.dll" Alias "GetUserNameA" (ByVal lpBuffer As String, nSize As Long) As Long

Public Function GetWindowsUser() As String
    On Error GoTo ErrorOut

    Dim lngResponse As Long
    Dim strUserName As String * 32

    lngResponse = GetUserName(strUserName, 32)
    GetWindowsUser = Left(strUserName, InStr(strUserName, Chr$(0)) - 1)

ExitOut:
    Exit Function
ErrorOut:
    MsgBox Err.Description
    Resume ExitOut
        
End Function

You can then marry up the Logged in User to their permissions however you want. Typically, I use some global Boolean variables for some basic permissions, like Manager, Administrator, Human Resources and set them when the application first starts up. Then code like the following can be placed in the Form_Current or Form_Load Event:

   Me.cmdLaunchAdministratorForm.Enabled = gAdministrator
   Me.cboRecordOwner.Enabled = gManager
   Me.cmdViewSalaries.Visible = gHR

One nice thing about coding it this way is that if someone forces a break in the code execution and somehow resets the Global variables, all the Globals get reset to False and it pretty much locks down the entire application.

This is just another option for you.