Tabbed Master-Detail Form

I've found that most Access applications have a single table that is the heart of the database design.

This table is easy to recognize in a well-designed database.  It's the table whose primary key appears as a foreign key more than any other.  In my custom Accounts Payable (AP) application, that table is Vendors.  The screenshot below is a simplified excerpt of the relationship diagram for the database.  

The table's central role is easy to see:

The Tabbed Master-Detail Form

This type of form consists of two parts:

• A Form Header section with the minimum amount of information needed to identify the record
• A Form Detail section with a single Tab Control, sized to fill the entire section

The Tab Control

The tab control has two kinds of tab pages:

• A Master Tab which exposes the fields from the central table itself
• Additional Detail Tabs which each represent a different one-to-many (or many-to-many) relationship with another table

The Master Tab Page

The master tab page generally dictates the initial size of the form.  

It's also the only tab that gets populated with data when the form initially loads.  This optimizes performance. The initial load requires reading as little as a single row of data from one table.  

Most (if not all) of the fields on the main table that users can edit should appear on this master tab.  Also, any processes that apply to the main table should also appear in this tab.  For example, there's a button on the screenshot below that launches the process to "Merge Another Vendor Into This One."

The Detail Tab Pages

The remaining tabs primarily represent related tables.

Optimizing Performance

Each of these additional tabs has a single subform control.  The Source Object property is set to an empty string when the form is saved.  In the Tab Control's OnChange event, the SourceObject property is populated the first time the user selects that tab.  

Private Sub TabCtl0_Change()
    Dim NewTabName As String
    NewTabName = Me.TabCtl0.Pages(Me.TabCtl0).Name
    
    Select Case NewTabName
    Case "VendorDetailPage", "NoticesPage"
        Me.RecordSelectors = True
    Case Else
        Me.RecordSelectors = False
    End Select

    '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
    '... etc. ...
    End Select
End Sub

I do not clear the SourceObject once it's been set.  At that point, I've already had to pay the price to get the records from the database.  In a way, these source objects act as the form's cache.  

Like any other cache, they improve performance so long as you have the memory available to keep them populated.  If you find yourself running into memory errors (e.g., "System resource exceeded"), you might consider clearing the SourceObject when the user navigates away from a tab.  I've never had to do this myself.

Simplifying Development

Don't try to size every subform to match the size of the master tab.

Instead, create each subform so that it is no larger than the master tab.  Be sure to design the tab so that the controls shrink and expand as the form is resized.  I wrote a custom resizing class that I use, but you can use the built in control anchoring properties to achieve the same result.

Then, when you add the subform control to the tab, resize it to fill the available space.  Set the subform control itself to shrink and expand in both the horizontal and vertical directions.  This way, as the form is resized, your subforms will fill whatever space is available.

Detail Tab Gallery

The screenshots below show what each of the tabs looks like:

For information on how I created the yellow highlight effect below, read How to Highlight the Current Record in a Continuous Form.

The subforms in the "Checks" and "Non-Checks" tabs do not expand or shrink horizontally because all of the fields contain fixed-width data.

Referenced articles