The difficult I do immediately, the impossible takes a little bit longer.
You already have your Main Menu (Switchboard) but now you don't want your Users to get access to all the Forms and/or you may only want to assign certain *permissions* based on their level. Here's a technique that will do just that. You can find another way here. (Please read all the way thru before implementing!)
Assign *Permissions* based on Users Level...
Simulate a Drop Down box (not a Combo Box) as seen on Web Pages (Access 2007 and higher)
Make Controls resize with a form (Access 2007 and higher)
Switch Windows (Access 2007 and higher)
Private Sub Form_Load()
Private Sub cmdTemporaryAccess_Click()
Private Sub cmdDelete_Click()
In your Table where you keep your Users information add whichever fields are missing from your table from the one below. *txtDepartmentID* is *optional* as we will not be using it in this example. (You can name them whatever you like, however, make sure you follow that field name change throughout the rest of the code on this page.)
To capture this information I use a Log On form once. The User enters their First Name, Last Name and eMail Address and I *capture* the balance. Further down I show how. Doing it this way I don't have to pester the User with logging on every time they want to open the database. Initially, their permission is set to *Read Only*. To change it to *Read/Write* you will need an ADMIN.
If you don't already have one, set up tblSecurity, as shown below. Again, if you already have one under a different name you will need to change he code accordingly. You only need to enter the three entries shown below. The ones with nothing under *sSecurity* are not used in this example.
On your Main Menu (or Switchboard) you will need 6 hidden unbound fields named...
And now let's add the code...
Copy and paste the code between *Private Sub Form_Load()* and *End Sub* to the On_Load event of your Main Menu/Switchboard. ( Reminder , check the field names to make sure they are reflected below if you are not using the ones assigned above.)
The section enclosed in brackets is optional, it can be deleted with no reprecussions. However, you will need some way to capture this information. To apply this section to your Main Menu/Switchboard click *frmNewUser* to see the Form and the code.
Examples of Use...
A *Delete* button...
Form *On_Current* events...
Function IsDeveloper() As Boolean
Dim UserName As String
IsDeveloper = False
UserName = VBA.Environ("Username")
Select Case UserName
Case "Your NetworkID goes here"
IsDeveloper = True
IsDeveloper = False
*Optional* Now we are going to add the *Temporary Access* button. This button can be used for several purposes, such as, giving a User temporary ADMIN abilities or with some additonal code the ability to delete when that is only reserved for ADMIN's.
Add a button to your Main Menu/Switchboard and name it *cmdTemporaryAccess*. You can put whatever image or caption you want on the button, I use a key icon.
Copy and paste the code between *Private Sub cmdTemporaryAccess_Click* and *End Sub* to the On_Click event of the button. ( Reminder , check the field names to make sure they are reflected below if you are not using the ones assigned above.)
Create a Form to manage the information...
Lock Users out of a Menu/Switchboard button...
On my Main Menu I used and Option Group with Toggle Buttons so the code to lock a User out is on the Option Group.
Private Sub CategoryButton_BeforeUpdate(Cancel As Integer)
Function ChangeProperty(strPropName As String, varPropType As Variant, varPropValue As Variant) As Integer
Create a Module, mine is named modUtilities, and copy/paste the IsDeveloper() and ChangeProperty() code shown below into the Module. Do not paste behind the Form these two Functions must be a seperate Module.