Filter Subreports from Form OnClick with VBA

[gumby]

I have a report that has two subreports. Two different record sets,
however each record set has a similar field. [Agency] I want to filter
both subreports by a combo box on my form.

rpt_HRC_HR_Monthly Report_2008_Final_Org_Filtered_PAY is the report
and qryNonPayAffecting_Measures_CBO_Build_Cases subreport_Org is one
of the subreports. Of course I get an error with this since the
subreport is not open when it tries to filter. Any ideas on how to do
this?

Dim strFilter As String
Dim stDocName As String
Dim strSpecName As String

stDocName = "rpt_HRC_HR_Monthly
Report_2008_Final_Org_Filtered_PAY"
DoCmd.OpenReport stDocName, acPreview

If IsNull(Me.cboAgency.Value) Then
strSpecName = "Like '*'"
Else
strSpecName = "='" & Me.cboAgency.Value & "'"
End If
strFilter = "[Agency] " & strSpecName & ""

' Apply the filter and switch it on
With Reports![qryNonPayAffecting_Measures_CBO_Build_Cases
subreport_Org]
.Filter = strFilter
.FilterOn = True
End With


David

 

[Duane Hookom]

I expect it is too late to set the filter once the report has been opened. I
would either hard-code a reference to cboAgency in the subreport's record
source or change the SQL property of the subreport's record source query.