Disable buttons while "calculating..."

[John Keith]

My setup:
A form with buttons to run reports and other routines, containing a sub-form
that shows a detail row per site w/ a DLookup on a query that sums all of the
dollar fields to produce a Total Recipts by site for the currently selected
period (Month-Year which are unbound controls on the main form)

The Total Recipts calculation takes about 10 seconds to complete, during
which "Calculating..." shows on the status bar. If a button is clicked while
calculating, it causes my other form that needs to have the focus set on a
control (so that it will be shown on top of the main form) to loose focus and
thus get covered it up.

How can I determine when the sub-form is done calculating?
So that I can then set the necessary focus and bring it to the front, or
Enable the buttons (after the are disabled as the form 1st opens or when the
period changes - both of which trigger the calculation)

 

[John Keith]

Here is what NOT to do...

I tried adding a text box control on to the footer of the sub-form...
=If(IsNumeric([GTTR]),"Done Calculating","Please Wait...")

GTTR is the grand total for the total recipts on the footer that is the sum
of all the Total recipts from the detail rows.

It caused a "Not enough memory..." error that corrupted the form so that not
only could I not open the form, I couldn't even open it in design to delete
that control. I'm guessing that I caused a runaway recursive loop. Luckily
I had an earlier verison of the form in question that I could import back
from a backup.

Still wondering how do you check to see if a form is done calculating...

 

[Daryl S]

John -

I don't know the answer, but I would try setting the hourglass in the code
before the calculation, and then setting it back off after the calculation.
Will that visual help users not click a button?

 

[John Keith]

If I could run code at the point in time after the calculation was finished,
I Would be able to re-enable the buttons at that point...

The problem is that I can not find any of the events that occur after that
calculation is complete. I created a simple msgbox in every event I could
find off the form and subform but they all fire before the calculation
starts.

Just had a thought and tested it.. I tried the Afterupdate and dirty events
for the grand total box on the sub-form footer... they never fire either.
Since it's control source is "=sum([TotalRecipts])" those events dont apply I
guess. If there was a way to know that the sum has now been resloved to the
calculated value.. this is what could trigger the code I need at the proper
time.

I have noticed that sometimes the Form I need to stay on top does stay on
top even when clicking the button prior to the calculation being complete..
It's just annoying that I can t make the form do what I want. For now my
user has been instructed wait for all calculation to complete before clicking
the buttons.

I am thinking I need to update some hidden fields located back on my main
form with the date range selection. Then I could make the selector form
modal and when the continue button is clicked, I can just update to the main
form and close the modal form then have the reports/queries reference the
main form hidden fields instead.

 

[Daryl S]

John -

I agree, there should be a way to know when the calc is done, but I can't
think of one.

Maybe someone has a better idea, but you could save the value of the grand
total box before starting the calculation, then set a loop (with a timer exit
in case the total doesn't really change) that will break out when control has
a different value. Maybe check every 1/2 second, but exit after 15 seconds
exit in any case.