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:
=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:
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.
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.
' 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.
Implementation of the functionality is quite simple:
A working sample is for you to download:
The previous versions are here for reference:
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.
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (11)
Author
Commented:Version 1.1.0 has been published.
This version also will synchronize all subforms after adding or deleting records in the subforms.
Commented:
Best regards,
Brandon
Author
Commented:Commented:
Best regards,
Siew Kheng
Author
Commented:View More