Filter both report and subreport

[Chad1984]

(This is a continuation of the thread at

http://www.tek-tips.com/viewthread.cfm?qid=1398184&page=1)

Okay. I'm using VBA code to add filter critera in the DoCmd.OpenReport statement. I have a report based on one query with a subreport based on another query- both queries are based on the same common query. The filter works beautifully on the main report, but the subreport is not filtered. What's the best way to filter both in one blow?

 

[Chad1984]

Never mind- I figured it out. I realized that it is possible after all to have the "totals" query run off the regular report query, simply grouping and summing it, minus the "county" field that groups it into sections in the main report. Then when the main report is filtered, the subreport is filtered as well.

 

[Chad1984]

Wow, I must be more tired than I thought. Obviously I wasn't paying attention when I tested it- that did NOT fix the issue! So! Any thoughts on filtering both a report and subreport?

 

[Chad1984]

Okay, I figured it out, finally (yay!) after guessing and researching many many different wrong ways to do it. I'll post the answer here as a reference (because many answers I find are from Googling forums like this one) The simple answer to filtering a report and its subreport is: you don't. Instead, I made up a temporary table that only holds the primary key values I would have filtered the reports by. Then I changed the report queries to say "where myfield in (select * from temptable)" or something similar. Then in the VBA code that iterates through every selected item in the multi-select listbox, I clear the temporay table, insert the values for the selected items, and open the report.