• There are two problems here.

    1. When a form has subforms, every of there subforms are initialized before the initialization sequence of the main (parent) form (i.e. the Form_Open --> Form_Load --> Form_Current sequence of events for each subform occurs before the Form_Open --> Form_Load --> Form_Current sequence of events for the main (parent) form). However, you cannot control the order of initialization of the subforms. This means that, in a subform, you cannot reference a property of another subform that could not be loaded yet.

    2. When a form is open as a subform in a main (parent) form, it is not added to the Application.Forms collection. This means that you cannot reference it using the syntax Forms!Formname.

    Here's a solution:

    Note: In this example, the main (parent) form is Frm_Orders, the subform (SF1) that controls the RecordSource property of the other subform (SF2) is SF_OrderLines and the second subform (SF2) is SF_OrderLineItems. The SubForm/SubReport controls of both subforms are named Child_OrderLines for SF1 (SourceObject = SF_OrderLines) and Child_OrderLineItems for SF2 (SourceObject = SF_OrderLineItems). The relationships are: PK_Tbl_Orders --> FK_Tbl_Orders and PK_Tbl_OrderLines --> FK_Tbl_OrderLines

    A. Add this to the class module of the main (parent) form:

    Option Compare Database

    Option Explicit

    Private m_booReady As Boolean

    Private m_strSQL As String

    Public Property Get Ready() As Boolean

    Ready = m_booReady

    End Property

    Public Property Let SQL(ByVal strSQL As String)

    m_strSQL = strSQL

    End Property

    Private Sub Form_Open(Cancel As Integer)

    m_booReady = True

    Me.Child_OrderLineItems.Form.RecordSource = m_strSQL

    End Sub

    B. Add this to the class module of the subform (SF1) that controls the RecordSource of the other subform (SF2):

    Option Compare Database

    Option Explicit

    Private Sub Form_Current()

    Dim strSQL As String

    strSQL = "SELECT * FROM Tbl_OrderLineItems WHERE FK_Tbl_OrderLines = " & Me.PK_Tbl_OrderLines

    If Me.Parent.Ready = True Then

    Me.Parent.Controls("Child_OrderLineItems").Form.RecordSource = strSQL

    Else

    Me.Parent.SQL = strSQL

    End If

    End Sub

    How it works:

    - When the Form_Current event of SF1 occurs for the fist time (i.e. when SF1 is initialized) we don't know whether SF2 is already loaded or not. However we know (from 1 here above) that the Form_Current did not occured yet. The Ready property of the parent form is False at that time and SF1 will store the SQL string in a member variable (m_strSQL) of its parent through the SQL property of this parent.

    - When the Form_Open event occurs form the parent form, we know that the sequence of events Form_Open --> Form_Load --> Form_Current already occured for both subforms (SF1 and SF2). This means that SF1 already stored the SQL string in the member variable of the parent. This SQL string is passed to the RecordSource property of SF2 and the Ready property of the parent is set to True (through its m_booReady member variable).

    - When subsequents Form_Current events occurs for SF1, the Ready property of its parent is True and SF1 can safely send the SQL string to SF2. However, as SF2 is not referenced in the Forms collection of the Application object (see 2 here above), SF1 must get a reference to SF2 though its parent, hence the expression:

    Me.Parent.Controls("Child_OrderLineItems").Form.RecordSource = strSQL

    Have a nice day!