Lazy Loading a Tabbed Form in Microsoft Access

Many of my Access applications include detail forms for my "main" tables.  By "main" tables, I'm referring to those tables that sit on the "one" side of several one-to-many relationships.

For example, consider the Vendor table in an Accounts Payable application.  That table could be linked to:

• One or more expense Accounts
• One or more Invoices
• One or more payments (both Checks and Non-Checks)
• One or more invoice Templates
• One or more Scheduled (a.k.a., recurring) invoices
• One or more Contracts
• Etc.

Below is a screenshot from an Accounts Payable application we wrote for one of our bank clients.  Notice that the Vendor Detail form for AFLAC includes a primary tab control with ten tabs.

On most tabs, there is a single subform control with a continuous form as its Source Object.  The selected "Scheduled" tab in the screenshot above is a good example.

The Standard (Inefficient) Approach

The typical approach when adding subforms to a main form is to set the Source Object and linked fields at design time.  But that means when the form loads, every single subform is populated with data.  

That approach is inefficient.  Why populate the subforms on the hidden tabs?  That leads to forms that are slow to open.

The Lazy Loading Approach

It's normally quite fast to load a single subform.  So, to improve the performance of these types of forms, it's best to wait until the very last moment before populating a subform.  Think of it as just-in-time population.

How does one actually do that, though?  I follow these steps:

1. Design the form as normal
2. In design view, set the Source Object, Link Master Fields, and Link Child Fields properties for each subform
3. Clear out the Source Object property for all subform controls
4. Add code to the tab control's After_Update event to set the SourceObject property for the subform on the currently selected tab

Here's what the Data tab of the Property Sheet looks like for a typical subform after completing Step 3:

And here is some sample code for Step 4:

Private Sub TabCtl0_Change()
    Dim NewTabName As String
    NewTabName = Me.TabCtl0.Pages(Me.TabCtl0).Name

    'Only load subforms when they are first used to improve performance
    Select Case NewTabName
    
    Case "AccountsPage"
        If Len(Me.Vendor_AcctsSF.SourceObject) = 0 Then
            Me.Vendor_AcctsSF.SourceObject = "Vendor_AcctsSF"
        End If
    
    Case "InvoicesPage"
        If Len(Me.Vendor_InvoicesSF.SourceObject) = 0 Then
            Me.Vendor_InvoicesSF.SourceObject = "Vendor_InvoicesSF"
        End If
        
    'Continue adding Case statements for all subforms...
    
    End Select

End Sub

Points of Emphasis

I don't bother clearing the Source Object property when I move off of a tab.  Most of the inefficiency comes from the initial load of a subform's data.  Since we've already paid that price for each visited tab, there's no good reason to throw that data away.  After all, the user could always come back to a previously selected tab.

I don't bother clearing the Source Object properties in the Form's Load or Open events.  If I recall correctly, there is no way to short-circuit the subform loading to prevent Access from retrieving those records at runtime (I could be wrong about that).  Instead, I have a cleanup routine that I run prior to deployment that checks to make sure the Source Object properties remain empty.  In other words, Step 3 must be done in form Design View and not in VBA.

I only set the SourceObject property for a subform control if it is currently unset (If Len...). Any time you set the SourceObject property–even if just to the existing value of the property–Access will force a reload of the subform.  This unnecessary data retrieval slows down the form and returns the cursor to the first record in the subform.

Image by Holger Stephan from Pixabay