error referencing subform calculated totals (a timing issue)

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am attempting to reference summary totals on a subform from the current
record event of the main form. Running the subform independently produces
the desired values. The attempted reference form the main form, it appears as
though a null value is returned. If a breakpoint is place on the referencing
statement, an error indicates an automation error occured, and #ERROR appears
in the Sum(xxxx) fields. After some experimentation I found by placing a
timer event to delay the reference of the subform totals every thing worked
fine. The problem with this solution it still fails when the subform is
working on a large number of records. Obviously a large timer value solves
this problem but makes the performance appear very slow.

Question: Is there a method to insure synchronization of the completed
subform calculation and then the main form reference of the result ?

Platforms: Win98se, Win2k and WinXP
Programs: Access97, access2k and access2003 (Similair errors in all)
Hardware: Pentium M centrino 1.7Ghz

Please Email to:

(e-mail address removed)

Thank you
 
If you place the Sum in a calculated control in the Footer of the subform
(hidden if desired once testing is done) does it display the correct value
for you? If so, leave it there and then refer to this control from the
control on the main form. If that doesn't work, you may need to use a DSum
statement in the control on the main form and pass the DSum statement the
same parameters that are being used by the subform to display its records.
This would allow the control to independently get its sum.
 
The calculated controls are in the subform footer. The following statement
attempted to retrieve the subform calculated controls in the current event of
the main form:
Total=me.subform.form!itemtot

The following error occurs:

Run-Time error 62506
The object doesn't contain the Automation object 'Me'

By replaceing the reference statement with a timerinterval=1000,
then adding at timer event to execute:

Total=me.subform.form!itemtot
timerinterval=0

every thing works fine, The calculated values are needed in the subform
trailer, if I then would use Dcalc to recalculate the same values in the main
form makes the process even slower.
It appears to to be a timing issue just need a reliable method to determine
when the Subform caculations are complete.

Superspie
 
What happens if you create a calculated control on the main form that refers
to the control on the subform (Control Source, =Subform.Form!itemtot) then
get the value from this control for your code? If necessary, Requery the
control on the main form in the Current event before you try to retrieve the
value from it (Me.txtTotal.Requery).

Another possibility would be to create a loop that would check the value in
the subform's textbox until it contained a valid value, then continue. Use a
DoEvents command in the loop to allow the calculations to continue while the
loop runs.

Example:
Do Until Not IsNull(Me.Subform.Form!itemtot)
DoEvents
Loop
Total=me.subform.form!itemtot

You may need to pick a different test than "Not IsNull". I would consider
this to be a "last resort" type of option, but will hopefully solve the
timing problem because it should continue as soon as the value is available,
regardless of how long that is.
 
Back
Top