VBA - How to make a sub tab control form appear in only a specific page of the main tab control form?

Question

So, I have a main tab control form called TabCtl11. The side left bar menu; for example, when Dashboard Menu is clicked it correspond to the page1 of the main tab control and Report Menu when clicked it correspond to page2; continue like this until the last menu corresponding to the last page of main tab control.

Our sub tab control name is TabCtl206. Sub control TabCtl206 resides in page3 of Main Tab control TabCtl11 but after completing the designs it appears in all pages of the main tab control. I have tried many ways to implement this and the code below worked but I am not sure if this approach is good practice or not.

How can I properly enable sub tab control to only appear in pages 3 on main tab control ?

Code below;

Option Compare Database

Private Sub btnSearch_Click()
    SearchAndPopulate
End Sub



Private Sub Form_Load()
    ' Set the default tab index for TabCtl11.
    Me.TabCtl11.Value = 0

    ' Hide TabCtl206 on form load.
    Me.TabCtl206.visible = False

    ' Set controls on all pages to read-only on form load.
    SetAllControlsEnabled False
    SetControlsEnabledOnPage "Personal_Infor", True

    ' Ensure the tab control is hidden on form load.
    Me.TabCtl206.visible = False

    ' Set controls on all pages to visible on form load.
    SetAllControlsVisible False  ' Adjust visibility based on your requirement.
    
    ' Set visibility for each page individually
    SetControlsVisibleOnPage "Personal_Infor", True
    SetControlsVisibleOnPage "Village_Infor", True
    SetControlsVisibleOnPage "Medical_Infor", True
    SetControlsVisibleOnPage "Education_Infor", True
    SetControlsVisibleOnPage "Ministerial_Infor", True
    SetControlsVisibleOnPage "Other_Infor", True
    
    ' Set read-only status for all pages
    SetAllControlsEnabled False
    SetControlsEnabledOnPage "Personal_Infor", True
    
    ' Maximize the form window
    DoCmd.Maximize
End Sub

Private Function SetAllControlsEnabled(enabled As Boolean)
    ' Helper function to set the Enabled property of all controls on all pages.

    Dim i As Integer
    For i = 0 To Me.TabCtl206.Pages.Count - 1
        SetControlsEnabledOnPage Me.TabCtl206.Pages(i).Name, enabled
    Next i
End Function

Private Sub SetControlsEnabledOnPage(pageName As String, enabled As Boolean)
    ' Helper function to set the Enabled property of all controls on a specific page.

    On Error Resume Next  ' Ignore errors if the page does not exist
    Dim ctrl As Control
    For Each ctrl In Me.TabCtl206.Pages(pageName).controls
        ctrl.enabled = enabled
    Next ctrl
    On Error GoTo 0  ' Reset error handling
End Sub

Private Function SetAllControlsVisible(visible As Boolean)
    ' Helper function to set the Visible property of all controls on all pages.

    Dim i As Integer
    For i = 0 To Me.TabCtl206.Pages.Count - 1
        SetControlsVisibleOnPage Me.TabCtl206.Pages(i).Name, visible
    Next i
End Function

Private Sub SetControlsVisibleOnPage(pageName As String, visible As Boolean)
    ' Helper function to set the Visible property of all controls on a specific page.

    On Error Resume Next  ' Ignore errors if the page does not exist
    Dim ctrl As Control
    For Each ctrl In Me.TabCtl206.Pages(pageName).controls
        ctrl.visible = visible
    Next ctrl
    On Error GoTo 0  ' Reset error handling
End Sub

Private Function SetBtnLeftSideBar()
    Dim Ax As Single
    Ax = CSng(Mid(Me.ActiveControl.Name, 2)) ' Remove the unnecessary addition of 0
    
    ' Display names and values for debugging
    ' MsgBox "Active Control Name: " & Me.ActiveControl.Name
    ' MsgBox "Ax: " & Ax
    
    ' Set a default background color for buttons
    Dim defaultColor As Long
    defaultColor = RGB(45, 80, 143) ' Adjust the RGB values 

    For i = 1 To 9
        ' Set the background color for buttons
        Me("B" & i).BackColor = defaultColor
    Next

    ' Set the background color for the active button
    Me.ActiveControl.BackColor = RGB(100, 150, 200) ' Adjust the RGB values for the active button color

    Me("P" & Ax).SetFocus
    LB0.Caption = Me("B" & Ax).Caption

    ' MsgBox "Box0.BackColor: " & Box0.BackColor
    ' MsgBox "Box1.BackColor: " & Box1.BackColor

    ' MsgBox Ax
End Function


Private Sub TabCtl11_Change()
    ' This event is triggered when the selected page changes in the main control tab.
    ' You can use this event to show/hide the inner tab control based on the selected page.

    ' Show the inner tab control only when Personnel Menu (B3) is selected.
    If Me.TabCtl11.Value = 2 Then ' Adjust the index based on the actual position of Personnel Menu in your menu structure.
        Me.TabCtl206.visible = True
    Else
        Me.TabCtl206.visible = False
    End If
End Sub

Private Sub TabCtl206_Change()
    ' This event is triggered when the selected page changes in the inner tab control.
    ' You can use this event to enable/disable controls on different pages.

    ' Disable controls on all pages except the current page.
    SetControlsEnabledOnPage Me.TabCtl206.Pages(Me.TabCtl206.Value).Name, True
End Sub

Private Sub txtSearch_AfterUpdate()
    SearchAndPopulate
End Sub

Private Sub SearchAndPopulate()
    Dim searchCriteria As String
    searchCriteria = Me.txtSearch.Value  ' Use the exact value without wildcards

    ' Your query to retrieve data based on search criteria
    Dim strSQL As String
    strSQL = "SELECT * FROM tblPersonnel " & _
             "WHERE PersonnelID = " & searchCriteria & ";"

    ' Execute the query
    Me.RecordSource = strSQL

    ' Refresh the form to display the updated records
    Me.Refresh
    
    Debug.Print "SQL Query: " & strSQL
End Sub 

Am I doing this correctly ? I just don't know if this approach will cause errors in the future. Please assist. I welcome any comments.

Answer 1

It seems you cannot insert a TabControl in a Page of another TabControl (nested TabControl), even if the page is focused, because access sets parent of TabControl to the Form automatically.

Instead, create another form with the second one (TabCtl206) and remove all spaces, and insert this form as a SubForm in Page3.