Auto-hide empty subform tab in Access
The title is rather representative of the conceit of this post. Suppose you have a form with several subforms that you arrange in a tabular manner (I think Access calls them pages). Now you probably don't want to show every tab unless there is some content within that tab worth seeing, so we write a routine which (upon loading the parent form) will quickly check the subforms' recordsets and see if they have any data in them. Problem: Automatically check for content within subforms arranged in a tabular manner, and do something as a result of content/no content. Solution: Well, keep reading. Basically, what we'll do is create a collection for the subforms and a collection for the pages. It is necessary to make sure these collections "line up" - meaning item 1 in the form collection corresponds with item 1 in the pages collection. VB doesn't really have a good data structure for this sort of thing - well, I suppose you could make a 2d collection. You implement it how you see fit. Code:
Dim mySubForms As New Collection
mySubForms.Add Me.sfrmCasing
mySubForms.Add Me.sfrmFormation
mySubForms.Add Me.sfrmGPLog
Dim myPages As New Collection
myPages.Add Me.pgCasing
myPages.Add Me.pgFormation
myPages.Add Me.pgGPLog
Dim rs As Recordset
Dim i As Integer
For i = 1 To mySubForms.Count
Set rs = mySubForms.Item(i).Form.RecordsetClone
If (rs.RecordCount = 0) Then
myPages.Item(i).Visible = False
Else
myPages.Item(i).Visible = True
End If
Next iTrue, otherwise set it to False. The same basic idea could be used to embolden a page title when there's data - instead of showing/hiding, you would change the font style of the page caption.
Using all this Access jargon is making me nauseous - I'm going to go write some Python.
-David
