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!