When does a report unlock the table? 1

[FoxProProgrammer]

My program creates a temporary table with a SELECT... INTO query. I use a random number generator to create the table name, which is stored in a string variable called tmpTable. A report prints a subset of records from tmpTable with the use of a Select... From tmpTable...Where query that is stored in the report's recordsource. This all works fine.

The problem occurs when I try to delete the temporary table with DoCmd.DeleteObject acTable tmpTable. Access says that the table is locked and returns an error that the table can't be deleted. I determined that the report has the table locked, so I tried to move the delete table action to the report's Close event. That didn't work...same error.

My question is, when does the report unlock the table? I figured that it would release the table before the Close event executes, but I must be wrong. I tried moving the delete action outside the report, to the statement after the DoCmd.OpenReport command. That didn't work either because the program doesn't wait for the report to close before running all the way to the end.

I also tried looping until the report was closed, and then executing the delete table action. This works in debug mode, but it locked up Access when I ran outside the debugger. It didn't work because Access won't let you click the mouse on the report, or anywhere for that matter, when it is in the loop. Here's the code:

Do While SysCmd(acSysCmdGetObjectState, acReport, "Undefined Parts&quot = 1
Loop
DoCmd.DeleteObject acTable tmpTable

Does anyone have an idea how to get around this? I am open to suggestions to alter any of the ways that I tried, or something entirely different.

Thanks,

dz

 

[Steve101]

dz,

Use the following code to do the looping whilst the report is open:

While Application.CurrentObjectName = "YourReportNameHere"
DoEvents
Wend

This code should be placed immediately after the code which opens the report (ie. do not include it in the report's events). What it does, is keeps the code in a loop until the report is closed (at which point the CurrentObject changes; hense the loop terminates).

You should be able to place your table deletion code immediately after the Wend statement.

Hope this helps,
Cheers,
Steve

 

[FoxProProgrammer]

Thanks, Steve!

I had a problem with Application.CurrentObjectName, but DoEvents worked great with my original code. Here's what I did to solve the problem.

DoCmd.OpenReport "Undefined Parts", acViewPreview
Do While SysCmd(acSysCmdGetObjectState, acReport, &quot;Undefined Parts&quot <> 0
DoEvents
Loop

This works like a champ. I very much appreciate your help.

dz