How to Suppress Partial Calculations?

[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]

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

 

[Dirk Goldgar]

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.

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.

 

[Tom Lewis]

That definitely is a simpler approach and should take care
of the initial problem I was having.

Thanks.