Passing a form Filter to a report

  • Thread starter Thread starter Carl Rapson
  • Start date Start date
C

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
 
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.
 
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
 
Back
Top