Microsoft Access - page of tabcontrol visible if subform has records

Asked By Heidi Boucher on 06-Dec-12 04:05 PM
Thank you for reading!  I am looking for help on hiding a tab page if there is no data in it's subform

I use this VBA code in main form on current to hide subforms with no records.
this particular main form has 4 subforms so I am using a tabcontrol to place the forms in. The 1st main form (not shown in code) always has records..the other 3 may or may not
the first one and the last one have table record sources
the middle one is a query

the VBA doesn't work on the main form that has the tab control?
no errors
Private Sub Form_Current()
With Me![sfViewSlmnNotes].Form
.Visible = (.RecordsetClone.RecordCount > 0)
With Me![RQTHISTORYsf].Form
.Visible = (.RecordsetClone.RecordCount > 0)
With Me![frmsfTedsLine2].Form
.Visible = (.RecordsetClone.RecordCount > 0)

End With
End With
End With

So what I like about the result of the subforms being hidden is that the user doesn't have to wonder about that information....what I don't like is how it is arranged on the mainform...that's why I am moving toward the tabcontrol.

I want the entire page hidden if there is no subform records - which means that Access will have to show page 0 (always show page 0) then page 2 on one instance; page 0 then page 1 on another; page 0 then page 1 and 3 on another; page 0 then nothing on another. I added the page 0 as well but that didn't change the result. All subfoms are visible.
I also tried this code in the mainform current event -
Me.Tabctl75.PageIndex(1).Visible = Me.[sfViewSlmnNotes].Form.Recordset.Recordcount >0)
Me.Tabctl75.PageIndex(2).Visible = Me.[RQTHISTORYsf].Form.Recordset.Recordcount >0)
Me.Tabctl75.PageIndex(3).Visible = Me.[frmsfTedLine2].Form.Recordset.Recordcount >0)
and with this I tested different examples - none hidden at all

First, I'm not sure tab control is what I should be using. Isn't the logic behind the tab control to stack info so that it's all visible? And that's why I would use it?  Not all of mine have info 
If it's possible to do - can I put the subforms 0"W 0" on top of each other?  and the ones with data they'll display one after the other neatly - not sure how to manage subform positioning that way

Second, I noticed some variation in the subforms when I clicked through them in design view that makes me think they didn't load on the page correctly. For example, the names aren't consistent.....my page 1 had the subform name as the name, but my pages 2 & 3 were assigned something by access. I was so confused I changed them to all read the same....but maybe I should try to relink the subforms on the pages? Is there a correct and incorrect way to load these that would cause the code to fail?
Pat Hartman replied to Heidi Boucher on 30-Dec-12 12:39 PM
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