Sub form is not showing the most updated data

[Guest]

I have a main form with 3 subforms. The first two subforms (Revenue and
Costs) in turn have 4 subforms each. The totals showed in all those subforms
is based on selections made using combo boxes on the main form, which allows
the user to select, for example, a specific region, country, channel or
month. Everything is working well, the data is indeed updated in all subforms
every time a new option is selected in one of the combo boxes.

But the 3rd subform (Margins), which contains Text Box controls whose
formulas make reference to the other subforms' children, is only updated
once, when the form is firstly loaded (a default selection is setup for each
combo box on the main form). Note that this 3rd subform doesn't have any
"link fields" to the main form, it contains only formulas.

What can be done to updated or "refresh" the data on this Margin subform
every time a new selection is made on the combo boxes?

This is an example of the formulas used on these Text Box controls. We
basically subtract the costs (children forms of 2nd subform) from the revenue
(children forms of 1st subform) to show the gross margin. As said before, the
formulas are right, they return the correct results when the form is loaded,
but they are not being updated.

=
Forms!PerformanceReporting!SubPerformanceReporting.Form!SPR_Voice.Form!Text58
-
Forms!PerformanceReporting!SubPerformanceReportingTelco.Form!SPT_Voice.Form!Text96

Thanks in advance,

Paulo Tavares

 

[Duncan Bachen]

I have a main form with 3 subforms. The first two subforms (Revenue and
Costs) in turn have 4 subforms each. The totals showed in all those subforms
is based on selections made using combo boxes on the main form, which allows
the user to select, for example, a specific region, country, channel or
month. Everything is working well, the data is indeed updated in all subforms
every time a new option is selected in one of the combo boxes.

But the 3rd subform (Margins), which contains Text Box controls whose
formulas make reference to the other subforms' children, is only updated
once, when the form is firstly loaded (a default selection is setup for each
combo box on the main form). Note that this 3rd subform doesn't have any
"link fields" to the main form, it contains only formulas.

What can be done to updated or "refresh" the data on this Margin subform
every time a new selection is made on the combo boxes?

This is an example of the formulas used on these Text Box controls. We
basically subtract the costs (children forms of 2nd subform) from the revenue
(children forms of 1st subform) to show the gross margin. As said before, the
formulas are right, they return the correct results when the form is loaded,
but they are not being updated.

=
Forms!PerformanceReporting!SubPerformanceReporting.Form!SPR_Voice.Form!Text58
-
Forms!PerformanceReporting!SubPerformanceReportingTelco.Form!SPT_Voice.Form!Text96

Thanks in advance,

Paulo Tavares

One thing you could do would be to create a function which sets the
values of the textboxes in subform3, rather than having the controls
directly reference the values they need.

This way you can call that function from the After_Update Event of the
combo boxes. Once they change, it will update the other textboxes.

Private Sub CalcFormulas
txtSubFormTextBox1 =
Forms!PerformanceReporting!SubPerformanceReporting.Form!SPR_Voice.Form!Text58

txtSubFormTextBox2 =
Forms!PerformanceReporting!SubPerformanceReportingTelco.Form!SPT_Voice.Form!Text96
End Sub

Private Sub cboMainFormCombo1_AfterUpdate()
CalcFormulas
End Sub