How to filter bound subform from unbound main form?

[ittechguy]

I have a main form which is used for data entry and a sub form which displays data (all data in tblCustomer). The main form is unbound. In the header I have text boxes and combo boxes to add new records. In the footer, there are text boxes and combo boxes to filter the records displayed in the subform. For an example, if I only wanted to see records for one organization, I'd select the organization and hit the search button.

The form looks like this:





I'm having issue with the search form in the footer. I need to filter the subform based on values selected from the main form's footer.

Here is my VBA code:

Private Sub cmdSearch_Click()
Dim startStr As String
Dim strFilter As String
If Not IsNullOrEmpty(Me.cboSearchOfficeSym) Then
    startStr = IIf(strFilter = "", "", " AND ")
    strFilter = strFilter & startStr & " Forms!frmMainAddCustomer!frmSubAddCustomer.Form.OfficeSymPK = " & Me.cboSearchOfficeSym
End If
Call MsgBox(strFilter, vbOKOnly, "Debug")
If DCount("*", "qrySubAddCustomer", strFilter) = 0 Then
                MsgBox "No corresponding records to your search criteria." & vbCrLf & vbCrLf
Call cmdReset_Click
End If
Forms!frmMainAddCustomer!frmSubAddCustomer.Form.Filter = strFilter
Forms!frmMainAddCustomer!frmSubAddCustomer.Form.FilterOn = True
End Sub

I believe the issue I'm having is that I can't just use [OfficeSymFK] because that doesn't exist in the main form's recordsource. I need to either reference the subform, or the query for the subform. How do I do that?

[pbaldy]

When creating the filter string, you just use the field name, not the whole form reference. It doesn't matter if that field is in the main form since you're using the string in the subform's filter.

[ittechguy]

Thanks pbaldy! That was much easier than I thought.

[pbaldy]

Happy to help!