It is possible to set a filter on a subform from the parent form before loading subform data

Question

I have frmParentForm with several controls used to create a filter for frmSubForm.

In frmParentForm_Load, I do (simplified example):

Me.sbfInvoice_List.Form.filter = "[created_on] >= #" & Me.RecentOrderDateCutoff & "#"
Me.sbfInvoice_List.Form.FilterOn = True

The problem is that upon initial loading, it seems that the subformat is loaded first, so the whole table is loaded.

Is there a way (perhaps, in another case) to properly configure the subform filter from the parent form so that it is applied before the slave system loads its source data? (A subform can exist on its own or as a child of many different parental forms (sometimes filtered, sometimes not), so I would prefer not to use some complex hacks in the most subordinate form to accomplish this.)

Answer 1

Because the subform is loaded to the parent form, the parent form cannot set the subform filter before the initial subform is loaded.

( , , , ), , , .

Private Sub Form_Open(Cancel As Integer)
    Dim strParent As String
    Dim strMsg As String

On Error GoTo ErrorHandler

    strParent = Me.Parent.Name
    Select Case strParent
    Case "frmYourParentForm"
        'set filter to only records from today '
        Me.Filter = "[created_on] >= #" & Date() & "#"
        Me.FilterOn = True
    Case "frmSomeOtherParent"
        'do something else '
    End Select

ExitHere:
    On Error GoTo 0
    Exit Sub

ErrorHandler:
    Select Case Err.Number
    Case 2452
        'The expression you entered has an invalid reference to '
        'the Parent property. '
        Resume Next
    Case Else
        strMsg = "Error " & Err.Number & " (" & Err.Description _
            & ") in procedure Form_Open"
        MsgBox strMsg
    End Select
    GoTo ExitHere
End Sub

. , , , .

Private Sub Form_Load()
    Debug.Print Me.Name & ": Form_Load"
End Sub

Open Load .

fsubChild: Form_Open
fsubChild: Form_Load
frmParent: Form_Open
frmParent: Form_Load