Apply Filter By Form to Report

[Stang02GT]

I have users who are using the Sort&Filter options on a from. Specifically they are using the Advanced>>Filter By Form option and then selecting a Vendor and applying that filter to the form.

After this Filter by form is applied they would like to be able to open the a report and have it only show the records they just filtered on.

We are using Access 2013, does anyone have suggestions on how we can accomplish this?

[NeoPa]

Use the .Filter property of the form in the call to open the report.

[Stang02GT]

So i could do something in the OnLoad Event of the report to reference the form filter?

or can i use the Filter On Load property on the report and set it to yes..but that leaves the question what do i reference in the Filter property on the report?

[Stang02GT]

Actually i figure it out.

I took the underlying query and changed it to have criteria on the vendor field that was looking at the form. So if they sort on the form and go to the report it will be based on what is currently on the report.

[NeoPa]

I assume somewhere you're using code to open the report. That would be in the format :

Call DoCmd.OpenReport(...)

One of the parameters is called WhereCondition. Simply use the form's .Filter property in there.

[NeoPa]

Stang02GT:
Actually i figure it out.
...

I would recommend you steer clear of such interactions. They make the database objects incestuous in as much as they can only be used together and not flexibly anywhere in your database.

No such issues if the underlying object (QueryDef in this case.) has a filter applied externally.

[Stang02GT]

This is what i am using to try and accomplish what you are saying trying to work through it now.

Private Sub Command1680_Click()
Dim fltr As String
    If Me.FilterOn Then fltr = Me.Filter
    DoCmd.OpenReport "Data Security Provision Review",acViewReport , , ".Filter"
End Sub

[Stang02GT]

This seems to be working well

Private Sub Command1680_Click()
    DoCmd.OpenReport "Landscape Data Security Provision Review", acViewReport, , Form.Filter
End Sub

Thanks for the direction on this Neo!

[NeoPa]

Hi Stang.

I'd do it slightly differently, using Me.Filter, but the results will be the same :

Private Sub Command1680_Click()
    Call DoCmd.OpenReport(ReportName:="Landscape Data Security Provision Review" _
                        , View:=acViewReport _
                        , WhereCondition:=Me.Filter)
End Sub

[Stang02GT]

This was working great. Now i just got hit with something out the the blue. If you filter by form at the bottom of your form it displays a "OR" tab allowing you to filter the form for a different set of data.

When you apply the filter it brings all the results back to one form but this button i added is not applying the "OR" results to the report.

Would i need to apply multiple filters in this case?

[NeoPa]

Can you say what the state of Me.Filter is at the time - as well as what filtering you've applied to the form?

I don't filter by form on an existing form but I still can't see why it would resolve to anything different from what's stored in Me.Filter. Strange.

[Stang02GT]

So this issue is actually with the report. The filtering is working as expected.

I needed to remove some code from the report that i had forgotten about that was causing me issues.

Working great now. Thanks again!

[NeoPa]

8-) Pleased to hear it's all going well now :-)