Passing a form Filter to a report

[Carl Rapson]

I have a form and a report based on the same Query. The users can use Filter
by Form to filter the records displayed on the form, and I would like to
pass the same filter to the report so that the report lists the same
(filtered) records. Since I don't know which fields the user may have
filtered on, it seems like I would need to pass the form's Filter string to
the report to apply the same filter. But so far, I haven't been able to get
it to work - no filter is ever applied to the report, and I continue to see
all (unfiltered) records. I have tried setting the report's FilterOn
property to True, both in design view and in the Report_Open event, with no
success.

Is there some other trick to doing this? I know I can filter a report by
building a WHERE clause and passing that, but how can I do that when using
Filter By Form?

Carl Rapson

 

[Allen Browne]

Add a command button to your form to open the report with the same filter.
That should work, provided the report is not already open at that time.

Private Sub cmdPreview_Click()
Dim strWhere As String
If Me.FilterOn Then
strWhere = Me.Filter
End If
DoCmd.OpenReport "Report1", acViewPreview, , strWhere
End Sub

If your form contains combos where the bound column is hidden, and you
filter by those fields using the non-bound value, you will have to make sure
that the report's Record Source is a query that uses the same names for the
lookup tables as is used on your form's filter.

 

[Carl Rapson]

Thanks Allen, that did it. It didn't occur to me to put the Filter in the
Where clause. I've used the Where clause before, but never with a form
Filter.

Carl Rapson