I have a form with 17 tabs some of which have several subforms. I elected to not hide the tabs because I would then have to add buttons to force them to show so the user could enter data. What I did instead was to alter the tab caption when the tab had data. The code checks a control on each subform that would always be not-null if the form contained data. You could choose to do it the other way around. Your situation would dictate which makes more sense - highlight the ones with data or highlight the ones without data. My tab pages are named because if you use the index value, you will need to change your code if you decide to add new tabs and want to place them between other tabs. Besides, who wants to try to figure out what Tabctl75 is or PageIndex(3) when they read your code?
Public Sub SetTabs()
Dim pgNum As Object
On Error GoTo Err_Proc
For Each pgNum In Me.TabCtlTransactions.Pages
Select Case pgNum.Name
Case "pgIBC123"
If IsNull(Me.sfrmIBC123.Form!VER_NO) Then
pgNum.Caption = " 123"
Else
pgNum.Caption = "**123"
'this statement is here because the change event doesn't fire for the first tab and so the PPTS_ID doesn't get set
Me.txtPPTS_ID = DLookup("PPS_PPTS_STS_NO", "PCR_PCR_PPTS_STS", "PPCR_PRV_CHG_RQT_NO = " & Me.PPCR_PRV_CHG_RQT_NO & " And VER_NO = " & Me.sfrmIBC123.Form!VER_NO & " AND PCR_CORP_FORM_CD_NO = " & IBC123)
End If
Case "pgIBC124"
If IsNull(Me.sfrmIBC124.Form!VER_NO) Then
pgNum.Caption = " 124"
Else
pgNum.Caption = "**124"
End If
Case "pgIBC126"
If IsNull(Me.sfrmIBC126.Form!VER_NO) Then
pgNum.Caption = " 126"
Else
pgNum.Caption = "**126"
End If
Call cboProviderType_AfterUpdate
Case "pgIBC127"
If IsNull(Me.sfrmIBC127.Form!VER_NO) Then
pgNum.Caption = " 127"
Else
pgNum.Caption = "**127"
End If
Case "pgIBC128"
If IsNull(Me.sfrmIBC128.Form!VER_NO) Then
pgNum.Caption = " 128Old"
Else
pgNum.Caption = "**128"
End If
Case "pgIBC128New"
pgNum.Caption = " 128New"
If IsNull(Me.sfrmIBC128New.Form!sfrmIBC128A1.Form!VER_NO) Then
Else
pgNum.Caption = "**128"
End If
If IsNull(Me.sfrmIBC128New.Form!sfrmIBC128A2.Form!VER_NO) Then
Else
pgNum.Caption = "**128"
End If
If IsNull(Me.sfrmIBC128New.Form!sfrmIBC128C.Form!VER_NO) Then
Else
pgNum.Caption = "**128"
End If
If IsNull(Me.sfrmIBC128New.Form!sfrmIBC128D.Form!VER_NO) Then
Else
pgNum.Caption = "**128"
End If
Case "pgIBC131"
If IsNull(Me.sfrmIBC131.Form!VER_NO) Then
pgNum.Caption = " 131"
Else
pgNum.Caption = "**131"
End If
Case "pgIBC133"
If IsNull(Me.sfrmIBC133.Form!VER_NO) Then
pgNum.Caption = " 133"
Else
pgNum.Caption = "**133"
End If
Case Else
End Select
Next pgNum
Exit_Proc:
Exit Sub
Err_Proc:
MsgBox Err.Number & "-" & Err.Description
Resume Exit_Proc
End Sub