How do I make my code wait?

[Pheliciano]

I'm writing a database in Access 2007 and I've come across a bit of a problem.

I have a bunch of items in an Items table which contains a Yes/No 'Selector' field to enable the users to select which items they want to affect when performing certain actions. In using the database, the user checks the box for the items they want toaffect and then chooses an action they want to perform on those items. Once the action is complete, VBA clears all the selected fields to make sure that actions aren't performed on items by mistake.

Here's an example of the code from one of the actions:

DoCmd.RunSQL "UPDATE tblItems SET tblItems.Status = '" & strStatus(2) & "' WHERE tblItems.[Selector] = True "
DoCmd.Close acForm, "frmInspected"
DoCmd.OpenReport "rptRepair", acViewPreview, , "tblItems.[Selector] = True"
ClearSelected
Done

And here are the 'ClearSelected' and 'Done' Procedures:

Sub Done()
Dim myMessage As Byte
myMessage = MsgBox("Done", vbOKOnly + vbInformation)
End Sub

Sub ClearSelected()
DoCmd.RunSQL "UPDATE tblItems SET tblItems.Selector = False WHERE tblItems.[Selector] = True"
End Sub

This all behaves as I want it to until I perform the action on more than two items at once. The code seems to be executing too quickly, it opens the report, but before it has loaded a page for each item it has moved on to the 'ClearSelected' procedure and so obviously there are no selected items left to run the report on.

If I comment out the 'ClearSelected' line in the action code then the report opens for all of the selected items rather than just the first two.

How can I make the code wait until the report has been run on all items before going ahead with the 'ClearSelected' procedure? Bearing in mind that the action could be run on any number of items and so the report could take any amount of time to open fully.

Any ideas? Thanks!

[hansup]

Opening the report in dialog mode will suspend the rest of your code until the report is closed.

DoCmd.OpenReport "rptRepair", acViewPreview, , "tblItems.[Selector] = True", WindowMode:=acDialog
ClearSelected ' <- this line will not execute until report is closed

[Pheliciano]

That works a treat, thanks.

The only problem with this solution is: the report is being opened so the user can review it and print it. The dialog window mode lacks a nice intuitive print button. It's not immediately obvious that you have to right click the report to get to the print command.

It seems a bit picky I know, but this needs to be a very simple & intuitive to use database.

[hansup]

You could use the Windows API Sleep function after DoCmd.OpenReport to make the code wait for a fixed number of milliseconds before calling ClearSelected. You can find code for that here: http://www.mvps.org/access/api/api0021.htm

Or you could call ClearSelected from an event procedure in your report, such as On Close:

Private Sub Report_Close()
ClearSelected
End Sub
Or you could add a command button to the form which drives this "action" (frmInspected?), and have the button click event call ClearSelected. In that case, the selections would be cleared only when the user asks them to be cleared.

But I would use a different approach altogether. Instead of saving selection choices in a table, let the user choose their selections from a multi-select list box. Then list those selections in the WhereCondition of DoCmd.OpenReport.

Here is a simple example:
DoCmd.OpenReport "rptLinks",acViewPreview, WhereCondition:="link_id in (1,2,3)"
link_id is the primary key for the data source used by rptLinks.