Which event to use when updating form when subform updated ?

[TonyB]

Hi,
I'm lookng for some help with updating a value in a form when a subform
total changes ?
I have an order form which contains a subform containing several line items,
and there is a control on the subform which
calculates the total cost of the line items in the subform.
On the main form I have a control which takes this subtotal, and adds sales
tax. I want to update the total as and when items are altered or added in
the subform.
My problem is trying to detect if the subtotal in the subform changes and
updating the total on the main form.
Is there any way to communicate the change in subform total to the main form
so I can recalculate that ?
TIA
Tony

 

[John Vinson]

My problem is trying to detect if the subtotal in the subform changes and
updating the total on the main form.

Use the AfterUpdate event of the form you're using as a subform.

John W. Vinson[MVP]

 

[TonyB]

Hi John,
I thought about this but this control only updates when I move between
records in the subform.It's data source is =Sum([price]), but this only
updates when I select a different record in the subform, not when I update
the price field in the record.
Basically each line item has a number of fields which affect the total for
that line item. This works fine. I use events on each item in the record to
update the totla for that record. What I'm finding difficult to see for some
reason is how to "programatically" force the =Sum([Price]) control to
update in the subform when the current record is updated, and then to update
the grand total on the main form ?
Regards
Tony

 

[John Vinson]

Hi John,
I thought about this but this control only updates when I move between
records in the subform.It's data source is =Sum([price]), but this only
updates when I select a different record in the subform, not when I update
the price field in the record.

The record is only saved to disk (by default anyway) when you move off
the record. You could put code in the [Price] control's AfterUpdate
event:

Private Sub Price_AfterUpdate()
Me.Dirty = False ' force a save of the record
Me.Recalc ' recalculate the form including the sum field
End Sub

You may also need to "push" the sum into the main form:

Parent!controlname = Me!controlname

John W. Vinson[MVP]

 

[TonyB]

Hi John,
I tried your suggestion, but although the control [price ] is changing the
after update event is not triggered so the code you suggested doesn't get
run.

This maybe because the value of the price control is adjusted by my vba
code, so for instance if qty is changed, I use the after update event to
adjust the price control value.

So instead I use Me.recalc in the vba that calculates the new value for the
record price control, and this method updates the sub total and also the
parent control which is what I wanted to achieve.

I'm curious that the act of writing a new value to a control using vba
doesn't cause an update event for that control. Should that happen ?

Thanks
Tony

Hi John,
I thought about this but this control only updates when I move between
records in the subform.It's data source is =Sum([price]), but this only
updates when I select a different record in the subform, not when I update
the price field in the record.

The record is only saved to disk (by default anyway) when you move off
the record. You could put code in the [Price] control's AfterUpdate
event:

Private Sub Price_AfterUpdate()
Me.Dirty = False ' force a save of the record
Me.Recalc ' recalculate the form including the sum field
End Sub

You may also need to "push" the sum into the main form:

Parent!controlname = Me!controlname

John W. Vinson[MVP]

 

[Ken Snell [MVP]]

Hi John,

I'm curious that the act of writing a new value to a control using vba
doesn't cause an update event for that control. Should that happen ?

No. When you programmatically change the value of a control, that control's
AfterUpdate and BeforeUpdate events do not occur. This is documented in the
Help file. When you change the value programmatically, if you want to run
code on one or both of these events, you'll need to call the procedure in
your code.

Me.ControlName.Value = "MyValue"
Call ControlName_AfterUpdate()