Access Control Using User Rights & Groups

Question

I have a database that is built from the ground up using MS Access 2007 with VBA running all the scripting (SQL, data validation etc) between the forms, reports and the data tables. I am trying to put a user access implementation system on the database's landing page. The users can be 1 of 3 groups, an admin(enters system settings, user management), an officer(posts data) or a manager(views data). Users are linked to a user group table which has the following:

ID    UserGroupDescription HideOps  HideAdmin HideManager  
1     Administrator          TRUE    FALSE       TRUE  
2     Operations            FALSE    TRUE        TRUE  
3     Manager                TRUE    TRUE        FALSE

I would like to show each group their shortcut only(using a label's .visible property) on the landing page but I am stuck on how to manage the looking up of this data without hardcoding the 3 groups only in a case statement (meaning I have code any additional groups being added).
Is there a way to make the checking of which label to show dynamic? while using the sql user user group approach.

Suggestions on how to do this better are highly appreciated. I am not currently using the MS Access native functions just vba — Avagut, Dec 25 '13 at 16:40
@HansUp I have a user table and it has a mapped user group id, at login the user id and the user group id are put into temp vars that are loaded at the landing page opening — Avagut, Dec 25 '13 at 17:14
Good. In the landing page form's Form Load event, open a `DAO.Recordset` based on a query of the user_group table. Then use the recordset's HideOps, HideAdmin, and HideManager values to adjust the Visible properties of those form controls. Does that all make sense? If not, where is your first sticking point? — HansUp, Dec 25 '13 at 17:24
Awesome, I hadn't thought of using a recordset, I was trying to change the controls directly. Now this I can work with, thank you @HansUp — Avagut, Dec 26 '13 at 02:32
This has worked excellently, first time through. If you put your comment as an answer I will accept it as an answer immediately. I have appreciated the help. Many thanks — Avagut, Dec 26 '13 at 09:21

score 1 · Accepted Answer · answered Dec 26 '13 at 15:08

1

In the landing page form's Form Load event, open a DAO.Recordset based on a query of the user_group table. Then use the recordset's HideOps, HideAdmin, and HideManager values to adjust the Visible properties of those form controls.

answered Dec 26 '13 at 15:08

HansUp

95,961
11
77
135

score 1 · Answer 2 · answered Dec 28 '13 at 17:51

I went with hiding all unauthorised parts using a big label for each group(lblHideAdmin & lblHideOps) :-). I managed to pass user's group after the login using a TempVar variable and this is loaded at the starting page load event.
The variable hide/show option is then pulled by use of a dlookup as below.

UserGroupId = TempVars![UserGroupId]
Me.lblHideAdmin.Visible = True
Me.lblHideOps.Visible = True

OpStatus = True
OpStatus = DLookup("[hideops]", "tblUserGroupDetails", "Id = " & UserGroupId)
Me.lblHideOps.Visible = OpStatus

AdminStatus = True
AdminStatus = DLookup("[HideAdmin]", "tblUserGroupDetails", "Id = " & UserGroupId)
Me.lblHideAdmin.Visible = AdminStatus

Access Control Using User Rights & Groups

2 Answers2

Linked