How to insert a Please Wait form while a query is running

Question

I want to display a "Please Wait" form while a 60+ second query is running. I have the query and the form made (easy enough), except the macro won't bring them together. Here is my autoexec macro:

1. SetWarnings=False
2. Open Wait Form
3. Run the query & Display Results
4. Close Wait Form

The problem is that I can only see the outline of my wait form while the maketable query is running. I can only guess that the macro doesn't wait for the form to completely load before going to the next step.

And just for fun:

The Wait form has a Timer that shows the number of seconds elapsed. How do I run the query while still allowing the form to update itself at the same time?

-------Update----------

Repaint only updates the background color before moving on to the query. I also tried 6 repaints in a row with no difference (I thought that might get around the bug you people mentioned). The form is very simple. A single text box that uses "loading" for the control source and a timer function (disabled for the moment). Here is my macro in slightly greater detail:

1. SetWarnings=False
2. Open Wait Form (modal=yes, popup=no)
3. Repaint the form
4. Open a maketable query (this query saves alot of time)
5. Close Wait Form
6. Open the display query

Answer 1

The first problem is easy to solve, try putting me.Repaint before the query runs, sometimes access can get a bit lazy with screen painting and you have to force it.

As for the other thing that is a bit more complicated. You would have to execute the query asynchronously. You can do this in ADO by adding the adAsyncExecute option when executing the query. You would then catch the finishing event to tell the users the query is done

Here is a link on the Microsoft knowledge base http://support.microsoft.com/kb/262311

Answer 2

Firstly, you just need to Repaint the wait form after it is shown for it to show correctly, it's just a bug in VBA that sometimes causes forms not to be displayed properly even if they are drawn at the correct point in the code.

Secondly, if you set the wait form's ShowModal property to false then you will be able to update it through your routine. I use a public function that takes a 0-100 argument to update a progress bar. This works best in a loop where you can normally calculate the number of loops remaining and provide an accurate guide to the elapsed progress, but even when performing a series of operations you can time and pass suitable values through to the progress form so that the user is kept informed. This works better than most Windows progress bars!