Database Security Question #2

As always, thank you for the past help you all have given.

My new question is...is it possible to have a database open to a different form or switchboard depending on who is accessing it? (I have users in groups through the security wizard)

For instance, if a general user opens the database, they see an input form. If an Admin user opens the database, they see the main swithcboard. Or, can I disable buttons on my swithcboard based on who is signed in (user or admin)

Thanks for you help.

Gale "Still a newbie but getting better" Fulton

Hi Gale,

There are several ways to go about restricting objects within the database. You can use the User-Level Security Wizard as one method and just follow the wizard prompts to grant permissions to the specific database objects, or you can write some VBA code that will open a specific form with an autoexec macro based on the CurrentUser. Example

If CurrentUser = "Admin" Then
DoCmd.OpenForm "Switchboard"
Else
DoCmd.OpenForm "Name of Form"
End If

Then create an autoexec macro to run this code on startup.

Another method would be to place all the restricted objects from your database into a new switchboard page that you don't want the users to have access to and for the button used to switch to that switchboard page, run a similar type of code, but prompt with a message box that the user doesn't have access to that switchboard page. Or you could have that particular page open with a Password form first and prompt for a password to access that particular switchboard page. This still doesn't stop the user from opening the database window to gain access to other forms and reports unless you have your database window secured so that holding the Shift key is disabled during startup.

You can also create an mde front end with only the forms/reports/queries that you want the users to have access to. This is a little more secure because then the user will only have access to the forms/reports/queries you want them to have. If you are only working with two groups, the user and the admin groups, then this might be the easiest way to go about it.

Mostly it depends on how you want your application to flow based on the current user that is logged in.

This should give you some ideas to work with.

lwells

This is an easy fix for what I wanted to do...I simply coded the switchboard buttons I wish to keep general users out of with a password.



Private Sub Option2_Click()

Dim strPasswd
strPasswd = InputBox("This is a Restricted Area...Please Enter Password", "Restricted Area")

If strPasswd = "" Or strPasswd = Empty Then
MsgBox "Password Required", vbInformation, "Restricted Area"
Exit Sub
End If

If strPasswd = "password" Then
DoCmd.OpenForm "FormName", acNormal
Else
MsgBox "Access Denied", vbOKOnly, "Restricted Area"
Exit Sub
End If
End Sub


Of course I had to disable the "Shift" button function or this is a useless code.

Gale Fulton

LWells;


Here is the code you were telling me about:
If CurrentUser = "Admin" Then
DoCmd.OpenForm "Switchboard"
Else
DoCmd.OpenForm "Name of Form"
End If


Can I modify it to read something like this...

If CurrentUser = "Admin" or "Gale Fulton" or "John Doe" Then
DoCmd.OpenForm "Switchboard"
Else
DoCmd.OpenForm "Name of Form"
End If

I realize that this will not work the way I have written it, but is there a way to include multiple people in the same line?

Or better yet...can the code be modified to recognize the current user's group level security and open a form based on that?

Thanks for your help. Again...and again...and again.

Hi Gale,

Don't suppose saying Yes to all of the above would be very helpful would it....

While I have never tried opening the form "Switchboard" based on a multiple criteria of CurrentUser as shown above, but there is no reason why it wouldn't work. You can also write a procedure to open specific forms based on Current User and then create an autoexec macro to open the specific form based on the CurrentUser that logged in.

The following example is off-the-cuff (Air Code!!) but will give you an idea.

Select Case CurrentUser
Case "Admin"
DoCmd.OpenForm "Switchboard"
Case "Gale Fulton"
DoCmd.OpenForm "SwitchBoard"
Case "John Doe"
DoCmd.OpenForm "Order Entry"
End Select

Then create an autoexec Macro to run the procedure.

You can also set permissions either by group or individual to allow access to certain forms, tables, queries, macros etc. using the User and Groups Permissions from the Toolbar.

So it really depends on which way is the easiest for you to implement what you want.

lwells

I wish it would be as easy as simply using permissions to allow - not allow access, but our CEO wants certain users to only see certain things on their switchboard. Example: Sales would only see sales related buttons and Tech would only see tech related buttons but bothe groups would see HR buttons.

Since we are a small company, the CASE - Open will work fine, but I am going to plug away at how to open based on current user's permission. Because as we add employees, this CASE - Open will be a logistical nightmare.

Thank again.

Gale

Hi Gale,

Ahh...got it...let me play around with some code that will allow you to enter a new employee and set permissions up front to make it easier. Also, I still think using the switchboard where you have some switchboard pages with the controls you want to use all on one....example..Switchboard page 1 - full access Switchboard page 2 - limited access.

Give me the items you want full access to, and the items you want limited access to and I think I can put together what you are looking for.

lwells

I actually have the switchboard pages all made (I was calling them forms I guess) And I have everything working and opening up to the proper switchboard for each individual. Now, when a new employee comes in, I will have to add them to the prpoer user group and then add them to the CASE code. It really isn't that big of a problem, I just thought there may be an easier way.

If there is a front-end way to cover both adding to the user group and the CASE code in one fell swoop...I am all for that!

Gale

Hi Gale,

I know this may sound silly, but what about putting a field in your employees table that would establish which security level for that employee, and then using the DLookup when they sign on to determine what security level they have privilages to. Then use a select statement based on security levels rather than by CurrentUser and you won't have to change your Case statement every time an employee is added.

Just an idea,
lwells