User Access Rights to Forms/Actions

[MattBaldry]

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]

Should always search here?

https://www.access-programmers.co.uk/forums/search/536537/?q=user+access+rights&o=relevance

 

[MattBaldry]

Should always search here?

https://www.access-programmers.co.uk/forums/search/536537/?q=user+access+rights&o=relevance

Thanks Gasman,

I shall have a look and see if I can work it out. When I looked yesterday, my search terms were slightly different and I didn't see any of these.

~Matt

 

[Gasman]

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

 

[isladogs]

... thinking of adding a multiselect field....

If you are referring to lookup fields at table level or a multivalue field, then I would strongly advise against it.
Have a look at my example app Password Login - Mendip Data Systems which includes at least part of what you may need.

 

[Saphirah]

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.

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.

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.

 

[isladogs]

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:

 '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]

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]

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...