Different Reports/Same Table

[GrexP]

I have a make table query I run to produce the data for a report. The query is executed in code with a DoCmd.RunSQL call. The criteria is based on user selection from some list boxes populated from other tables.

The user clicks a button. The MT Query executes. Then the report pops up. The problem occurs if the user makes a differernt selection and clicks the button a second time to produce a different report based on different data.

An error occurs because the MT query tries create the table a second time while the original report is still open in preview mode. I would like the user to be able to view two reports side by side.

Any ideas? I get Run time error 3211 because the report seems to have the table locked. Is there away to release the table with the report still visible so i can create the next report?


Thanks

 

[Peter Bellamy]

I don't have a solution for, but something to think about.
The first report is based on the Table you are trying to rebuild on the second run. I won't as it open providing data to the first report.

How about naming the table with suffix of 1, 2, 3, etc so you make a completely new table for each run?

Cheers

Peter

 

[GrexP]

That's a good idea, but I'm not sure it would work. If the report is based on tblReport, for example, I can't then call subsequent tables by a different name. The report won't find the data.

 

[Peter Bellamy]

Perhaps a little clumsy but you could have copies of the tblreport, also numbered tblreport1, tblreport2 etc., and select the next one on each pass. I guess your users won't want too many to compare. And you could trap it if they asked for more than you had allowed for.

 

[boblarson]

Why build a table for the report? - instead of a make table query, just base the report on a select query, then there's no problem.