Have an application which allows filtering and produces 7 reports or charts (using tab control) that can be viewed as filters are applied. I also offer the ability to print or print to PDF. The format on the forms works good for the screen, but for the reports and PDF, I need to do more appropriate formatting. This works fine by creating a report and putting the form (drag/drop as a sub form) in the report.
The problem arises when I pass the Where Condition to the report. It appears that since the report is based on the form, it does not pick up the Where Condition. I tested by creating a report based on the form and doing a VBA OpenReport with the Where Condition it worked as expected. However, if I put the form into the report (drag/drop), same command the Where Condition is not picked up. Am wondering if I need to execute a focus commmand or have a different reference for the Where Condition. Stumped, would appreciate any help. Thanks.
But rptTestQuery2 has no recordSource....
;-)
If the *form* is what you want filtered, then you must pass the filter to the form.
This is why we all are wondering why you are doing this in such a non-standard way...
This is why LSM suggests building the SQL in Code.
Another factor here is that some code in a Report will not fire in Report View (The default view for both of your reports.), ...but will fire in Print Preview.
So, ...If you set the subform's Filter (via a Public variable, on the subforms Open Event) when the Report is opened in Report View, the Subform's Open Even will fire.
Thus giving you what you want.
(You will have to determine the correct event to clear the Public Variable though...)
...If, on the other hand, you try to set the Filter of the subform (via a Public variable) when the Report's Default View is Print Preview, ...the form won't filter, because in Print Preview the subform's Open Even will not fire.)
This is why your technique may become confusing at some point in the future.
I hate to sound like a broken record when it comes to all the things I have learned from LSM...
But of all the different ways to "Filter" anything, setting the recordsource is, AFAIC, buy far the most reliable...
No the easiest, mind you, ...just the most reliable.
Remember that reliability is the only thing that really matters in most cases.
;-)
Sometimes when coding things like this, it is easy to get lost in your own code.
When this happens I always revert to setting the SQL in code.
The all is right with the world...
;-)
Revised sample attached
JeffCoachman
Access-EEQ26493811FilterSubforIn.accdb