Report with multi-query source

[weckels]

I have a report that displays the data from a query. There are potentially two other queries that have data that need to be combined with the first query's data. I have check boxes that the user can check if they want the second and/or third query's data added. I tried using a make-table query and append queries. This worked as long as no more than 1 person had the report loaded. If someone else tries to run the report the make-table query bombs because the table it was to make is already made and in use.
Any suggestions? Thank you.

Will

 

[randysmid]

Hi,
I think I have a solution to this problem. Obviously, you are running these queries from a sub procedure within a form. You can use the "OnError" code with this procedure.

Here is what it might look like:
Private Sub cmdRunReport_Click
On Error GoTo CheckError
DoCmd ~~~~ run queries, reports, etc.

CheckError:
If err.number = 3260 Then
MsgBox "One of the tables needed is locked by another user"
DoCmd ~~~~~~ run duplicate set of queries, reports
End if

So, the idea is to have a duplicate set of queries to run. The only difference is that the MakeTable name is different. Unfortunately, this will only give you the option of having two users simultaneously running the same report.

Other err.numbers:
3197 Record has been changed since you last accessed it.
3167 Record has been deleted since you last accessed it.

There may be a way to programmatically create a table, but the coding might be stressful (LOL). Plus, there is a way to get the system's user name, which could then make your table name unique. Let me know if you want the code for this.

HTH,
Randy Smith
California Teachers Association

 

[weckels]

Randy,
Thanks for the reply. I appreciate your thoughtfulness. I just recently came up with a solution, I hope. Right after the table is made with the make-table query, I rename it to (NOW). This way every time the table is made it can be renamed to a unique name. I set this unique name to a invisible field on my report form and feed this name to the report's record source. When the report is closed, I delete the temporary table. I haven't fully tested this but I think it will work. Thanks again.

Will