Filter Subreports from Form OnClick with VBA



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

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

stDocName = "rpt_HRC_HR_Monthly
DoCmd.OpenReport stDocName, acPreview

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

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


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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question