<

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x

Synchronizing Multiple Subforms in Access

Published on
13,402 Points
4,802 Views
6 Endorsements
Last Modified:
Approved
Tab between several subforms keeping these in sync when browsing records, and even when adding and deleting records.

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:


SyncSub1.PNG


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:

SyncSub2.PNGand the fourth:

SyncSub4.PNGNote 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.
' 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.


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.1.0.zip

The previous version is here for reference: SyncSubforms 1.0.0.zip

6
11 Comments

Expert Comment

by:jalongoria
@Gustav Amazing article! I don't entirely understand the solution yet, but getting there as this solves a specific concern I've had with creating forms and trying to not overwhelm the user with what is a substantial amount of data. Thanks for the knowledge share.
0
LVL 56

Author Comment

by:Gustav Brock
Thanks!

/gustav
0
LVL 7

Expert Comment

by:Doug
Playing off of Gustav's solution, a less complex method would be to merely have one subform and have buttons that hide the columns that are of no interest and unhide the columns of interest.  So, one button for each category (i.e., same names as Gustav's tabs) that both hides and uhides datasheet columns.
0
IT Pros Agree: AI and Machine Learning Key

We’d all like to think our company’s data is well protected, but when you ask IT professionals they admit the data probably is not as safe as it could be.

Expert Comment

by:Kennyson Katowa
I have had a  similar problem like one for synchronizing multiple subforms. In my case, I need to display an image to main form from subforms on one location that corresponds to each tab control as I click on the particular tab control. Please help me on how I can do this.
0
LVL 56

Author Comment

by:Gustav Brock
I'm not sure I understand what you try to do but doubt that it relates very close to this article, so I will suggest that you post a question in the Microsoft Access topic area perhaps including a small sample database.

/gustav
0

Expert Comment

by:Brandon Kivler
This is great - exactly what I needed and am so glad I found your article.  Question - is it possible to set this up to work even when a new record is added?  I've tried using requery but that will put me back to the first id for that record.  Any help would be much appreciated!
1
LVL 56

Author Comment

by:Gustav Brock
Brandon et al

Version 1.1.0 has been published.
This version also will synchronize all subforms after adding or deleting records in the subforms.
1

Expert Comment

by:Brandon Kivler
Awesome Gustav - just checked out the new version, and it works great!  I will definitely utilize this functionality.

Best regards,
Brandon
0
LVL 56

Author Comment

by:Gustav Brock
Thanks Brandon, good to know. You may click a "thumbs up" (top-left).
1

Expert Comment

by:SK Quah
Thank you very much for your sharing, Gustav - by following your precise instructions here, I was able to synchronize the scrolling of two sub-forms. My boss is very happy!  Without your codes, I will not be able to do it.

Best regards,
Siew Kheng
1
LVL 56

Author Comment

by:Gustav Brock
Thanks Siew, I'm glad to hear that.
0

Featured Post

Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
How can you see what you are working on when you want to see it while you to save a copy? Add a "Save As" icon to the Quick Access Toolbar, or QAT. That way, when you save a copy of a query, form, report, or other object you are modifying, you…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month