I have a main form (frmMain) with a button (btnPrintStats) which opens another form (frmPrintOptions) in Modal mode. This form allows the user to select some report options and then, when they click a print button (btnPrint), it closes the form to return control to the main form which then opens the report (rptStats) in Preview mode (see code below).

The problem is that the report is bound to a very complex Union query which can take from about 5 to 60 seconds to process depending on what options the users selects. What happens on screen is that when they click the Print button the form closes and the screen goes virtually blank while the query runs and then, after a period of time, the report appears on screen. So rather than leaving the user with a blank screen I have added another pop up form (frmPrintingReport) which shows a message to the effect that the report is being processed and to please wait. In the report's Page event I have added some code which closes the pop up message form which then disappears when the report appears on screen. This all works fine (and will do if the next bit is not possible).

However, what I would really like to do is rather than giving the user a 'static' screen display, I would like to have some sort of counter on screen which counts off the seconds as the query is processed as this would give the user an idea of the time it takes to process various options they choose.

Problem is, it doesn't work. I added a Text box control to show a number and used the form's Timer to increment the counter at 1 second intervals but it does not work (i.e. the timer does not get triggered). I also tried using the Timer API function with the same result.
What is happening (I believe) is that when the frmPrintingReport form loses the focus to the report itself, any code in the form stops executing, I confirmed this by Remming out the line of code in the report which closes the pop up form and when the report appears on screen - the counter on the form starts counting.

So the question is - is there any way to execute some VBA code in a form when it does not have the focus?



I don't think it is possible to do this but maybe someone has some alternative ideas.

Peter Hibbs.

Hi Peter,

Put the timer in a form that is hidden and always open when the database is open, and when timer fires it opens your form/popup.
I do this for pop-up forms that have dates about to expire for different certificates, etc.

Good luck with your project!!

Hi Aqua,

Sounds like a good plan, I do have a form that is always open. Will give it a try tomorrow and report back then.

Cheers.

Peter.

Peter,

Yea, I use timers in the hidden form in all my DB's.

Good luck with your project!!

Hi Aqua,

Unfortunately, it did not work. I set the TimerInterval to 1000 just before the DoCmd.OpenForm "frmPrintingReport" command and added some code in the Timer event which incremented the pop up form's counter but the Timer event still does not trigger when the query is running.
I think my theory about the form losing the focus was actually wrong, I think what is happening is that when the query is running, it does not allow Access to do anything else which is preventing the timer code from executing. If I Rem out the OpenReport command (so the query does not execute) then it works correctly (although it never stops, of course, because the report does not open to stop it). What we need, I think, is a DoEvents within the Query code (which is obviously not possible).

Looks like I am going to have to live with the 'static' display, which I think my client will be OK with.

Thanks anyway,

Peter.