Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.
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:
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.
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:
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:
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.
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.
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. ' 2018-02-25. Gustav Brock, Cactus Data ApS, CPH. ' Version 1.1.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 ' 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 ' New record. Don't sync. wNew = True Exit For 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 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] rst.FindFirst Split(aControls(wIndex), "!")(ControlName.Key) & " = " & 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.
Implementation of the functionality is quite simple:
A working sample is for you to download: SyncSubforms 1.1.0.zip
The previous version is here for reference: SyncSubforms 1.0.0.zip