Filtering a report used with OutPutTo Method

[Guest]

I have a report that users can select multiple filtering conditions. I use
the Where Condition arguement of the OpenReport method to either preview or
print the report. It is also a requirement I allow the user to export the
report to Excel. I have no problem using the OutputTo method to do this;
however, there is no Where Condition argument for the OutputTo method.
What would be the best way to output the report to Excel using the same
Where Condition string I use for print and preview?

 

[Allen Browne]

Create a public string variable.
Set it to whatever would have been in the WhereCondition.
Apply it as the Filter of the report in its Open event.

1. In the General Declarations section (top) of a standard module:
Public gstrReportFilter As String

2. In the Open event procedure of the report:
Private Sub Report_Open(Cancel As Integer)
If gstrReportFilter <> vbNullString Then
Me.Filter = gstrReportFilter
Me.FilterOn = True
gstrReportFilter = vbNullString
End If
End Sub

3. In the code that outputs the report:
gstrReportFilter = "ID = 99"
DoCmd.OutputTo ...

 

[Guest]

Thanks for the response, Allen. A couple of follow up questions.
First, I try to avoid Global variables when I can. As all the activity
occurs in one form, would it be just as well to create a propertry for the
form or a hidden text box for the for and reference that from the report?

Also, I guess, for consistancy's sake, I should use the same approach for my
preview and print options and drop the where condition in the OpenReport
method.

 

[Allen Browne]

Yes, it's a good idea to avoid global variables where possible, because of
the capacity for one part of the software to interfere with another
unintentionally.

In this case that's unlikely to be an issue because:
a) If you use the "g" prefix consistently for globals only, then no
programmer on your team should create a local variable with the same name,
so that issue should not arise.
b) The lifetime you need the global to be correct is only the nano-second
between when you assign the value to the string, and when the Report_Open
fires, so the chance of another process interferring with the value in that
timeframe is negligible.
c) If this is a multi-user environment, every user should have their own
copy of the front end, so each has their own variable and will not interfere
with each other's.

I don't think you need use the same approach with OpenReport. The global
string is just a fudge to work around the issue that OutputTo does not have
a WhereCondition. OpenReport does, so I see no reason to use the fudge.

What I personally do is use my own function to call OpenReport. This
function:
a) defaults to preview instead of print;
b) returns True if the report opened;
c) suppresses error 2501;
d) closes the report if it is already open (to avoid the issue where the
filtering is not set if the report is already open.)
e) takes an argument that is a description of the WhereCondition in English
and passes it to the report so that any printout contains a description of
the criteria in a meaningful way.
The description is passed in OpenArgs in A2002 or 2003, or in a public
string in earlier versions.

 

[Guest]

Good points.
The problem I see using the Where Condition for print and preview and the
Open event of the form for Output to is that it is the same report. The Open
event would not know that the filtering had already been set by the Where
condition, or would it?

 

[Allen Browne]

Report_Open doesn't know if it was called by OutputTo or OpenReport. But the
global string is reset to a null string in OpenReport anyway, so it will
only contain a value for a nanosecond.

If the report is opened with a WhereCondition, the global string will be
zero-length, so Report_Open takes no action in regard to the filter, and so
OpenReport with a WhereCondition still works just as if there were not code
in the report.

Either way, there is a problem with knowing if a filter is applied to a
report or not. Access fails to set the report's FilterOn property reliably.
It's another one of those bugs that has been there for years and Microsoft
never bothered to fix it.