Recalculate Total on Form when record on Subform is deleted
Posted on 2006-06-08
I have cleared the Confirm Record Changes, Document Deletions, and Action Queries in the database's Options.
That has caused the following problem:
I have a global procedure that adds up the Order Detail "Amount" values, and puts the Total in the Main Form's txtTotal textbox.
In other words, if there are 3 items in the subform, the procedure adds up the Amounts for the 3 items ($2,000 + $2,000 + $1,000), and puts that Total ($5,000) on the Main Form.
Here is the procedure:
Public Sub gsbTotalPurchaseOrderAmount()
Forms!frmPurchaseOrderHeader!txtTotal = _
Nz(DSum("[fldAmount]", "[tblPurchaseOrderDetail]", "[fldPurchaseOrderID] = " & _
This procedure is called from the subform's "Item" combobox's AfterUpdate event, the subform's "Quantity" AfterUpdate event, and also in the Main Form's Current event (so the Total textbox is updated when the user clicks through open Purchase Orders).
So far, everything works great.
But I can't figure out how to call the procedure when a subform's record is deleted. If the sum of the 3 Order Detail records is $5,000, and I delete an item that costs $2000, the txtTotal on the Main Form should update to $3,000, but it does not.
I tried placing the procedure call in the subform's OnDelete, AfterDeleteConfirm (where it USED to work, before I cleared the "Confirm..." options), KeyUp, and KeyPress events. I also tried all of these for the Main Form. But the Total on the Main Form doesn't get updated when a child record gets deleted.
How can I get this procedure to fire after a child record gets deleted, (or ANY way to get the Total textbox to update with the Sum of all Order Items still remaining in the current Purchase Order, after a child record is added or deleted, or the Main Form moves to another record) without restoring the Confirm Record Changes, Document Deletions, and Action Queries in the database's Options?