Main Form won't total subform fields?

[Randomblink]

I have a form that is used to Projects handled by the City. On this form, there is a ChangeOrder section where any ChangeOrders are entered and tracked. My problem is this:

On the subform there is a field that holds the amount of the ChangeOrder and one that holds the reason for the ChangeOrder.

What I want is to have a field on my main form to show an Accumulated ChangeOrder amount...

There is an unlimited number of ChangeOrders that could be processed, so that for Project X, there could a change order for $1,000 and one for $2,000 and another for $12,000...

My problem is I can't figure out how to have the Accumulated field on my Main Form update to show all the ChangeOrder amounts from my subform...

My subform is a Continuous Form, with 3 fields:
Field 1: [Change Order Number]
Field 2: [Change Order Amount]
Field 3: [Change Order Justification]

I have set up an Expression on the Accumulated field that Sums the ChangeOrderAmount on my subform After the ChangeOrderAmount field Updates...

It glitches...

I have tried writing VB to do it, but the word SUM calls a Function somewhere and so it won't work...

Could someone point me in the right direction...?

 

[Rich]

Put a calculated control in the subform footer and refer to that on the main form.

 

[Randomblink]

How do I refer to that on the Main Form?
I have looked at every Event you can set to align or SetValue or whatever and I can't find anything that will keep it updated...

I did however figure out to go in and create (on my subform) a form footer (.visible = false) with an unbound field (=Sum[Change Order Amount]) and the unbound field updates GREAT...

But on my Main Form I can't get the linked field to update with the unbound...
You can't set the OnChange Event to do anything with a calculated control...ugh!
And BeforeUpdate and AfterUpdate don't work either unless you tab all the way from that record...ugh!

If I could find a way, I would set the LostFocus Event on the Change Order Amount field (on the subform...fyi) so that it would automatically update THAT field and then reference through to the Main Form...

If someone could help I would appreciate it...thanks...

 

[Rich]

Just set the record source to =Forms!MainformName!SubFormName subform.Form!ControlName

 

[Randomblink]

Problem is, that field ALREADY has a Control Source, it is a field in the underlying table...

I know, I know...

It's not good to have a field contain calculated data...

Talk to my boss...

 

[Randomblink]

So can anyone help me...?