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.

Answer 1

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

Answer 2

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.