Today’s post is based on a question that I received.
We are going to assume that there are multiple users who have rights
to view different database objects.
So in this example there are 3 user logins with different rights to access 3 forms and 3 reports.
Here is an overview of the database setup:
This is tblLogins:
This is tblAdmin:
Notice that all you really need to do is check whether the user has access to the object.
If you want, you can assign the permissions based on groups, instead of user by user. Then you can assign the users to groups, and the groups can have the permissions to the objects.
On the “Open” event for each form, place the following code:
Private Sub Form_Open(Cancel As Integer) If Not Allowed(Me.Name) Then MsgBox "You are not allowed here" Cancel = True End If End Sub
On the “Open” event for each report, place the following code:
Private Sub Report_Open(Cancel As Integer) If Not Allowed(Me.Name) Then MsgBox "You are not allowed here" Cancel = True End If End Sub
On the “Open” event of each database object we are using the “Allowed” function to let us know if the user is allowed to open the database object.
The “Allowed” function is used by a number of forms and reports, so we put the “Allowed” function in a module called “basSecurity” by itself.
Here is the “Allowed” function:
Public Function Allowed(ObjectName) As Boolean Dim rst As Recordset Dim strSQL As String strSQL = "SELECT Allowed FROM tblAdmin WHERE ObjectName='" & ObjectName & "' AND UserName ='" & m_LoggedInUser & "'" Set rst = CurrentDb.OpenRecordset(strSQL) If rst(0) = True Then Allowed = True Else Allowed = False End If rst.Close Set rst = Nothing End Function
This function will check the tblAdmin table to see if the user is allowed to open the requested object.
basSecurity also has a modular scope variable called “m_LoggedInUser”, to store the user name of the currently logged in user.
Here is the screenshot all together:
To test and open all the forms and reports I am using a form called “frmMain”:
…and here is “behind the scenes”: