How to Suppress Partial Calculations?

  • Thread starter Thread starter Tom Lewis
  • Start date Start date
T

Tom Lewis

When I use a control on a main form to display a total
that is calculated in a subform's footer, the value shown
in the main form changes as Access parses through the data
in the background. Ultimately, the correct total is shown
when the calculations are complete, but I do not want to
display a value until the calculations are complete and
the correct value is availiable. Is there a property or
event that can be used to signal that the total from the
subform is done calculating?

(I am using an Access 2002 project with data in MSDE.)

TIA for any suggestions.

Tom
 
Tom Lewis said:
When I use a control on a main form to display a total
that is calculated in a subform's footer, the value shown
in the main form changes as Access parses through the data
in the background. Ultimately, the correct total is shown
when the calculations are complete, but I do not want to
display a value until the calculations are complete and
the correct value is availiable. Is there a property or
event that can be used to signal that the total from the
subform is done calculating?

(I am using an Access 2002 project with data in MSDE.)

TIA for any suggestions.

Tom
Click to expand...

No, I don't think there's any way to do this using a calculated control.
However, you could use a completely unbound control, and set its value
from the subform at whatever point you determine that you've got all the
information required for the calculation.
 
Dirk,

Thanks for the reply. Maybe you're right that an unbound
control is the way to go. It would allow better control
over what is displayed as the total.

However, it makes me uncomfortable that Access would
display an arbitrary result while it is still in the
process of totalling data in the subform. Do you have an
idea of what may actually be happening within Access to
make it 'jump the gun' and show a total prematurely? Are
there timeout or other server settings that might make
Access more aware that calculated controls aren't done
calculating?

Thanks again,

Tom
 
Tom Lewis said:
Dirk,

Thanks for the reply. Maybe you're right that an unbound
control is the way to go. It would allow better control
over what is displayed as the total.

However, it makes me uncomfortable that Access would
display an arbitrary result while it is still in the
process of totalling data in the subform. Do you have an
idea of what may actually be happening within Access to
make it 'jump the gun' and show a total prematurely? Are
there timeout or other server settings that might make
Access more aware that calculated controls aren't done
calculating?
Click to expand...

I'm not sure what phenomenon you're reporting, so I don't know whether
the behavior is expected and proper, expected but undesirable,
unexpected but explicable, or just plain buggy. Could you describe
exactly what's happening? How are the form and subform set up, what's
the controlsource expression, and what does it show you when?
 
I've figured out what is happening:

The problem is resulting from the use of the subform's
OnCurrent event to set the current record in the main form
as the subform's selection changes. When the form is first
opened the OnCurrent event is firing before the subform's
control has finished totalling the recordsource, and the
main form displays whatever the total was at the instant
the main form is refreshed by the code, which is:

Private Sub Form_Current()
Dim rs As ADODB.Recordset
Set rs = Parent.Form.RecordsetClone.Clone
rs.Find "FacilityID='" & FacilityID & "'"
DoCmd.GoToRecord acDataForm, _
Parent.Form.Name, _
acGoTo, _
rs.AbsolutePosition
DoCmd.RunCommand acCmdSelectRecord
End Sub

I think I can avoid the problem by preventing this code
from executing when the form first opens, or as you
initially suggested, by using unbound controls that the
subform will update as needed.

Thanks for your help.

Tom
 
Tom Lewis said:
I've figured out what is happening:

The problem is resulting from the use of the subform's
OnCurrent event to set the current record in the main form
as the subform's selection changes. When the form is first
opened the OnCurrent event is firing before the subform's
control has finished totalling the recordsource, and the
main form displays whatever the total was at the instant
the main form is refreshed by the code, which is:

Private Sub Form_Current()
Dim rs As ADODB.Recordset
Set rs = Parent.Form.RecordsetClone.Clone
rs.Find "FacilityID='" & FacilityID & "'"
DoCmd.GoToRecord acDataForm, _
Parent.Form.Name, _
acGoTo, _
rs.AbsolutePosition
DoCmd.RunCommand acCmdSelectRecord
End Sub

I think I can avoid the problem by preventing this code
from executing when the form first opens, or as you
initially suggested, by using unbound controls that the
subform will update as needed.
Click to expand...

This seems to me to be a very strange thing to be doing: you're making
the main form depend on the subform, rather than the other way around.
If you're doing it so as to have the subform show a list of records and
the main form show details of the record currently selected on the
subform, a much simpler way to do it is to have two subforms, one in
continuous view and one in single-form view, on an unbound main form.
Let a calculated text box on the main form pick up the FacilityID value
from the continuous subform, and specify that calculated text box as the
Link Master Field for the single-form, "detail" subform. No code is
required.
 
That definitely is a simpler approach and should take care
of the initial problem I was having.

Thanks.
 
Back
Top