I use subform datasheet mode to display table. The table will be very wide if I use one subform only. I will divide fields to couple groups. Each group will be displayed by one subform and each subform will be in a tab of tab control. How can I sychonize each subform on screen rows? For example, user scroll subform A and row 12~23 are on screen now while row 15 is selectd. Can I set other subform on screen to row 12~23 and row 15 is selected also? It means all subform display rows and selected row are synchronized.
On the main form, place a textbox, say, txtSyncSubforms.
Apply this control source to it:
=SyncSubforms([subControlFirst]![ID],[subControlSecond]![ID], .., [subControlLast]![ID])
replacing subControlxxxx and ID with the actual names of your subform controls and the ID which, of course, must be unique.
Behind the form add this code:
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
Full documentation and demo for download is here: Synchronizing Multiple Subforms in Access