Avatar of brasiman
brasiman
 asked on

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
       
                    DoCmd.OpenForm "Dashboard"
                    Forms![Dashboard]![txtLogin] = tempUsername
                    Forms![Dashboard]![txtUser] = Username
           
            Else
                DoCmd.OpenForm "Dashboard"
                Forms![Dashboard]![txtLogin] = tempUsername
                Forms![Dashboard]!AdminTab.Enabled = False
                Forms![Dashboard]![txtUser] = Username
           
                End If
            End If
        End If
    End If

End Sub
Security-Level-and-Nav-Tabs.jpg
Microsoft AccessVB ScriptVisual Basic Classic

Avatar of undefined
Last Comment
Nick67

8/22/2022 - Mon
Antonio Salva Ripoll

Hi.

You can use a code similar to this one:

'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

Open in new window



I hope this be helpful.

Regards.

Antonio.
Nick67

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= False
End select

Open in new window

brasiman

ASKER
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?
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
brasiman

ASKER
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

Open in new window


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 tests

UserLevel = 3
...
...
...

Open in new window

I hope this be helpful.

Best regards.

Antonio.
brasiman

ASKER
Thanks Antonio. Where would I plug this code into mine...comparing it to the original code i put in my first question? Sorry, I'm new to this. Thanks!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
Antonio Salva Ripoll

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
brasiman

ASKER
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
'**************************************************************************

Open in new window


to

'**************************************************************************
' This line forces the userlevel value, remember to delete or comment it after the tests
        UserLevel = 2
'**************************************************************************

Open in new window


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:

        Forms![Dashboard]!AdminTab.Enabled = UserLevel < 2
        Forms![Dashboard]!AddNewCustomerTab.Enabled = UserLevel < 3
       Forms![Dashboard]!SearchCustomerTab.Enabled = UserLevel < 4

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 started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
brasiman

ASKER
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!

        Forms![Dashboard]!AdminTab.Enabled = UserLevel = 1
        Forms![Dashboard]!AddNewCustomerTab.Enabled = UserLevel = 2
       Forms![Dashboard]!SearchCustomerTab.Enabled = UserLevel = 3

Open in new window

Antonio Salva Ripoll

Hi Brasiman.

Sorry I could not answer before, I live in Spain and we have a 7 hours difference.

The cause you can only access one tab is because you use an equal (=) sign  instead a minor than (<) sign.

Forms![Dashboard]!AdminTab.Enabled = UserLevel = 1
Forms![Dashboard]!AdminTab.Enabled = UserLevel < 1


 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 (<=).

Forms![Dashboard]!AdminTab.Enabled = UserLevel <= 3


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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
brasiman

ASKER
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

Open in new window


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

Open in new window


In this way, if userlevel is 1 or 3, the tab will be enabled.
brasiman

ASKER
Antonio, you're awesome. Thank you sooo much!!! I really do appreciate your help!
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
brasiman

ASKER
Thanks Antonio. Extremely helpful. Thank you!!!!
Nick67

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.