Me.Filter on Subreport does not work?

[Larry]

I have a subreport, twice, on a report to which I am trying to apply a
filter. The main query, on which the report is built, contains
different columns (as it's a Union query to get specific data) so I
can't just apply a filter to the entire report.

What I have is a form from which the report is submitted. This form may
be filtered by the user, and if it is, that filter is applied to the
reports that apply to this form. There are three reports, but the other
two do not have subreports, so apply the filter to these using
Me.Filter in the Report_Open event is no problem.

I have tried to do something similar to the subreport, but I am getting
an error "2101: The setting you entered isn't valid for this property."
and the code stops on the "Me.Filter = strFilter" line.

I have tried to make sure the filter is applied only once, but it's not
working on the first time through the code either. The code is below.

Anyone have any ideas?

BTW, it's Access 2003.

Thanks,
Larry

Private Sub Report_Open(Cancel As Integer)
Dim strFilter As String
Static blnInitialized As Boolean

If Not blnInitialized Then
blnInitialized = True
If IsLoaded("frmInterfaceUpdate") Then
With Forms!frmInterfaceUpdate
strFilter = Nz(.Filter, "")
End With
Me.Filter = strFilter
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End If
End Sub

 

[Allen Browne]

Hi Larry

I don't think you will get the Filter to work for subreports, so you are
looking for alternatives.

First choice would be to use the LinkMasterFields/LinkChildFields property
of the subreport if possible.

Next option would be to set criteria in the subreport's query so it reads
the limiting values from the form.

If neither of those will work, you could use the Click event of the button
on your form to write the SQL property of the QueryDef that the subreport is
based on before you OpenReport.

If both subreports are using the same query, and you want different filters,
and you can't use LinkMasterFields/LinkChildFields, you might have to create
separate subreports/queries.