Automate field update from calculated control. 2

[edwilli]

I have a work order form with 3 embedded subforms. Each of the subforms contains one or more items with its price, and a total price for all subform items. There is a calculated control on the main form that adds together the price totals from each subform. Users can add, change, or delete the quantity of each item on the subforms and the calculated control will automatically update.

What I would like to do is have the value of the calculated control copied into a "Total Price" field on the main form whenever the user makes a change to one of the subforms. However I cannot find the combination of Events to push the new total into the "Total Price" field. The After Update event does not work because the subform totals appear to recalculate only after the event fires.

Any advice is greatly appreciated!

Eric

 

[PHV]

Have you tried the Current event procedure of each subform ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[willir]

Eric

To do what you want may require several event procedures including the OnCurrent event procedure indicated by PHV (Congrats once again on Tip-Master! Do you keep track -- you are just amazing in the number of solutions you provide in such concise and wise words)

I had a similar issue to the one you are describing, except it was a Point-of-Sale system.

The way I handled the problem was three fold...
- Used many EventProcedures, the primary ones were OnCurrent and AfterUpdate for key fields.
- Instead of adding values on the form, I referenced the values on the table.
- I used a central function. I would pass the function the primary key to the order (foreign key on the detail table), and recalculate the order.

One last thing...

calculated control copied into a "Total Price" field

As part of the third form of normalization, calculated values are not stored on the table as a field. Why? Well, you just answered your own question. Calculated values can change, and there is administrative overhead to recalculate the correct total. There is a chance there may be an error in the total becase the recalc was not done for a certin condition.

Having said this, sometimes it is more effecient to store a calculated value on a table -- my rules -- the value wont change, it saves processing time and adds to the effeciency, and lastly, you can delete historic data but still retain the summary data. A good example of the above is a balance table / file in a financial application.

...Moving on
My approach was pretty simple.
- Had an unbound text box on the main form, say txtTotalSale
- ControlSource for txtTotalSale was =ReclacSalesOrder(OrderI) where ReclacSalesOrder was a function.
- Anytime I wanted to recalculate the total, the coding for event procedure was ...
Me.txtTotalSale.Requery
... This would call the ReclacSalesOrder function which would add up the value Order.

Richard

 

[edwilli]

Richard

I tried applying your Requery suggestion to my existing code, but without success. However I am at a loss as to why it doesn't work. Here is the code from one of my subforms with the Requery included:

Private Sub Quantity_AfterUpdate()
Me.WorkOrderMaterialPrice.Value = Me.MaterialPrice.Value * Me.Quantity.Value

'Takes the sum of each sub form and inserts it into
'WorkOrderPrice on the main form.

Forms!frmWorkOrder!frmWorkOrderMaterial!txtMaterialSum.Requery '****This line ought to change the txtMaterialSum value, but it doesn't.

Forms!frmWorkOrder!WorkOrderPrice = _
nnz(Forms!frmWorkOrder!frmWorkOrderLabor!txtLaborSum) + _
nnz(Forms!frmWorkOrder!frmWorkOrderMaterial!txtMaterialSum) + _ 'The old value is still present.
nnz(Forms!frmWorkOrder!frmWorkOrderWaste!txtWasteSum)
End Sub

I agree with your normalization views. I don't like entering duplicate data into the database, but having the Total in its own field saves so much time.

Eric

 

[PHV]

And this ?
Forms!frmWorkOrder!frmWorkOrderMaterial.Form.Recalc

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[edwilli]

That did the trick! You're awesome PHV! And thank you Richard for sending me in the right direction!