I open a report using docmd.openReport and provide a filter. Inside the report's Report_Open event I optionally want to add an additional condition to the filter.
What amazes me is that the "FilterOn" property is set to true, but the "Filter" property is an empty string. This seems wrong to me; either the filter is not set yet at all, or both properties should be set.
When continued the report opens correctly using the filter and when switching back to design mode the filter property has been set. If I then go back to preview mode, the filter property is set in Report_Open. So it only goes wrong if the report is opened for the first time.
Applying a filter in the Report_Open event isn't a good idea. It'll cause the data to be loaded yet again which will mean a slower report. In addition it will be harder to track the records that should be returned. Never mind about possibly causing other unwanted affects.
Create a form dialogue box with some combo boxes which allows users to specify the criteria and then either:
1. Build a filter using combo box values
2. Have those combo box values be referenced in a parametized query.
3. Rewrite the SQL property of a permanent query on the fly.
There are other combinations but they rely on you splitting the db and having local front ends so I'll not go there. But you get the idea.
Incedently, I appreciate your bafflement but setting Filter="" and FilterOn=True will force a form or report to requery so it has it's uses.
MSAccess is used as a reporting tool in this scenario; it is opened with the instruction to print a report via the command line. The report detects it can open several subselections and should ask the user which one he wants.
I solved this it by (indeed) moving the logic to the application triggering msaccess, but that means the report cannot be opened standalone anymore.
AFAIK the open_report event is executed before the query is executed, this is where you can make changes to the SQL (I sometimes change the SQL just before it is executed - a bit dirty, but the best solution for that situation).