Hey All,

New design project trying to get grips with and wanted to pick people's brains as to best way to do it.

Trying to find a way to have a navigation menu which alters according to the role of the user logged in rather than the individual login.

The reason for the choice is that someone may alter job however their new role will mean they should not have access to information any more that their old role did even though they may actually be more senior in the company, or their role may mean they need to access the system design (inc tables) just not the data itself, or they may need to be able to see the data is there and that is shown on a form just with the password input mask over the top.

The only design schema to code that I can find use the concept of if level is above or below x then they have access which wouldn't work here.

I've thought about a global recordset of the role permissions as yes and no with one huge form to control the role permissions.

That middle paragraph is one sentence!

Have the application open a hidden form on start up, and run these events from the On Load event of the hidden form.

Create a table, perhaps in another database, that has the user name and role. When the application opens, have it take the username from the person's computer:

sUser = (Environ$("Username"))

Then have it open the crendentials database (I use a SharePoint site for this purpose so I can manage user levels there) and find the user.

If the user is not in the database, then Application.Quit.
If the user is in the database, take their username, maybe first and last names (I like to display them so the user knows the app knows who they are) and the group name.

Take these credentials and put them in unbound text boxes on the hidden form. Then open your main form.

I generally use a main navigation page, and if the user is allowed to see the admin forms, the button to those forms is visible. If not, then the button is invisible. The forms check the hidden form like this:

If [Forms]![frmHiddenCreds].[GroupName] = "Admin" Then
   Me.btnAdminForm.Visible = True
Else
   Me.btnAdminForm.Visible = False
End If

Also put an event on the forms themselves on their on load event, to close the form if the user is not in the admin group.

I set all the forms and tables to Hidden, as well. The only way around that is to move data in and out as the user opens forms and reports they are allowed to see. There are lots of kludges. Then turn your database into an MDE so the users can't modify your code and get access, etc.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com

It helps to a degree, however don't have sharepoint to use that option.

Think having a blonde moment in regards to the groups bit as not getting that.

I was thinking you would have groups of users. Like this:

tblUser
UserID
Username
FirstName
LastName
etc
Group - like Admin, User, SuperUser, etc.

This way you can do your code like this:

Dim sGroup as String

sGroup = [Forms]![frmHidden].[txtGroup]

If sGroup = "Admin" Then
   ...Visible = True
Else
   ...Visible = False
End If

instead of:

Dim sUser As String

sUser = [Forms]![frmHidden].[txtUser]

If sUser = "jdoe" Or sUser = "jsmith" Or sUser = "moe" Then
   ...Visible = True
Else
   ...Visible = False
End If

If you manage users in groups, then you don't have to change your code every time you want to grant or deny rights. Just move the user between groups.

You can use a separate database to keep all your user rights in, and then look up each user as they log in.

Access has its own security features, but I don't use them because of my network environment.

Did that help?


mmcdonal

Look it up at: http://wrox.books24x7.com

yes its what i was thinking of it was just the sheer number of groups that was running into.

Currently at over 60000 combinations to code in that way. Access security isn't going to work for this, looked at it too long and its not flexible enough.