Hide a Form tab depending on the value of a field

Question

pretty simple question here in scope.

Question: Wondering If I would be able to hide the tabs of a form based off the values of a table's fields.

I have been reading the 2019 Access Bible and so far it is still unclear to me how I would write the VBA module to constantly be running. Im asking the question a little early in my attempts, but hoping I can ask this well enough to get a head start.

I dont quite understand the execution model of VBA for access yet. I have prior expierence coding but this will be my 1st time in access. Just looking for a little help on how to write the function scope. You see below that I think it should be "Main" something, as I want it to run whenever in form view. Like I know how to write Private sub functions to respond to a button click. But not for a function that just runs in the background of the form.

I guess the real question is when to execute? Right? Any suggestions?

I was thinking something along the line of this below.

Main function()
If Me.FieldProcess_Water = "1" Then
Me.TabProcess_Water.Visible = True
Else
Me.TabProcess_Water.Visible  = False
End If
End Sub

Answer 1

This requires some setup to reduce redundant code but should do what you want.

First you'll need your checkbox names and page names to be similar. In my example I have the page names as Tab_Name and the checkboxes as just Name. eg. Tab_Process Water and Process Water.

Then Create a sub called Form_Current() in the form's code-behind.

Private Sub Form_Current()
    Dim chk As Control
    
    For Each chk In Me.Controls
        If chk.ControlType = acCheckBox Then
            If chk = False Then
               'Change TabCtl0 to whatever your's is called
                Me.TabCtl0.Pages("Tab_" & chk.Name).Visible = False
            Else
                Me.TabCtl0.Pages("Tab_" & chk.Name).Visible = True
            End If
        End If
    Next
    
End Sub

This will iterate through the current record's checkboxes and toggle it's respective tab's visibility.

To have the CheckBox update the tabs as they are clicked:

Private Sub Form_Load()
    Dim chk As Control
    Dim prop As Variant
    For Each chk In Me.Controls
        If chk.ControlType = acCheckBox Then
           chk.OnClick = "=Check_Click()"
        End If
    Next
End Sub

This will assign a command to each checkbox.

    Dim caller As String
    caller = Me.ActiveControl.Name
    If Me.ActiveControl.Value = False Then
        Me.TabCtl0.Pages("Tab_" & caller).Visible = False
    Else
        Me.TabCtl0.Pages("Tab_" & caller).Visible = True
    End If

This will hide the relevant tabs.