Synchronizing Multiple Subforms in Access

Background

If you need to edit a table with many fields, the form may be far too wide to be displayed in full on a normal screen. You will then have to limit the width of the form, leaving the user to scroll the fields horizontally. This is bad for at least two reasons: the user spends a lot of time scrolling, and it is hard to locate individual fields.

A common solution is to split the table view in tabs where you organize related fields in groups. Here is an example where product info has been split in four groups, each having its own tab with a subform on a tab control:

Problem

Splitting the fields across several subforms, however, introduces a new issue. When the user moves to another record on one subform, the others don't follow. This means that the user must keep track of this and manually select the correct current record when tabbing to another tab. Users won't accept this, because it is far too easy to get wrong, then entering data on the wrong records.

Solution

One way to handle this, is to add a calculated field on the main form that calls a function that forces the current records on the non-current subform to follow the ID of the current record on the current subform.

On our demo form, this is the textbox txtSyncSubforms. It has this control source:

=SyncSubforms([PPAsubVendor]![ID1],[PPAsubFreight]![ID1],[subPPAsubPurchase]![ID1],[PPAsubStock]![ID1])

Note that in localized versions of Access, the parameter separator may be semicolon and not comma as shown.

Also note, that one subform control is named differently than its SourceObject  - the subform it hosts. In other words: The function and the synchronizing is not dependent on the name of the subform objects.

The trick is that whenever the main form updates, the function is called, and this happens when the user selects another tab. The function keeps track of the last selected ID (which is common for all subforms - remember that they all have the same table as source). When the current ID changes, the function loops through all subform controls and adjusts the current record to the selected ID of the current subform. To illustrate this, and only for this purpose, a second textbox is added to the main form with this control source:

=[PPAsubVendor]![ID1] & " : " & [PPAsubFreight]![ID1] & " : " & [subPPAsubPurchase]![ID1] & " : " & [PPAsubStock]![ID1]

Whenever you change record, within a second all IDs are lined up. Thus, as shown on these screenshots, the other subforms (first, second, and fourth) are synchronized to the third. But you can change any subform; the others will be synchronized in a second as you can see on the first subform:

and the fourth:

Note that scrolling is limited to bring the selected record into the view, not to position it vertically exactly as the leading subform.

As you will notice from the screenshots, the native record counter doesn't catch up. For some reason it stays on number of that last manually selected record of the subform.

Also add and delete records

The first version posted in 2015 didn't synchronize if records were added or deleted; you had to do a manual update of the individual subforms.

This ability has been included in version 1.1.0. Now - if the recordset and the subform allow - you can add or delete records in one subform, and the other subforms will requery and synchronize automatically. The code has, of course, been expanded a bit to accomplish this feature.

Only code in the main form

Note that the subforms contain no code related to the synchronization; all code remains in the main form. Further, the code is quite simple and can handle any count of subforms without modification:

Option Compare Database
Option Explicit

' Automatic synchronizing of multiple subforms.
' 2019-01-05. Gustav Brock, Cactus Data ApS, CPH.
' Version 1.2.0
' License: MIT.

    ' Index for Split to separate the name of the subform control from
    ' the name of the control with the key.
    '   [subControlAny]![ID]
    ' will be split into:
    '   [subControlAny]
    ' and:
    '   [ID]
    Enum ControlName
    SubForm = 0
    Key = 1
    End Enum

Private Function SyncSubforms(ParamArray sControls() As Variant) As Variant

' Array sControls() holds the values of the key controls on the subform controls
' to be held in sync.

    ' Name of visible textbox on main form bound to this function.
    Const cControl  As String = "txtSyncSubforms"

    ' Static to store the value of the key of the last synced record.
    Static wLastID  As Variant

Dim rst         As DAO.Recordset
Dim wSubform    As Form

' Array to hold the names of the subform controls and key controls.
Dim aControls() As String

Dim bmk         As Variant
Dim wNew        As Boolean
Dim wThisID     As Variant
Dim wIndex      As Integer
Dim wItem       As Integer
Dim wCount      As Long
Dim wFieldName  As String

' If any key value is Null, we have moved to a new record.
' No syncing shall take place.
For wIndex = LBound(sControls()) To UBound(sControls())
    wThisID = sControls(wIndex).Value
    If IsNull(wThisID) Then
        If sControls(wItem).Parent.Name = Me.ActiveControl.SourceObject Then
            ' New record. Don't sync.
            wNew = True
            Exit For
        End If
     ElseIf IsNull(wLastID) Or Me.ActiveControl.Form.NewRecord Then
        ' Initial opening of form, or new record has been created.
        ' Set wLastID to the value of the current key of the first subform
        ' or to the key of the new record.
        wLastID = wThisID
        ' Stop further processing.
        wNew = True
        Exit For
ElseIf IsEmpty(wThisID) Then
        ' Record has been deleted.
        ' Pull the ID from the active subform.
        For wItem = LBound(sControls) To UBound(sControls)
          If sControls(wItem).Parent.Name = Me.ActiveControl.SourceObject Then
              wThisID = Me.ActiveControl(sControls(wItem).Name).Value
' Store as the current key.
wLastID = wThisID
Exit For
            End If
          Next
Exit For
        ElseIf wThisID <> wLastID Then
            ' This key is the new value to sync the other subforms to.
            ' Store the current key.
            wLastID = wThisID
            Exit For
          End If
        Next

If wNew = True Then
    ' New record or initial opening. Do nothing.
Else
    ' ControlSource of cControl will read like:
    '   =SyncSubforms([subControlFirst]![ID],[subControlSecond]![ID], .., [subControlLast]![ID])
    '
    ' Build array of the names of the subform controls with the key controls:
    '   [subControlFirst]![ID]
    '   [subControlSecond]![ID]
    '   ...
    '   [subControlAny]![ID]
    '   ...
    '   [subControlLast]![ID]
    ' by extracting arg names between "(" and ")".
    aControls = Split(Replace(Split(Me(cControl).ControlSource, "(")(1), ")", ""), ",")

    ' Get current record count as it will change after an append or delete in one of the subforms.
    For wIndex = LBound(aControls()) To UBound(aControls())
      If Me(Split(aControls(wIndex), "!")(ControlName.SubForm)).Name = Me.ActiveControl.Name Then
      Set wSubform = Me(Split(aControls(wIndex), "!")(ControlName.SubForm)).Form
      wCount = wSubform.RecordsetClone.RecordCount
      Exit For
    End If
Next

' Loop to locate and sync those subforms that haven't changed.
For wIndex = LBound(aControls()) To UBound(aControls())
    ' Extract name of subform control using Split:
    '   [subControlAny]
    Set wSubform = Me(Split(aControls(wIndex), "!")(ControlName.SubForm)).Form
    If wCount <> wSubform.RecordsetClone.RecordCount Then
       ' A record has been added or deleted in another subform.
       wSubform.Requery
    End If
    If IsNull(sControls(wIndex)) Or sControls(wIndex) <> wThisID Then
      ' This subform is to be synced.
      Set rst = wSubform.RecordsetClone
      ' Find record for current key.
      ' Extract name of control on subform using Split:
      '   [ID]
      ' Then use ControlSource to get the name of the field to search.
      wFieldName = wSubform(Split(aControls(wIndex), "!")(ControlName.Key)).ControlSource
      ' Wrap the fieldname in brackets in case it should contain spaces or special characters.
      If Left(wFieldName, 1) <> "[" Then
        wFieldName = "[" & wFieldName & "]"
     End If
     rst.FindFirst wFieldName & " = " & wThisID
     If Not rst.NoMatch Then
        bmk = rst.Bookmark
        wSubform.Bookmark = bmk
     End If
     rst.Close
    End If
Next

End If

Set rst = Nothing
Set wSubform = Nothing

SyncSubforms = wLastID

End Function

Please study the in-line comments for the subtle details.

The code has been tested in Access 2016, but should work in any version from Access 2003.

Guide

Implementation of the functionality is quite simple:

1. Copy and paste all code from the CBF module of form PPA to the CBF module of the new form.
2. Copy and paste the TextBox txtSyncSubforms from the form PPA to the new form.
   
   On the new form:
    
3. Adjust ControlSource of txtSyncSubforms to contain the names of the ID subform controls of the subforms to be held in sync.
4. TextBox txtSyncSubforms cannot have property Visible = False, but its:
    - Width can be reduced to near zero
    - ForeColor can be set to be equal to its BackColor
    - BorderStyle can be set to Transparent

Sample 

A working sample is for you to download: 

SyncSubforms 1.2.0.zip

The previous versions are here for reference:

SyncSubforms 1.1.0.zip

SyncSubforms 1.0.0.zip

I hope you found this article useful. You are encouraged to ask questions, report any bugs or make any other comments about it below.

Note: If you need further "Support" about this topic, please consider using the Ask a Question feature of Experts Exchange. I monitor questions asked and would be pleased to provide any additional support required in questions asked in this manner, along with other EE experts.

Please do not forget to press the "Thumbs Up" button if you think this article was helpful and valuable for EE members.