Custom User Permissions, Requesting tips for improvements.

[NeutronFlux]

I've built an Access 2007 application that lists our company's suppliers and creates purchase orders. In this application, I want to allow different users access to different things. Some of the things that I want to control access to include:

• adding suppliers
• deleting suppliers,
• editing suppliers,
• adding comments about suppliers
• seeing a list of suppliers with expired certificates,
• reviewing purchase order log,
• managing users,
• receiving alerts to add/modify suppliers through email
• receiving purchase orders through email,
• etc.

I originally thought of classifying these into groups like 'Admin', 'Manager', etc., but it got messy since some users might need A,B,C, one might need B,C,E,F, another might need A,C,F and it just ends up with too many groups with too few people in each. So instead, I have each of these permissions set as a Boolean value in the Login table.


Managing this table seems pretty simple so far. But the forms and VBA are a mess. Most of my code looks like this:

If Not AllowAdd Then
     cmdAdd.visible = False
     Me.AllowAdditions = False

End If
If Not AllowDelete Then
     cmdDelete.visible = False
     Me.AllowDeletions = False

End If
If Not AllowEdit Then
     For ctl in controls
          ctl.locked = True
     Loop
End If

...

or

If AllowEdit Then
        Me.cboMenu.RowSource = Me.Me.cboMenu.RowSource & _
        "Modify records;"
    End If
    If AllowApprovePurchase Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "Review Purchase Orders;"
    End If
    If AllowUserAdmin Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "User Administration;"
    End If
    If ShowExpired Then
        Me.cboMenu.RowSource = Me.cboMenu.RowSource & _
        "List of suppliers with expired certificates;"
    End If
...

This is generally on the Form_Load() procedure of each form that restricts user access, although sometimes it's in the middle of the code, such as when retrieving email addresses for users that are supposed to receive requests/status updates. It works, but it gets incredibly messy to maintain such as when adding or modifying a permission. I thought of managing this in its own module, but I'm having trouble figuring out how it could work.

I'm wondering if anyone has any suggestions for better organizing these permissions so that adding/modifying them isn't as much of a tedious and error-prone process as it currently is.

Thanks