How to Recalculate all rows in subform Datasheet (1 Viewer)

Hello,

Please help. I am trying to figure out how to recalculate all rows in a subform datasheet when a value in the main form is updated.

I can't post pictures yet so I will have to describe my forms.

The main form is a work order and, among other things, contains a "Quantity" field which is used in calculations in the subform.

The sub form is the routing through our various Work Centers that produce our end product. The main body of the sub form includes, among other things, a field that summarizes the rows of a datasheet within that subform and then calculates a total duration for the Work Order and a Must Start Date.

Here is the calculation I am using in each row of the datasheet.
This is triggered in the "after update" event when the Work Center is selected. "[Capacity(Lbs/Hr)]" and "Dur" are also loaded from a query when the Work Center is selected. Some Work Centers may not have a Capacity specified. In that case a Dur will be specified.

The rows in the datasheet perform a calculation of the "Duration" for each Work Center. I have all this working well and am happy with it with one exception.

I can't figure out how to cause all the rows in the datasheet to recalculate if the Quantity in the main form is updated. Any suggestions would be greatly appreciated.

Thanks,
Mike

suggest in the qty control afterupdate event, call the workcentre afterupdate event

CJ_London said:

suggest in the qty control afterupdate event, call the workcentre afterupdate event

Click to expand...

Thanks for your response, but I don't understand how to do that. The Quantity is in the main form. Work Center is in the subform Datasheet rows. I need to call that event for each row of the datasheet. That is what I am stuck on.

suggest change your calculation from vba code to an iif statement in the control controlsource

=iIf( [Capacity(Lbs/Hr)] > 0, [Parent].[Quantity] / [Effective Capacity],Dur)

then you just have to requery the subform

However I don't understand how your code is working at the moment. You say it is on the subform, but the quantity is on the main form - but your code is looking for qty on the subform

CJ_London said:

suggest change your calculation from vba code to an iif statement in the control controlsource

=iIf( [Capacity(Lbs/Hr)] > 0, [Parent].[Quantity] / [Effective Capacity],Dur)

then you just have to requery the subform

However I don't understand how your code is working at the moment. You say it is on the subform, but the quantity is on the main form - but your code is looking for qty on the subform

Click to expand...

Oh. Good catch. I am actually getting QTY from the query not the form. It joins the Work Order table with the Routing Table.

I am actually getting QTY from the query

Click to expand...

In that case I would be inclined to put the calculation in the query unless that prevents you from editing

CJ_London said:

In that case I would be inclined to put the calculation in the query unless that prevents you from editing

Click to expand...

Working on that now. Thanks.