Access Form - Datasheet is a master form with subforms

Question

This is embarrassing - sorry. I'd like to build an access form - imagine a rectangle with 4 quadrants. I want the upper-left quadrant to be a datasheet. Each other quadrant will also be a subform datasheet, with records linked by a common key.

I can do this easily if my main ("master") form is a split form. Then I just add the sub-forms into the part of the form that is not the datasheet, and link the records.

But I don't want to use a split form, because I don't want its datasheet to span all the way across the screen - I want that datasheet confined to its north-west quadrant.

The problem is, if I start with a blank form, and drag datasheets (or tables) on to it, and then try to Link Master and Child fields, get message "can't build a link between unbound forms".

How can I designate one datasheet to be the master form, I guess behavior is similar to a split form.

Thanks to whomever answers this before the question is battened down as not stackoverflow worthy :)

Answer 1

This function (full module) will allow you to do that:

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 story, documentation, and demo:

Synchronizing Multiple Subforms in Access