Null Disquisition

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 i

Pretty straightforward. Loop through each subform, see if there’s any data there, if so set the visibility of the corresponding page to True, 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