Access 2007 VBA - Report filter doesn't work until report is reloaded

Question

I'm using VBA to dynamically load the content of a report, and depending on which report is selected from the control panel form I've built, the report's query might be filtered.

At the beginning of my Report_Open function, I have this:

Private Sub Report_Open(Cancel As Integer)
    Me.Filter = "VIP=True"
    Me.FilterOnLoad = True

Now, this was working when I started this project - I had commented out these lines, and when uncommenting them discovered that this doesn't work properly anymore. Instead of the filter being applied when the report is loaded, I have to reload the report for the filter to work - either by switching to print preview and switching back to report view, or by switching to design view and then back to report view (in design view, the selected filter does display in the properties pane).

I am loading the report using command buttons that allow the user to view, export to PDF, or print (opens in print preview). None of these commands cause the report to open with the filter applied - it has to be reloaded.

The commands I'm using to load the report are below for reference:

If (Action = "View") Then
    DoCmd.OpenReport "Test", acViewReport
ElseIf (Action = "PDF") Then
    DoCmd.OutputTo acOutputReport, "Test", acFormatPDF
ElseIf (Action = "Print") Then
    DoCmd.OpenReport "Test", acViewPreview
End If

Ok, I have no idea why Me.Filter and Me.FilterOnLoad don't work, since from everything I have seen on MSDN and elsewhere, it should work. That being said, I figured out that I can use DoCmd.ApplyFilter instead:

'Check if VIP - add filter if necessary
If (getGlobal(1) = True) Then
    DoCmd.ApplyFilter , "VIP = True"
End If

I'd still like to know why the other way was behaving so oddly, if anyone has any ideas...

Or, since you're doing it in the OnOpen event, you can set the Recordsource with the full WHERE clause. I would never muck about with filters in the fashion you've outlined -- if I'm writing code in the OnOpen event, I'm going to use the Recordsource rather than the filter properties. — David-W-Fenton

HansUp HansUp · Accepted Answer · 2011-07-11T16:12:29

As @David-W-Fenton suggested, use the WhereCondition with OpenReport instead of setting a Filter expression. Your WhereCondition can be the same string you were using for the Filter expression.

Also, if you give OpenReport an empty string as the WhereCondition, the effect is the same as no WhereCondition, so this (untested) code should work whether or not your getGlobal(1) returns True.

Dim strWhereCondition As String
Dim strReport As String
strReport = "Test"
If (getGlobal(1) = True) Then
    strWhereCondition = "VIP = True"
End If

Select Case Action
Case "View"
    DoCmd.OpenReport strReport, acViewReport, , strWhereCondition
Case "PDF"
    DoCmd.OpenReport strReport, acViewReport, , strWhereCondition
    DoCmd.OutputTo acOutputReport, , acFormatPDF
Case "Print"
    DoCmd.OpenReport strReport, acViewPreview, , strWhereCondition
End Select

Notice also that DoCmd.OutputTo, without an ObjectName argument, uses the active object ... which will be the "Test" report in this case.

Access 2007 VBA - Report filter doesn't work until report is reloaded

1 Answers