Passing a form Filter to a report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Carl Rapson - 18 Oct 2005 14:42 GMT

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

Reply to this Message

Allen Browne - 18 Oct 2005 17:55 GMT

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.

Signature

Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

>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
[quoted text clipped - 12 lines]
>
> Carl Rapson

Reply to this Message

Carl Rapson - 18 Oct 2005 20:00 GMT

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

> 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.
[quoted text clipped - 28 lines]
>>
>> Carl Rapson

Reply to this Message