A Little Help With Tab Control (1 Viewer)

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
Through other threads of asking and receiving help on my database it was suggested that I ditch the navigation form layout and go with tabbed controls.

I was finally able to figure out how to get all of the forms in the tabbed control and them function correctly. I was being stupid and was trying to copy and paste the form controls on the tab pages when I simply needed to drag and drop the entire forms on the pages. This is all fine but now I am trying to fix the permissions to the tabs by access level of the users.

Before I had multiple navigation forms that only had the "tabs" that the specific access level users needed to see so I had 5 different "main forms". When the user logged in the code checked to see what the users access level was and directed them tot he appropriate navigation form containing forms specific to their jobs.

Now that I have only one "main form" with all of the forms in the tabbed control... can someone help me with the code changes to only show the tabs related to the access levels?

Here is the "main form":


*Admin need access to all pages. tbl_accesslevel - AutoNumber field "ID"=1, field "AccessLvl"=Admin
*VisInspector needs access only to pages 1,5 & 6 tbl_accesslevel - AutoNumber field "ID"=2, field "AccessLvl"=VisInspector
*LabInspector needs access only to pages 2,5 & 6 tbl_accesslevel - AutoNumber field "ID"=3, field "AccessLvl"=LabInspector
*MultiInspector needs access only to pages 1,2,5 & 6 tbl_accesslevel - AutoNumber field "ID"=4, field "AccessLvl"=MultiInspector
*Engineer needs access only to pages 0 & 5 tbl_accesslevel - AutoNumber field "ID"=5, field "AccessLvl"=Engineer

in the code for the login form it used to look like this which checked their access level and directed them to the correct "main form" designed for that access level:
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 & "'")
            
        
            Select Case Credentials.AccessLvlID
                Case 1
                    If showProfile() Then
                        DoCmd.OpenForm "frm_home", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_home"
                    End If
                Case 2
                    If showProfile() Then
                        DoCmd.OpenForm "frm_visualinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_visualinspector"
                    End If
                Case 3
                    If showProfile() Then
                        DoCmd.OpenForm "frm_labinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_labinspector"
                    End If
                Case 4
                    If showProfile() Then
                        DoCmd.OpenForm "frm_multiinspector", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_multiinspector"
                    End If
                Case 5
                    If showProfile() Then
                        DoCmd.OpenForm "frm_engineer", , , , , , "profile"
                    Else
                        DoCmd.OpenForm "frm_engineer"
                    End If
                Case 6
                    MsgBox "Your Account Has Been Deactivated. Please Contact the Administrator."
                Case Else
                    DoCmd.OpenForm "frm_loginform"
            End Select
            
            DoCmd.Close acForm, Me.Name
        
        Else
            MsgBox "Incorrect Login or Password"
        End If
    End If
    
End Sub

Private Function showProfile() As Boolean
    Dim Password As Variant
    Dim Question1 As Variant
    Dim Answer1 As Variant
    Dim Question2 As Variant
    Dim Answer2 As Variant
    Dim Question3 As Variant
    Dim Answer3 As Variant
    
    Password = DLookup("Password", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question1 = DLookup("Question1", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer1 = DLookup("Answer1", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question2 = DLookup("Question2", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer2 = DLookup("Answer2", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Question3 = DLookup("Question3", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    Answer3 = DLookup("ID", "tbl_users", "UserName = '" & Credentials.UserName & "'")
    
    showProfile = (Password = "password" Or IsNull(Question1) Or IsNull(Answer1) Or IsNull(Question2) Or IsNull(Answer2) Or IsNull(Question3) Or IsNull(Answer3))
End Function

Does this make sense? This code still works if I create multiple "main forms" again but that is no different than using the navigation forms.

The profile parts of the code do not work though because the code in the main form has this code to open the frm_userprofile but the reference is to a navigation subform and I had to comment the openargs part out or I get an error: (this main navigation form was used only by admin the others were similar)
Code:
Private Sub Form_Open(Cancel As Integer)
    If Credentials.AccessLvlID <> 1 Then
        DoCmd.OpenForm "frm_loginform"
        Cancel = 1
    End If
    If OpenArgs = "profile" Then
        DoCmd.BrowseTo acBrowseToForm, "frm_userprofile", "frm_home.NavigationSubform"
    End If
End Sub
 

Attachments

  • Capture1.JPG
    Capture1.JPG
    67.1 KB · Views: 653
Last edited:

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
I am pretty lost on this one as I know very little about tabbed controls since the navigation form option was easy enough.

Been reading so much I don't know what to think anymore.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 02:14
Joined
Aug 30, 2003
Messages
36,118
In general you can set the visibility of each tab (page) with:

Me.PageName.Visible = True 'or False as appropriate

So you can start with all tabs hidden and your code could set the appropriate ones visible based on the security level. I can't recall offhand if a loop of controls would see each page. If so you could use the Tag property in that loop (if the tag property contains "VisInspector" make it visible type of thing).
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:14
Joined
Sep 21, 2011
Messages
14,037
You have *VisInspector in there twice, but are lucky enough to have distinct first characters for the levels?,

A,L,M,E,V

You could use Instr to look for the relevant character in the Tag property and if found make the page visible as MajP has shown.?

aircode

Code:
 For Each tp As TabPage In TabControl1.TabPages
     Me.tp.Visible = instr(1,Me.tp.tag,left(userlevel,1)) >0
  Next
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
Ugh... I think I will stick with the navigation forms... it works

Thanks guys for the input
 

Mark_

Longboard on the internet
Local time
Today, 02:14
Joined
Sep 12, 2017
Messages
2,111
I'd recommend having ONE form as that limits where you have to make changes. More than one means you'll have to make sure you make any changes more than one location.

A little time now getting the tabs to hide/unhide will save you hours later the first time you have to really rework your screen.
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
I'd recommend having ONE form as that limits where you have to make changes. More than one means you'll have to make sure you make any changes more than one location.

A little time now getting the tabs to hide/unhide will save you hours later the first time you have to really rework your screen.

That is definately the desired configuration but i am realizing I have to strip all of my existing code and start over and I have been struggling with the login form code.
 

BigHappyDaddy

Coding Monkey Wanna-Be
Local time
Today, 02:14
Joined
Aug 22, 2012
Messages
205
Ugh... I think I will stick with the navigation forms... it works

No one is forcing you to implement any type of solution. But a question was asked and members of this forum with years (if not decades) of experience have offered solutions that will undoubtedly save you hours/days/weeks of frustration in the future. Remember, we all started out just like you and already made the mistakes you are making. Learn from us, please? :D

Sincerely, best of luck to you!
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
No one is forcing you to implement any type of solution. But a question was asked and members of this forum with years (if not decades) of experience have offered solutions that will undoubtedly save you hours/days/weeks of frustration in the future. Remember, we all started out just like you and already made the mistakes you are making. Learn from us, please? :D

Sincerely, best of luck to you!

I understand. I have been trying to implement solutions suggested.

I now have my login form (frm_loginform) working and recognizing who is logged in with this code:
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 & "'")
    
    DoCmd.OpenForm "frm_home"
    DoCmd.Close acForm, Me.Name
    
    End If
    End If

End Sub

I know I need something in the login form code to recognize access level 6 which is a deactivated user that will display the message and on closing the messagebox close the database but not sure yet how.

And I used the suggestion from pbaldy for displaying the pages with this code for my main form (frm_home) but it does not work... no matter the access level all pages and tabs still show.
Code:
Private Sub Form_Open(Cancel As Integer)
    
    If Credentials.AccessLvlID = 1 Then
        Me.NewRecordInputForm.Visible = True
        Me.VisInputForm.Visible = True
        Me.LabInputForm.Visible = True
        Me.NewPartForm.Visible = True
        Me.NewUserForm.Visible = True
        Me.UserProfileForm.Visible = True
        Me.ReportCenterForm.Visible = True
        Cancel = 1
    End If
    
    If Credentials.AccessLvlID = 2 Then
        Me.NewRecordInputForm.Visible = False
        Me.VisInputForm.Visible = True
        Me.LabInputForm.Visible = False
        Me.NewPartForm.Visible = False
        Me.NewUserForm.Visible = False
        Me.UserProfileForm.Visible = True
        Me.ReportCenterForm.Visible = True
        Cancel = 1
    End If
    
    If Credentials.AccessLvlID = 3 Then
        Me.NewRecordInputForm.Visible = False
        Me.VisInputForm.Visible = False
        Me.LabInputForm.Visible = True
        Me.NewPartForm.Visible = False
        Me.NewUserForm.Visible = False
        Me.UserProfileForm.Visible = True
        Me.ReportCenterForm.Visible = True
        Cancel = 1
    End If
    
    If Credentials.AccessLvlID = 4 Then
        Me.NewRecordInputForm.Visible = False
        Me.VisInputForm.Visible = True
        Me.LabInputForm.Visible = True
        Me.NewPartForm.Visible = False
        Me.NewUserForm.Visible = False
        Me.UserProfileForm.Visible = True
        Me.ReportCenterForm.Visible = True
        Cancel = 1
    End If
    
    If Credentials.AccessLvlID = 5 Then
        Me.NewRecordInputForm.Visible = True
        Me.VisInputForm.Visible = False
        Me.LabInputForm.Visible = False
        Me.NewPartForm.Visible = False
        Me.NewUserForm.Visible = False
        Me.UserProfileForm.Visible = True
        Me.ReportCenterForm.Visible = False
        Cancel = 1
    End If

End Sub
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:14
Joined
Sep 21, 2011
Messages
14,037
Try moving it to the Load event?
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
Try moving it to the Load event?

I tried that but it pops the run-time error 2501 OpenForm action was canceled and it highlighted this line in my login form code:
Code:
DoCmd.OpenForm "frm_home"
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:14
Joined
Sep 21, 2011
Messages
14,037
No, not that code, the code you had in the Form_Open event to hide/show tab pages

Also is that form going to know what Credentials.AccessLvlID is set to.?
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:14
Joined
May 21, 2018
Messages
8,463
Sorry to confuse things but if you have not fully given up on the tab control, it is really pretty simple. All solutions posted would work. This is my entire code for and it appears to do what you describe. I am having trouble loading the demo.
Code:
Private Sub Form_Load()
  If Not Trim(Me.OpenArgs & " ") = "" Then
    SetAccess (Me.OpenArgs)
  Else
    'hide all tabs
    SetAccess (0)
  End If
End Sub

Public Sub SetAccess(AccLvl As String)
   'Replace with your tab name
   Const TabName = "tabPermissions"
   Dim pg As Access.Page
   Dim tb As Access.TabControl
   
   Set tb = Me.Controls(TabName)
   'if not admin hide everything except page 5
   If AccLvl <> 1 Then
      tb.Pages(5).SetFocus
      For Each pg In tb.Pages
          If pg.PageIndex <> 5 Then pg.Visible = False
      Next pg
   End If
   Select Case AccLvl
     Case 2 'Visual inspection
       tb.Pages(1).Visible = True
       tb.Pages(6).Visible = True
     Case 3 ' Lab Inspector
       tb.Pages(2).Visible = True
        tb.Pages(6).Visible = True
     Case 4 'Multi Inspector
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(6).Visible = True
     Case 5  'Engineer
       tb.Pages(0).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(6).Visible = True
    End Select
End Sub

I simply pass in the access level using open args
Code:
Private Sub Command1_Click()
  DoCmd.OpenForm "frmPermissions", , , , , , Me.cmboOpen
End Sub
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
No, not that code, the code you had in the Form_Open event to hide/show tab pages

Also is that form going to know what Credentials.AccessLvlID is set to.?

I did move the code that was in the form open event that shows or hides the tabs... but by moving it to the form load even it made the error trying to open the form after logging in.

I believe the form is not knowing about the Credentials thing.
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
Sorry to confuse things but if you have not fully given up on the tab control, it is really pretty simple. All solutions posted would work. This is my entire code for and it appears to do what you describe. I am having trouble loading the demo.
Code:
Private Sub Form_Load()
  If Not Trim(Me.OpenArgs & " ") = "" Then
    SetAccess (Me.OpenArgs)
  Else
    'hide all tabs
    SetAccess (0)
  End If
End Sub

Public Sub SetAccess(AccLvl As String)
   'Replace with your tab name
   Const TabName = "tabPermissions"
   Dim pg As Access.Page
   Dim tb As Access.TabControl
   
   Set tb = Me.Controls(TabName)
   'if not admin hide everything except page 5
   If AccLvl <> 1 Then
      tb.Pages(5).SetFocus
      For Each pg In tb.Pages
          If pg.PageIndex <> 5 Then pg.Visible = False
      Next pg
   End If
   Select Case AccLvl
     Case 2 'Visual inspection
       tb.Pages(1).Visible = True
       tb.Pages(6).Visible = True
     Case 3 ' Lab Inspector
       tb.Pages(2).Visible = True
        tb.Pages(6).Visible = True
     Case 4 'Multi Inspector
       tb.Pages(1).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(6).Visible = True
     Case 5  'Engineer
       tb.Pages(0).Visible = True
       tb.Pages(2).Visible = True
       tb.Pages(6).Visible = True
    End Select
End Sub

I simply pass in the access level using open args
Code:
Private Sub Command1_Click()
  DoCmd.OpenForm "frmPermissions", , , , , , Me.cmboOpen
End Sub

I am looking into this right now to see how it works.

I am a bit confused about 1 thing...

The last sub... I do not understand what this is... I do not have any combo boxes related to opening forms... what is that for?
 

psyc0tic1

Access Moron
Local time
Today, 04:14
Joined
Jul 10, 2017
Messages
360
After trying that code... no matter who logs in... the only page visible is 5
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:14
Joined
May 21, 2018
Messages
8,463
On my demo form I had a combo box that showed the choices
Admin
Visual Inspector
etc

and the hidden bound column had the permission levels
1
2
...

If I pick visual inspector I pass in the open args 2 to my demo form. Then in the form load event it determines the args and passes that value to the SetAccess (should have called it SetPermissions not to confuse with MS Access)
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 05:14
Joined
May 21, 2018
Messages
8,463
After trying that code... no matter who logs in... the only page visible is 5

Good, that is exactly what it should do, since you did not pass in an argument in the openargs. Now call the form and pass in the correct access level.
 

Mark_

Longboard on the internet
Local time
Today, 02:14
Joined
Sep 12, 2017
Messages
2,111
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.
 

Gasman

Enthusiastic Amateur
Local time
Today, 09:14
Joined
Sep 21, 2011
Messages
14,037
I'm going to hazard a guess that nothing is passed in, hence 0 to the sub.?
 

Users who are viewing this thread

Top Bottom