Report.FilterOn is not working (1 Viewer)

[tvanstiphout]

In Report_Open I have:
Me.Filter = "EmployeeID = 1"
Me.FilterOn = True

For example, you can paste this in Northwind Dev edition rptSalesByEmployee. Set a breakpoint on the next line. Observe that FilterOn is still False. Why is that? And the report is not filtered.
I know there are other ways to restrict the report to my Where-condition, but I am interested in exploring THIS version. From all I can tell, it should work. Chatty thinks so too.

 

[tvanstiphout]

I found a workaround: in frmReports where the report is opened, add this:
130 DoCmd.OpenReport TempVars!ReportName, TempVars!ReportView, "", "1=1", acNormal

I wonder if this behavior is a bug, or by design.

 

[MajP]

The reason this does not work is because you are opening the form in Report View and setting the filterOn in the on open event. I do not fully understand why this is, but if you set the filteron in the open event that event it too early to "stick".

Many work arounds but the simplest is just move your filter code to the on load event.
1. If you open your report now in the print preview you will see it work. So report view and print view have different timing.
2. If you move your code to the on load event instead it will work for both views.
in fact you do not have to move all your code only the filterOn code
3. You can change the recordsource in the on open event.

 

[Imb]

In Report_Open I have:
Me.Filter = "EmployeeID = 1"
Me.FilterOn = True

What happens if you change the order of Me.Filter and Me.FilterOn?

Unfortunately I can not test it myself, because I never use Access Reports.
Nevertheless, sometimes the order of instructions is important. As an example, to select a part of a the text in a control, the setting of SelStart and SelLength seems order-dependent.

 

[MajP]

No difference. The issue is that the filteron must be moved to the onload. The filter will be applied in the on open but for some reason it is to early to set filter on in the onopen and must be set later in the onload.

 

[tvanstiphout]

Interesting though that setting a WhereCondition during DoCmd.OpenReport allows the code to be in Report_Open and work just fine.

 

[arnelgp]

you can call SetFilter on the Open event of your report and it will filter the report.

Private Sub Report_Open(Cancel As Integer)
DoCmd.SetFilter , "EmployeeID = 1"
End Sub

 

[CJ_London]

not tested, but if you want to set the filter in the open event, try setting the filteronload property to true (shouldn't need filteron=true). At the time of the open event, there is no data to filter, that only becomes available in the load event

 

[DickyP]

The received wisdom is not setting the filter in the report itself but in the DoCd,OpenReport statement in the WhereCondition or , if a stored filter exists, the FilterName parameter.

OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

As written above the filter is hard coded (to be avoided), whereas the code leading to the DoCmd.OpenReport statement should enable the program to establish the filter.

 

[MajP]

you can call SetFilter on the Open event of your report and it will filter the report.

Yes, but only in print Preview and not in FormView

You can set the filter in onopen, but you need to turn the filter on in the onload event. Or simply do everything in the onload and it works in formview and print preview.

As written above the filter is hard coded (to be avoided), whereas the code leading to the DoCmd.OpenReport statement should enable the program to establish the filter.

I agree not sure why you would want to set the filter in the load event of a report, except if you were pushing this in openargs.

See the below demo and open it in Form view and then in Print view. It only works in print preview since the filteron is in the open event. Move the filteron to the load event and it will work.

 

[tvanstiphout]

not tested, but if you want to set the filter in the open event, try setting the filteronload property to true (shouldn't need filteron=true). At the time of the open event, there is no data to filter, that only becomes available in the load event

I had tested that, and it did not make a difference.

 

[tvanstiphout]

The received wisdom is not setting the filter in the report itself but in the DoCd,OpenReport statement in the WhereCondition or , if a stored filter exists, the FilterName parameter.

OpenReport (ReportName, View, FilterName, WhereCondition, WindowMode, OpenArgs)

As written above the filter is hard coded (to be avoided), whereas the code leading to the DoCmd.OpenReport statement should enable the program to establish the filter.

I know one can open a criteria form before opening the report, and pass in WhereCondition that way. As I wrote in #1, "I am interested in exploring THIS version".
In THIS version, and as a general practice, I open a modal criteria form in Report_Open. That keeps the two together and I find it more self-contained and elegant. I learned the technique years ago from Armen Stein, a longtime Access MVP from Seattle.

 

[Imb]

I know one can open a criteria form before opening the report, and pass in WhereCondition that way. As I wrote in #1, "I am interested in exploring THIS version".
In THIS version, and as a general practice, I open a modal criteria form in Report_Open. That keeps the two together and I find it more self-contained and elegant. I learned the technique years ago from Armen Stein, a longtime Access MVP from Seattle.

In this case you could modify the RecordSource of the report to include the filter.