How to disable navigation tab based on username/password...adding to my code.
When I open my Access database, I have a macro that opens up a username/password screen. When I enter my username and password, it opens a form that has multiple navigation tabs. Depending on the username that I use to log in, I have access to all the tabs or one of them is grayed out. The code refers to a tblSecurityLevel and tblEmployee table. Here's the video I watched to make this: https://www.youtube.com/watch?v=n7PcyO4m6ZI. After I made that form with navigation tabs, I created code to make one of those navigation tabs visible or not if the security level was row 1 (admin). Here's the video i used for that: https://www.youtube.com/watch?v=sWsTwYuWc1o. There are 4 parts to his tutorial. I have it working as he created it. However, you will see on his example, has an admin group and a user group. If a username is assigned the security level of Admin, they can see the admin navigation tab. If the username has a security level of User, they CANNOT click on the admin tab, but they can click on all others.
I am trying to figure out a way to add code to his, where i have more security levels. He only has two security levels, "Admin" and "User. In my database, I have 3 security levels for now, "Admin", "Manager" and "User". I have 4 navigation tabs..."Home", "Admin Page", "Add New Customer" and "Search Customer". I don't want all security levels to see each tab. Only certain security levels should see certain tabs. I have attached a screenshot of what I am trying to give access to by Security Level. The code in the video allows me to make the Admin Page not clickable for non-Admin security levels. But I am hoping to make a few more un-clickable based on the security levels.
On the tblSecurityLevel, row 1 is Admin. Row 2 is Manager. Row 3 is User. In the code below, where it says "If UserLevel = 1 Then", that is referring to the tblSecurityLevel row.
How would I modify this code to allow for multiple security levels and disable the tabs based on the security level and login username? Here the code:
Private Sub btnLogin_Click()
Dim UserLevel As Integer
Dim Username As String
Dim tempUsername As String
If IsNull(Me.txtUsername) Then
MsgBox "Please Enter Username", vbInformation, "Username Required"
Me.txtUsername.SetFocus
ElseIf IsNull(Me.txtPassword) Then
MsgBox "Please Enter Password", vbInformation, "Username Required"
Me.txtPassword.SetFocus
Else
If (IsNull(DLookup("[Username]", "tbl1Employees", "[Username] ='" & Me.txtUsername.Value & "' And Password = '" & Me.txtPassword.Value & "'"))) Then
MsgBox "Incorrect Username or Password"
Else
tempUsername = Me.txtUsername.Value
Username = DLookup("FirstName", "tblEmployees", "Username = '" & Me.txtUsername.Value & "'")
UserLevel = DLookup("SecurityLevel", "tblEmployees", "Username = '" & Me.txtUsername.Value & "'")
DoCmd.Close
'open different form according to user level
If UserLevel = 1 Then ' for admin
'open different form according to user level DoCmd.OpenForm "Dashboard" Forms![Dashboard]![txtLogin] = tempUsername Forms![Dashboard]![txtUser] = Username Forms![Dashboard]!AdminTab.Enabled = UserLevel < 2 Forms![Dashboard]!AddNewCustomer.Enabled = UserLevel < 3
Ok, you got the userlevel (not that you should EVER use Dlookup, but hey...) UserLevel = DLookup("SecurityLevel", "tblEmployees", "Username = '" & Me.txtUsername.Value & "'")
Now you build a Select Case
Select Case UserLevel Case 1 ' admin 'do nothing, show all -- and show, not enable Case 2 ' manager Forms![Dashboard]!AdminTab.Visible= False Case 3 ' trusted employee Forms![Dashboard]!AdminTab.Visible= False Forms![Dashboard]!ManagerTab.Visible= False case 4 ' peon Forms![Dashboard]!AdminTab.Visible= False Forms![Dashboard]!managerTab.Visible= False Forms![Dashboard]!trustedTab.Visible= False case else ' no good login, show nothing Forms![Dashboard]!AdminTab.Visible= False Forms![Dashboard]!managerTab.Visible= False Forms![Dashboard]!trustedTab.Visible= False Forms![Dashboard]!workTab.Visible= FalseEnd select
Hi Antonio! Thanks for the quick reply. I added your code and the tabs are still visible, except for the Admin tab. So they are all showing up. Any ideas why?
Nick67, thanks for your reply! Where do i put your code?
Antonio Salva Ripoll
Hi Brasiman.
If you want to hide the tabs, simply change the 'Enabled' property by 'Visible'. I don't know the tabs names, but your code can be:
Forms![Dashboard]!AdminTab.Visible = UserLevel < 2 DoCmd.Close 'open different form according to user level......... Forms![Dashboard]!AddNewCustomer.Visible = UserLevel < 3
Try with all user levels, (you can force its value using the inmedite window or adding a line after
.........UserLevel = DLookup("SecurityLevel", "tblEmployees", "Username = '" & Me.txtUsername.Value & "'")' This line forces the userlevel value, remember to delete or comment it after the testsUserLevel = 3.........
Thanks Antonio! So i copied the code in, how it hides the AdminTab and AddNewCustomerTab regardless of the username and password. Even if the username and password are assigned the Administrator userlevel, those two tabs are hidden.
Antonio Salva Ripoll
Now, change
'**************************************************************************' This line forces the userlevel value, remember to delete or comment it after the tests UserLevel = 3'**************************************************************************
'**************************************************************************' This line forces the userlevel value, remember to delete or comment it after the tests UserLevel = 2'**************************************************************************
Now, the user level is forced to be "Manager". Change to UserLevel = 1 to force as "Admin"
Remove the line to use the UserName.
Regards.
Antonio.
brasiman
ASKER
Antonio, thanks for your patience! I'm with you now. This is awesome. There is only one thing left I can see. I added a 4th row because I have 4 security levels. The 4th level is SearchCustomer (SearchCustomerTab). Here's the line I added:
When i have a username with Level 4, it grays out the tab Search Customer, but that is the first tab, therefore the Search Customer tab is selected so I can see the data, I just can't click out of it. Is there a way to say in that case, do something else? Maybe i need to create another tab with nothing on it so it then moves to that tab.
I actually think i figured out a way. I just added a new tab at the beginning called Welcome. I will just put something random there to welcome them to the form. I think this works. I am testing now.
brasiman
ASKER
Ok, everything works great except one thing. When a username with Admin security logs in, they can only access the Admin tab. How do I make it so the SecurityLevel Admin can access all the tabs. Here's what I have. So close!
If you use the equal sign, the tab will be enabled only with that userlevel; in the other hand, if you use the minor sign, the tab will be enabled with any lower userlevel value .
Also, you can use a minor or equal condition (<=).
I've prepared you a very simple sample databse (Access 2003 format) with one form and 2 tab controls. In one tabcontrol I use the "Visible" property and in the other I use the "Enabled" property.
The form name is "Dashboard" and the tab names are similar to yours. DashBoard.mdb
Hi Antonio! No problem! I really appreciate your help! Thank you for your example it was SOOO helpful. One last question, is it possible to say something like
Forms![Dashboard]!AdminTab.Enabled = UserLevel = 1 And UserLevel = 3
I added the "And UserLevel = 3". This would make the doing the access level combinations easier. I just thought of a situation where some security levels might need access to a different level.
Antonio Salva Ripoll
Hi brasiman.
You can make any combination you want, but remember that logic is boolean, so if you say "UserLevel = 1 AND UserLevel = 3" you'll never get the tab enabled.
The reason is very simple, userlevel never will get, at the same time, the values of 1 and 3, it will be 1 or 3, but never the 2 values at the same time.
Instead of using an "And" clause, you must use the "Or" clause.
Forms![Dashboard]!AdminTab.Enabled = UserLevel = 1 Or UserLevel = 3
I added the "And UserLevel = 3". This would make the doing the access level combinations easier. I just thought of a situation where some security levels might need access to a different level.
This is why I showed you the select case structure
Select Case UserLevel
Case 1 ' admin
'do nothing, show all -- and show, not enable
Case 2 ' manager
Forms![Dashboard]!AdminTab.Visible= False
Case 3 ' trusted employee
Forms![Dashboard]!AdminTab.Visible= False
Forms![Dashboard]!ManagerTab.Visible= False
case 4 ' peon
Forms![Dashboard]!AdminTab.Visible= False
Forms![Dashboard]!managerTab.Visible= False
Forms![Dashboard]!trustedTab.Visible= False
case else ' no good login, show nothing
Forms![Dashboard]!AdminTab.Visible= False
Forms![Dashboard]!managerTab.Visible= False
Forms![Dashboard]!trustedTab.Visible= False
Forms![Dashboard]!workTab.Visible= False
End select
Then you are driving things by the userlevel -- which is a discete one/other thing.
In each Case, you can put however many statements as you need to customize the UI
If both userlevel one and userlevel three need the admin tab then Forms![Dashboard]!AdminTab.Enabled
goes in each Case that's applicable.
Even more powerful is Select Case True.
With that, you create expressions that evaluate to Booleans.
The FIRST true expression gets its code block executed.
The rest get skipped.
That's how Select Case works.
So you put your most specific, most persnicketty cases first, and work down to the general.
You can use a code similar to this one:
Open in new window
I hope this be helpful.
Regards.
Antonio.