report "requeries" form recordset and takes ages!??

[Happy YN]

A button on my form uses code to build sql with criteria based on other tetboxes etc. The resulting recordset is passed to a subform and sometimes takes a while to run but I am happy with it.
Another button previews a report which opens with code specifying that the report recordsource = subform recordsource. This works wonderfully but why does it take so much time to produce the report as if it would be building the sql and querying the tables all over again. Can't it just take all the records in the subform and put them straight into the report.
Maybe there is a quicker way of doing it?
Thanks
Happy YN

 

[llkhoutx]

I suspect your query has several joins, one or more of which has a large number of records.

I suggest that you use several sequential queries (the first being the record source for the second, the second being . . . the third, etc.) to build the rowsource for your report.

Several simple queries run in seriatum runs significantly faster that one large query with several joins.

 

[Happy YN]

Thanks for reply....
Agreed but once I have the recordset in the form why can't it take the report recordsource from there.
After posting I checked something. I removed page x of y on the report and it now does it in a fraction of the time. reason is that it needs to run thru it twice. once to build pages and count them, then again to produce them hence the time difference! So I don't think it requerying the sql, rather reformatting the report
Any other ideas apppreciated
Thanks again
Happy YN