User Access Rights to Forms/Actions (1 Viewer)

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 12:13
Joined
Feb 5, 2019
Messages
292
Hello forumers,

Does anyone have a link to some details on how to create user access rights? I have a table, tblEmployee, and I was thinking of adding a multi-select field to the different areas of the company to allow me to tick what they can have access to. At the moment I just look at the logged in user with GetUserName() and then write a VBA code on each form to say if that person is allowed access. My issue with that is, if I want to allow a new user to access this, I have to add them into the code.

If I could just select their access in their profile, this would be much easier for turning these things on and off.

Ideally I would like to be able to select the form for access and then have options for No Access, Read Only, Full Access.

I am sure this exists already, but when I search online, I only get results for the old User Level Access Rights.

~Matt
 

Gasman

Enthusiastic Amateur
Local time
Today, 12:13
Joined
Sep 21, 2011
Messages
14,051
Well I spotted one by @arnelgp in that link.?

I did similar in the last place I worked, though I did not present the forms in the Switchboard, so my user level worked mainly on switchboard items?
The user level was also quite simple, Me, Directors, Managers and the rest :)
 

Saphirah

Active member
Local time
Today, 13:13
Joined
Apr 5, 2020
Messages
163
The Password Login from Isladogs is really good! So i would first go for that.
It is quite hard to prevent users from accessing certain forms and tables in access. If someone with malicous intent and knowledge tries to access data that is not supposed to be for him, then he most certainly can. That is just the limitation of access. (at least to my knowledge)

There are a few things you can do though.
First, lock all tables with a password.
Then go to the settings and hide the content browser, which shows all queries, forms etc...
You probably have a navigation form anyway (something like a main menu), so you won't need it.

Now here is a trick i use exactly for that purpose:
I am using a custom function for opening a form. This allows you to execute certain code every time you open a new form.
Code:
Public Sub OpenForm(formName As String, Optional View As AcFormView = acNormal, Optional FilterName As Variant = "", Optional WhereCondition As Variant = "", Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, Optional WindowMode As AcWindowMode = acWindowNormal, Optional OpenArgs As Variant = "")
    DoCmd.OpenForm formName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
End Sub

If you did use DoCmd.OpenForm in the past, you can just replace all instances with the new function using replace all.

Next what you want to do is to create different "permission levels". For example level 1 might be your read only user, level 2 is your active user, level 3 are admins...
You can now go into your controls and add these permission levels as tags (or leave it empty if everyone should be able to see it).
So for example if you have a button that opens a customer form, but only admins are supposed to see it you add a 3 as the tag.

Then using the following code into our custom "OpenForm" function we can hide buttons if the user does not have a level required.
Code:
Public Sub OpenForm(formName As String, Optional View As AcFormView = acNormal, Optional FilterName As Variant = "", Optional WhereCondition As Variant = "", Optional DataMode As AcFormOpenDataMode = acFormPropertySettings, Optional WindowMode As AcWindowMode = acWindowNormal, Optional OpenArgs As Variant = "")
    DoCmd.OpenForm formName, View, FilterName, WhereCondition, DataMode, WindowMode, OpenArgs
   
    Dim ctrl As control
    For Each ctrl In Forms(formName).Controls
        If Not ctrl.Tag = "" Then
            ctrl.Visible = GetUserPermission() >= CInt(ctrl.Tag)
        End If
    Next
End Sub

This will prevent any non-malicious user from seeing and clicking that button.
And this worked for me and my company for quite a long time now.
 
Last edited:

isladogs

MVP / VIP
Local time
Today, 12:13
Joined
Jan 14, 2017
Messages
18,186
Another neat little trick that you can use to prevent users opening forms (or reports) from the nav pane if this is made visible by the user....
Add this code to the Open events of each form/report:

Rich (BB code):
 'block opening item from nav pane
    If Application.CurrentObjectName = Me.Name Then Cancel = True

Doing this will help ensure users cannot bypass any permissions code
 

MattBaldry

Self Taught, Learn from the Forums
Local time
Today, 12:13
Joined
Feb 5, 2019
Messages
292
Hi All,

I have sorted this now. Thread can be closed.

I have created a table, tblEmployeeRole. I have added a multi-select field in tblEmployee that links to the ID in tblEmployeeRole.

When the button to open the form is pressed, I now lookup the network username to see if they have access to that role and if not, it tells them they do not have access rights. I will post the code here once I have made it pretty.

~Matt
 

theDBguy

I’m here to help
Staff member
Local time
Today, 05:13
Joined
Oct 29, 2018
Messages
21,358
First, lock all tables with a password.
Sorry if I'm too slow this morning (I just woke up and haven't had coffee yet), but exactly how do you lock a table with a password? Just curious...
 

Users who are viewing this thread

Top Bottom