Show/hide different tabs based on multiple fields?

[templeowls]

See the attached example DB. I'm trying to show/hide tabs from my tab control based on multiple criteria and multiple fields.

For instance, I want the 'summary' tab to always show no matter what. However, I want the 'billing' tab to only appear if 'Category = Billing' while I only want 'room cleaniness' tab to appear if 'SubCategory = RoomCleaniness'



Any recommendations on how to code this based on this example?

[ranman256]

use constants to help 'spell' which numeric tab is which

'define tab #s
const kTabBILL = 1
const kTabCLEAN = 2


'on some event:
TabCtl.Pages(kTabBILL).Visible =  txtCategory = "Billing"
TabCtl.Pages(kTabCLEAN).Visible =  txtSubCategory ="RoomCleaniness"

[templeowls]

Okay. Should I place that code into the On Load of the form or the VBA for the tab control itself?

[Bob Fitz]

Okay. Should I place that code into the On Load of the form or the VBA for the tab control itself?

Try in the Form's On Current event and the Form's After Update event.

[templeowls]

That worked. Thanks so much you guys!

[templeowls]

Actually, i ran into a new issue when adding this to my actual DB.

In my actual DB, the first four tabs should always appear (in my example DB, I only had one). When implementing this solution, only the first tab is constant. Any advice?

[Micron]

Current event should fire after form is updated or requeried (but not refreshed) as well as navigating from one record to another, so both events might not be needed. Then again, I didn't see the db - there were already 6 downloads and I figured I was too late to come to the party.

[ssanfu]

Actually, i ran into a new issue when adding this to my actual DB.
In my actual DB, the first four tabs should always appear (in my example DB, I only had one). When implementing this solution, only the first tab is constant. Any advice?

Well, it really helps if you provide info on what you are trying to do. In this case, the number of tabs. possibly the names of the tabs and the conditions which affect the visibility of the tabs.

Ranman's example allows you to use a name instead if a number for the page index by using constants.

You could use

    Me.TabCtl23.Pages(1).Visible = (Me.Category = "Billing")
    Me.TabCtl23.Pages(2).Visible = (Me.SubCategory = "RoomCleaniness")

but using constants makes it easier to read and understand (IMHO)

'define tab #s
    Const kTabBILL = 1
    Const kTabCLEAN = 2
    
    'on some event:
    Me.TabCtl23.Pages(kTabBILL).Visible = Me.Category = "Billing"
    Me.TabCtl23.Pages(kTabCLEAN).Visible = Me.SubCategory = "RoomCleaniness"

One thing to remember is that the pages index is 0 (zero) based.
So, in your example dB,
the first tab (Page) is named "Summary" and toe page index 0.
The second tab (Page) is named "Billing" and the page index 1.
The third tab (Page) is named "Room Cleaniness" and the page index 2.

So lets say that you actual dB has 6 tabs (pages) and you want the first 4 to always be visible - only the last 2 tabs (page indexes 4 & 5) could be hidden. And let's keep the last 2 tab names the same.
Using Ranman's code, it would look like

'define tab #s
    Const kTabBILL = 4  ' the 5th tab
    Const kTabCLEAN = 5  ' the sixth tab
    
    'on some event:
    Me.TabCtl23.Pages(kTabBILL).Visible = Me.Category = "Billing"
    Me.TabCtl23.Pages(kTabCLEAN).Visible = Me.SubCategory = "RoomCleaniness"

If you didn't use the constants, the code would look like

    Me.TabCtl23.Pages(4).Visible = (Me.Category = "Billing")
    Me.TabCtl23.Pages(5).Visible = (Me.SubCategory = "RoomCleaniness")

As long as the visible property is explicitly set to TRUE of FALSE or the expression after the equals sign evaluates to TRUE or FALSE, you can show or hide tab(s).
Another way to write the code

Private Sub Form_Current()
'define tab #s
    Const kTabBILL = 4  ' the 5th tab
    Const kTabCLEAN = 5  ' the sixth tab


    If Me.Category = "Billing" Then
        Me.TabCtl23.Pages(kTabBILL).Visible = True
    Else
        Me.TabCtl23.Pages(kTabBILL).Visible = False
    End If

    If Me.SubCategory = "RoomCleaniness" Then
        Me.TabCtl23.Pages(kTabCLEAN).Visible = True
    Else
        Me.TabCtl23.Pages(kTabCLEAN).Visible = False
    End If

End Sub

Give it a try. If you have problems, post back with the info and what code you tried.