Here’s another common question that we see in Access forums:

How can I password protect a form/report?

As per with most thing Access related, there are numerous ways to handle this and in the post and sample database I cover 3:

• Hardcoded password
• Table based password (enable the admin to change it without needing to edit a form)
• Authorized User table (recognize the user without needing any password)

In all cases it is simply a case of using the Form’s/Report’s On Open event to perform the necessary validation(s).

Hardcoded Form/Report Password

The code for a hard coded password would look something like:

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler

    If StrComp(InputBox("Please enter the Password. (the password is: MyPassword"), "MyPassword", 0) <> 0 Then
        Cancel = True
        MsgBox "Wrong password.", vbInformation Or vbOKOnly, "Operation cancelled"
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Now the drawback of this approach is the fact that if you need/want to change the password you need to edit the deisgn of the form and then redistribute the front-end to your users.

Table Based Password

The drawback of the Hardcoded Password approach can easily be overcome by simply creating and reading the password out of a table. So let’s start by creating a very basic password table

CREATE TABLE tbl_DB_Object_Password (  
     ObjectPasswordId              COUNTER PRIMARY KEY,
     ObjectType      TEXT(10) NOT NULL,
     ObjectName      TEXT(255) NOT NULL,
     ObjectPassword      TEXT(255) NOT NULL,
     CONSTRAINT UniqueObjectEntry UNIQUE (ObjectType, ObjectName)
);

Now you need to make an entry for each object you wish to protect

ObjectType = Form or Report
ObjectName = Name of the Form or Report
ObjectPassword = Password you’d like to use to protect the Form or Report

Then once that is done, we’d use code along the lines of

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler
    Dim sFormPassword

    sFormPassword = Nz(DLookup("ObjectPassword", "tbl_DB_Object_Passwords", _
                               "[ObjectType]='Form' AND [ObjectName]='" & Me.Name & "'"), "")
    If sFormPassword <> "" Then
        '    If InputBox("Please enter the Password.") <> "MyPassword" Then 'if using Option Compare Binary
        If StrComp(InputBox("Please enter the Password. (the password is: ABc"), sFormPassword, 0) <> 0 Then
            Cancel = True
            MsgBox "Wrong password.", vbInformation Or vbOKOnly, "Operation cancelled"
        End If
    Else
        'You choose what to do here!
        '***************************
        '1. no password was set so this form isn't secured, do nothing
        '2. no password, so the admin forgot to set things up, so lock it down by Cancel=True
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Now this enables the database administrator to easily change any object’s password by simply editing the entry in the table. I’d recommend hiding the table so it is not easily accessible to nosy users though!

Authorized User(s) Table

The last approach, which is the best approach IMHO, is to do away with passwords altogether and authenticate the object by automatically recognizing the active user and cross-referencing them against an authorized user listing (table).

So once again, let’s start by creating a user table that we can use to verify if the active user should have access, or not. In it’s simplest form it would look something like:

CREATE TABLE tbl_Users (  
     UserId              COUNTER PRIMARY KEY,
     UserName      TEXT(255) NOT NULL,
     ObjectType      TEXT(10) NOT NULL,
     ObjectName      TEXT(255) NOT NULL,
     CONSTRAINT UniqueUserObject UNIQUE (UserName, ObjectType, ObjectName)
);

Now you need to make an entry for each object you wish to protect

UserName = The windows username of the authorized user
ObjectType = Form or Report
ObjectName = Name of the Form or Report

and then we code the Form’s/Report’s On Open event like

Private Sub Form_Open(Cancel As Integer)
    On Error GoTo Error_Handler

    If Nz(DLookup("UserName", "tbl_Users", _
                  "[ObjectType]='Form' AND [ObjectName]='" & Me.Name & "' AND [UserName]='" & fOSUserName() & "'"), "") = "" Then
        Cancel = True
        MsgBox "You are not authorized to open this form", vbInformation Or vbOKOnly, "Operation cancelled"
    End If

Error_Handler_Exit:
    On Error Resume Next
    Exit Sub

Error_Handler:
    MsgBox "The following error has occurred" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: Form_Open" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occurred!"
    Resume Error_Handler_Exit
End Sub

Now the user doesn’t even need to remember any password(s), but your forms are protected, so everyone is happy!

Disclaimer/Notes:

Microsoft Access 2010 Runtime
Microsoft Access 2013 Runtime
Microsoft Access 2016 Runtime
Microsoft 365 Access Runtime

Download a Demo Database

Feel free to download a 100% unlocked demo copy by using the link provided below:

Version History

V1.000 (2018-09-15) Initial Release
V1.001 (2018-09-15) Added Object Type & Object Name to tables to avoid name conflicts between Forms/Reports

Other References on the Subject

How to Create a Password Protected Form or Report