Pause execution opening a form until the form has calculated

[bonnief]

I a have a form with multiple summed fields whose control source is "=DSUM..." or "=SUM...". When the form opens it shows "Calculating..." in the bottom left corner until all of the fields actually have their values calculated. I would like to display the Hourglass while the form shows "Calculating..."? Is there some form event that I could use to turn the Hourglass on? Is there a form event that finishes when the form is done updating that I could use to turn off the hourglass?
Thanks!

 

[smedvid]

htih...

doCmd.hourglass true 'turn it on
doCmd.hourglass false 'turn it off

'Display text in Status Bar
SysCmd acSysCmdSetStatus, "Calculation - Step 12 of 17 - Manual Adjustments - " & Now()

SysCmd acSysCmdClearStatus 'Clear Status Bar

'Function to pause processing x seconds
' Call as i.e. DelayTime 3

Public Function DelayTime(PauseTime As Double)
'sometimes used to slow the program down and allow for screen updating or
'other processes to catch up
Dim start
'PauseTime = 4 ' Set duration.
start = Timer ' Set start time.
Do While Timer < start + PauseTime
DoEvents ' Yield to other processes.
Loop

End Function


Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...

 

[bonnief]

Form frm603 is the form I want to delay opening until all frm603 fields have been calculated. With my code, the fields are still not calculated when the form is opened. My code does delay for 10 seconds, but I think it is stopping everything so that my fields don't calculate until AFTER MY DELAY? What am I doing wrong? Thanks!

This is my code:

DoCmd.OpenForm &quot;frm603&quot;, acNormal, &quot;txtRecordSource&quot;, &quot;&quot;, , acNormal

'---------------------------------
'Pause so frm603 has a chance to calculate all values before it is displayed
DoCmd.Hourglass True 'turn the Hourglass on

'Display text in Status Bar
SysCmd acSysCmdSetStatus, &quot;Please wait until all fields on this screen are calculated with values before running a report&quot;

'Function to pause processing x seconds
DelayTime 10 ' Call function DelayTime (delay 10 seconds)

MsgBox &quot;Please wait until all fields on this screen are calculated with values before running a report.&quot;, vbInformation, &quot;Please wait...&quot;

SysCmd acSysCmdClearStatus 'Clear Status Bar
DoCmd.Hourglass False 'turn the Hourglass off
'---------------------------------


Here is my DelayTime function:

Public Function DelayTime(PauseTime As Double) 'If PauseTime is 4 seconds, this will delay 4 seconds.

'Sometimes used to slow the program down and allow for screen updating or other processes to catch up

Dim varStart
varStart = Timer ' Set start time. Returns a Single representing the number of seconds elapsed since midnight.

Do While Timer &lt; varStart + PauseTime
DoEvents ' Yield to other processes.

Loop

End Function

 

[smedvid]

Perhaps the delay needs to be in form &quot;frm603&quot;. In the On Open, Activate or Load event? Use ADO/DAO SQL to load variables with result sets?

Or another approach may be the solution. Do these calculations need to be performed automatically or can the user press a button on the form to calculate. Perhaps hide all objects except the calculate button?

Can you pre-calculate before the form is even openned. Store the calculated values into a database table and then just display those fields from the table?

Also, if you are using queries, are they fully optimized with proper indexes? If you are using a large dataset, can it be reduced? Like archive old data?

htwh,

Steve Medvid
&quot;IT Consultant & Web Master&quot;

http://www.saveourfarm.com

Chester County, PA Residents
Please Show Your Support...