Link subform A on Tab 1 to subform B on Tab 2

[SirHammock]

Hello, I have 3 Forms.
My MainForm is linked to Subform A and now I want to link Subform A to Subform B;

My problem is now, that for design reasons I have to put subform A and B in Tabbs.

[jforbes]

There is an Easy way and a Better way to do this. For this example, there is a MainForm which is a SalesDashboard with SubFormA listing Companies for the Currently Selected SalesPerson. Then there is an additional SubForm, SubformB, a Datasheet of Projects that are limited to only show Projects for the Currently Selected Company.


Easy Way
Base SubFormB on a Query that has criteria that references SubFormA on the MainForm. This is an example of this type of Query:

SELECT Project.*, Project.CompanyID
FROM Project
WHERE (((Project.CompanyID)=[Forms]![SalesDashboard]![SubFormA]![Form]![CompanyID]));

Better Way
Base SubFormB on the Table of Projects instead of a Query. Then create code to update the Filter of SubFormB whenever a different record is selected on the SubFormA. The update code, should be located in the common area between the SubForms, aka the MainForm. But then the question becomes about how and when to fire the code that sets the Filter. This is the fun part. In SubFormA, there is this some code like this:

Option Compare Database
Option Explicit
Private nParent As String
 
Public Sub setParent(ByRef sParent As String)
    nParent = sParent
End Sub
Private Sub Form_Current()
    If Len(nParent) > 0 Then
        If isLoaded(nParent) Then
            Forms(nParent).subFormCurrent
        End If
    End If
End Sub

This code provides a way to let SubFormA know who it's Parent is, as well as call back to the Parent Form (MainForm) when it has changed records.
This uses the isLoaded() function that comes in handy and should be put in a Module:

Function isLoaded(ByRef sFormName As String) As Boolean
    ' Determines if a Form is loaded
    Dim i As Integer
 
    isLoaded = False
    For i = 0 To Forms.Count - 1
        If Forms(i).FormName = sFormName Then
            isLoaded = True
            Exit Function
        End If
    Next
End Function

With these two pieces in place, all that is left is to write the code for the MainForm:

Option Compare Database
Option Explicit
Private Sub Form_Load()
 
    ' Setup SubForms
    Call Me.SubFormA.Form.setParent(Me.Name)
    Me.SubFormB.Form.Filter = "1=0"
    Me.SubFormB.Form.FilterOn = True
 
End Sub
 
Public Sub subFormCurrent()
 
    ' Filter SubForm
    Dim sFilter As String
    sFilter = "CompanyID=" & Nz(Me.SubFormA.Form!CompanyID, "")
    Me.SubFormB.Form.Filter = sFilter 
    Me.SubFormB.Form.FilterOn = True
 
End Sub

Hopefully this code is accurate Syntax wise as it was knitted together from a couple different places.