Solved

How to disable navigation tab based on username/password...adding to my code.

Posted on 2016-07-29
18
79 Views
Last Modified: 2016-08-02
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
0
Comment
Question by:brasiman
  • 10
  • 6
  • 2
18 Comments
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41735037
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.
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41735062
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

0
 

Author Comment

by:brasiman
ID: 41735114
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?
0
 

Author Comment

by:brasiman
ID: 41735116
Nick67, thanks for your reply! Where do i put your code?
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41735220
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.
0
 

Author Comment

by:brasiman
ID: 41735248
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!
0
 
LVL 2

Accepted Solution

by:
Antonio Salva Ripoll earned 500 total points
ID: 41735257
Here is the complete 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 & "'")

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

        DoCmd.Close
        
        'open different form according to user level
        DoCmd.OpenForm "Dashboard"

        Forms![Dashboard]![txtLogin] = tempUsername
        Forms![Dashboard]![txtUser] = Username

        Forms![Dashboard]!AdminTab.Visible = UserLevel < 2
        Forms![Dashboard]!AddNewCustomer.Visible = UserLevel < 3

    End If

End Sub

Open in new window


Best regards.

Antonio.
0
 

Author Comment

by:brasiman
ID: 41735273
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.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41735301
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.
0
Top 6 Sources for Identifying Threat Actor TTPs

Understanding your enemy is essential. These six sources will help you identify the most popular threat actor tactics, techniques, and procedures (TTPs).

 

Author Comment

by:brasiman
ID: 41735308
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.
0
 

Author Comment

by:brasiman
ID: 41735318
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.
0
 

Author Comment

by:brasiman
ID: 41735323
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

0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41735452
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
0
 

Author Comment

by:brasiman
ID: 41737609
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.
0
 
LVL 2

Expert Comment

by:Antonio Salva Ripoll
ID: 41737698
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.
0
 

Author Comment

by:brasiman
ID: 41737701
Antonio, you're awesome. Thank you sooo much!!! I really do appreciate your help!
0
 

Author Closing Comment

by:brasiman
ID: 41737702
Thanks Antonio. Extremely helpful. Thank you!!!!
0
 
LVL 26

Expert Comment

by:Nick67
ID: 41739918
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.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

707 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now