A Little Help With Tab Control (1 Viewer)

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
Very simple demo attached.
 

Attachments

  • DatabasePermissions.accdb
    776 KB · Views: 120

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
psyc0tic1

MajP's code is based off of a passed "AccLvl". What are you passing it? He has it set up to accept a string but is checking for numeric values.

So... my database recognizes who just logged in shown by the login code I posted in post #9. From post #1 I showed the login form code I had previously which directed users to individual navigation forms based on their AccessLvlID.

I don't allow users to choose their "roles" e.g. visual inspector, lab tester etc. from drop down boxes... I need the code to check the tbl_users for their AccessLevel (field name) and then display the pages they need to see.

I do not know anything about the openargs part but I have started reading about it and at the moment I do not get it.

Is it not possible for the main form to remember who just logged in and set page visibility based on their AccessLevel (field name in tbl_users)?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
MajP's code is based off of a passed "AccLvl". What are you passing it? He has it set up to accept a string but is checking for numeric values.
Interesting that even worked, did not catch that mistake. I get that passing the numeric 0 into the string argument casts it into a string "0". But then the select case seems to cast it back.

So more correct would be
in the load
Code:
SetAccess CInt(Me.OpenArgs)
In the procedure
Code:
Public Sub SetAccess(AccLvl As Integer)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
So... my database recognizes who just logged in shown by the login code I posted in post #9. From post #1 I showed the login form code I had previously which directed users to individual navigation forms based on their AccessLvlID.

I don't allow users to choose their "roles" e.g. visual inspector, lab tester etc. from drop down boxes... I need the code to check the tbl_users for their AccessLevel (field name) and then display the pages they need to see
Not suggesting you use a pull down, that was just for demo purposes. I cannot replicate getting your login values. You already have the code to get the acclvlID. Not open your single tabbed form passing in the acclvlID, instead of opening a bunch of different forms depending on permissions. Basically replace the select case with a single call to open the tabbed form.
 

Mark_

Longboard on the internet
Local time
Today, 11:16
Joined
Sep 12, 2017
Messages
2,111
So... my database recognizes who just logged in shown by the login code I posted in post #9. From post #1 I showed the login form code I had previously which directed users to individual navigation forms based on their AccessLvlID.

I don't allow users to choose their "roles" e.g. visual inspector, lab tester etc. from drop down boxes... I need the code to check the tbl_users for their AccessLevel (field name) and then display the pages they need to see.

I do not know anything about the openargs part but I have started reading about it and at the moment I do not get it.

Is it not possible for the main form to remember who just logged in and set page visibility based on their AccessLevel (field name in tbl_users)?

Did you pass his code Credentials.AccessLvlID? I am guessing he'd left it as "String" as you've never posted what Credentials.AccessLvlID is defined as. The CASE using numbers is based off of your original sample but if he played it safe ACCESS is pretty good at understanding 2 = "2" in most cases.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
Did you pass his code Credentials.AccessLvlID? I am guessing he'd left it as "String" as you've never posted what Credentials.AccessLvlID is defined as. The CASE using numbers is based off of your original sample but if he played it safe ACCESS is pretty good at understanding 2 = "2" in most cases.
In my demo and since openargs is a string I was passing in "1", "2", but mistakenly also passed in 0. So I get how that was cast. But in the select case, I was surprised that the numeric checks worked
case 2 works when "2" was passed.
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
Not suggesting you use a pull down, that was just for demo purposes. I cannot replicate getting your login values. You already have the code to get the acclvlID. Not open your single tabbed form passing in the acclvlID, instead of opening a bunch of different forms depending on permissions. Basically replace the select case with a single call to open the tabbed form.

I changed the codes that you mentioned.

I have attached a copy of the database if that helps to determine how to get the tabs to show.

and by the way... I can't thank everyone enough for helping me and putting up with my ignorance.

to log into the database the usernames are user1, user2 etc and the passwords are the same as the user name. user1 is an admin, user2 is a visual inspector etc.
 

Attachments

  • Test.zip
    788.2 KB · Views: 120

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
Well I cant figure it out with my very limited coding brain.
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
So if I login with this code in the login form:
Code:
Private Sub Command1_Click()
    If IsNull(Me.txtLoginID) Then
        MsgBox "Please Enter Login", vbInformation, "Need ID"
        Me.txtLoginID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please Enter Password", vbInformation, "Need Password"
        Me.txtPassword.SetFocus
    Else
        Credentials.UserName = Me.txtLoginID.Value
        If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword Then
            Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    
        DoCmd.OpenForm "frm_home"
        DoCmd.Close acForm, Me.Name
    
    End If
    End If

End Sub

Shouldn't the code on the main form (frm_home) go something like this? (of course this doesn't work but grasping at straws)
Code:
Public Sub Form_Open()
      If Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'") Then
      Select Case Credentials.AccessLvlID
     Case 1
       tb.Pages(0).Visible = True
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = True
       tb.Pages(4).Visible = True
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 2 'Visual inspection
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = False
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 3 ' Lab Inspector
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = False
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 4 'Multi Inspector
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 5  'Engineer
       tb.Pages(0).Visible = True
       tb.Pages(1).Visible = False
       tb.Pages(2).Visible = False
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = False
    End Select
End If
End Sub
 
Last edited:

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
So I just got it to work with this code on my main form (frm_home):
Code:
Option Compare Database

Private Sub Form_Open(Cancel As Integer)
    If Credentials.AccessLvlID <> 1 And Credentials.AccessLvlID <> 2 And Credentials.AccessLvlID <> 3 And Credentials.AccessLvlID <> 4 And Credentials.AccessLvlID <> 5 Then
        DoCmd.OpenForm "Login Form"
        Cancel = 1
    End If

End Sub

Public Sub Form_Load()

    Const TabName = "TabCtl87"
    Dim pg As Access.Page
    Dim tb As Access.TabControl
    Set tb = Me.Controls(TabName)

      If Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'") Then
      Select Case Credentials.AccessLvlID
     Case 1
       tb.Pages(0).Visible = True
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = True
       tb.Pages(4).Visible = True
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 2 'Visual inspection
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = False
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 3 ' Lab Inspector
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = False
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 4 'Multi Inspector
       tb.Pages(0).Visible = False
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = True
     Case 5  'Engineer
       tb.Pages(0).Visible = True
       tb.Pages(1).Visible = False
       tb.Pages(2).Visible = False
       tb.Pages(3).Visible = False
       tb.Pages(4).Visible = False
       tb.Pages(5).Visible = True
       tb.Pages(6).Visible = False
    End Select
End If
End Sub
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
Well it almost worked.

As I started testing:
I logged in as user1 (admin) and all tabs showed.
I logged in as user2 (Visual Inspector) and the proper tabs showed.
I logged in as user3 (Lab Tester) and I got a run-time error "2165" "You can't hide a control that has the focus". highlighted this line: "tb.Pages(1).Visible = False" under "Case 3" in the frm_home code.
I logged in as user4 (Multi Inspector) and the proper tabs showed.
I logged in as user5 (engineer) and the proper tabs showed.

I am confused... when you open the form that is on page 1 the first field after the combobox is selected.

However... page 1 gets False if you log in as user5 and it doesn't throw that run-time error

user1 (admin) sees page 1
user2 (visual inspector) sees page 1
user3 (lab tester) does not see page 1 **ERROR**
user4 (multi tester) sees page 1
user5 (engineer) does not see page 1 **NO ERROR**

Also if hiding a page with a control that has focus is an issue... the form that is on page 2 has the first field after the combobox with focus but no errors are thrown up about hiding that one.
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
Well I got around the issue by adding:
Code:
Me.LabInputForm.SetFocus
Right after "Case 3" and before the calls to visible or not

Don't know if that was the right way but it worked
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
There was no need to modify the code. As I said the only thing you had to do was pass in the access level and it worked. This is all you had to do and the code would have worked 100%
Code:
Private Sub Command1_Click()
    Dim AccLvl As Integer
    If IsNull(Me.txtLoginID) Then
        MsgBox "Please Enter Login", vbInformation, "Need ID"
        Me.txtLoginID.SetFocus
    ElseIf IsNull(Me.txtPassword) Then
        MsgBox "Please Enter Password", vbInformation, "Need Password"
        Me.txtPassword.SetFocus
    Else
        Credentials.UserName = Me.txtLoginID.Value
        If DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'") = Me.txtPassword Then
            Credentials.UserId = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            'Credentials.AccessLvlID = DLookup("AccessLvl", "tbl_users", "UserName = '" & Credentials.UserName & "'")
           'Get the access level and pass it to the form in the using open args which is the 7th argument of the Docmd.openform
           [B][COLOR="Red"] AccLvl = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
            DoCmd.OpenForm "frm_home", , , , , , AccLvl[/COLOR][/B]

            DoCmd.Close acForm, Me.Name
         End If
    End If
End Sub
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 14:16
Joined
May 21, 2018
Messages
8,463
When you are showing and hiding things it is often much simpler to first simply show everything with a loop and hide selected items, or hide everything and show selected items. That was why my code seems shorter. If it was admin I did hid nothing. Else i ran a loop first to hide everything except tab 5. Then I use the select case to show the appropriate items. And yes when you hide something you cannot let it have the focus. That is why in my code I put the focus on tab 5 first since it never gets hidden.
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
Fine!... Let's do it your way!

Just kidding... much simpler code. I was actually proud of myself for making it work even though I knew there was a simpler way.

The only thing about that... there are only 2 scenarios in which I want the UserCP (page 5) to be set focused and that is if the user has a password of "password" or they have not yet filled in their security questions and answers.

Otherwise the first page with the left most tab should have focus
 

Mark_

Longboard on the internet
Local time
Today, 11:16
Joined
Sep 12, 2017
Messages
2,111
Then have a piece of code that checks the security first and sets focus as needed (to a tab you know WON'T be hidden) followed by MajP's code.
 

psyc0tic1

Access Moron
Local time
Today, 13:16
Joined
Jul 10, 2017
Messages
360
So I finally got done with the conversion to tabbed controls... now the db takes forever to load.

With navigation control I believe it defers the forms loading until the corresponding navigation tab is clicked.

With the tabbed control the db loads all of the forms before it opens. Tripled the opening time of the database.

Sigh.

That was a lot of work for no gain.
 

Mark_

Longboard on the internet
Local time
Today, 11:16
Joined
Sep 12, 2017
Messages
2,111
IIRC, if you have disabled the sub-forms and other controls that would normally request data, they should not be loading until you enable the tab and control.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 11:16
Joined
Aug 30, 2003
Messages
36,118
I'm not sure disabling a subform prevents it from loading data. It would be nice if it did. If it doesn't, another solution is to either have a single subform control and switch which subform is displayed in it as you click on buttons/tabs, or load each subform as its tab is clicked on.
 

Mark_

Longboard on the internet
Local time
Today, 11:16
Joined
Sep 12, 2017
Messages
2,111
Paul,
I'm thinking default all controls on all tabs (including controls on subforms) to disabled. Then when you switch to a tab that is valid, you enable onload.
 

Users who are viewing this thread

Top Bottom