Show tabs with data in Tab Control

[Curt Gough]

I have a form with a tab control and several tabs. On each tab I am using a
subform. Users will not always have data in the subforms of each tab. My
question is can I somehow show them which tabs have data, so that they are
not forced to click through all tabs to find where their data is?

I have tried using the OnChange event of the tab control to check for data
and then add an asterisk to the caption of the tab if there is data. But
that causes some nasty flicker when switching from tab to tab. I would like
to know if anyone has come up with a more elegent way of showing customizing
tab captions at runtime based on data on the tab.

Any help appreciated.

 

[Rob Parker]

Hi Curt,

You can use code such as this, in the Current event of the main form, to
only display the pages of the tab control which have data in the subform on
that page:

Me.YourTabControlName.Pages(0).Visible =
(Me.NameOfSubformControlOnPage0.Form.RecordsetClone.RecordCount <> 0)

Repeat for each page of your tab control which contains a subform.

If you are using this form to allow users to enter new data via each
subform, this will not work, since the tab/subform will not be available for
any record which does not already have existing child data for that subform.
In that case, use something like this:
If (Me.NameOfSubformControlOnPage0.Form.RecordsetClone.RecordCount = 0)
Then
Me.YourTabControlName.Pages(0).Caption = "CaptionText"
Else
Me.YourTabControlName.Pages(0).Caption = "CaptionText *"
End If

You might wish to pad the shorter CaptionText with trailing blanks to keep
the tabs the same size (and if you're using a proportional-spaced font,
you'll need to experiment with exctly how many blanks you need).

HTH,

Rob

 

[Curt Gough]

Thanks, Rob. I suspected I would need to use that approach. I want to allow
users to see all tabs. So, the second approach is the one I will have to
take. However, I tried adding my code to the OnCurrent event of the form and
got no response. When I add it to the OnChange event of the tab control the
tab captions update as soon as I add data using the subform. But I get some
major flicker when moving from tab to tab. I was hoping to avoid that as it
is hugely annoying.

Anyway, thanks for your help.

 

[Rob Parker]

Hi Curt,

Using the Current event of the main form will set the tab captions
corrrectly for when the form is opened, or when navigating between records
on the main form. If you need the captions to update when data is added to
a subform which previously had an empty recordset, then I'd suggest using
code in the AfterInsert event of each subform to change the tab caption of
its tab when data is inserted. You'll need slightly different syntax to
refer to the tab page, and you won't need the If construction; the following
should be sufficient:

Me.Parent.Form.YourTabControlName.Pages(0).Caption = "CaptionText *"

If you are allowing record deletion on the subform(s), you can use similar
code in the OnDelete event; but here you will need the If construction to
check whether you are deleting the last record from the subform:

Private Sub Form_Delete(Cancel As Integer)
If (Me.RecordsetClone.RecordCount = 1) Then
Me.Parent.Form.YourTabControlName.Pages(0).Caption = "CaptionText"
Else
Me.Parent.Form.YourTabControlName.Pages(0).Caption = "CaptionText *"
End If
End Sub

Note that the test condition is checking if there is 1 record before the
deletion, since the RecordsetClone.RecordCount has not yet changed to
reflect the deletion.

Using this code, together with the previous code in the main form's Current
event, changes the captions with no flickering (on my system - Access 2002
running under Windows XP Pro).

Again, HTH,

Rob

 

[Curt Gough]

Thanks, Rob. It makes sense to put the caption update code in the
AfterInsert event of the subform. That part works great. I appreciate the
help.

I'm still having some troubles with the OnCurrent event of the form, though,
and what code to put there. I have tried using If... Then statements with no
luck. My desire is to have the captions on the tabs display the asterisk if
the subform contains records. Your code from your first post was great for
displaying tabs with data while hiding tabs without. I'm not exactly sure
what I can use to change the caption display, though, without using an If...
Then.

 

[Rob Parker]

Hi Curt,

I gave such code in my first post, towards the end of the message. Here's
the relevant portion of that message again:

<quote>
If you are using this form to allow users to enter new data via each
subform, this will not work, since the tab/subform will not be available
for any record which does not already have existing child data for that
subform. In that case, use something like this:
If (Me.NameOfSubformControlOnPage0.Form.RecordsetClone.RecordCount = 0)
Then
Me.YourTabControlName.Pages(0).Caption = "CaptionText"
Else
Me.YourTabControlName.Pages(0).Caption = "CaptionText *"
End If

You might wish to pad the shorter CaptionText with trailing blanks to
keep the tabs the same size (and if you're using a proportional-spaced
font, you'll need to experiment with exctly how many blanks you need).
<end quote>

Again, HTH,

Rob