Link to home
Start Free TrialLog in
Avatar of DavidWare
DavidWareFlag for United States of America

asked on

Recalculate Total on Form when record on Subform is deleted

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] = " & _
    Forms!frmPurchaseOrderHeader!txtPurchaseOrderID), 0)
End Sub

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?

Thanks,
David
Avatar of puppydogbuddy
puppydogbuddy

David,
Did you place a code break on your public procedure to verify that it was not being called?  In other words are you sure the procedure was not being called, or was it in fact, being called but the field was not updated because the code was falling through?
                                    PDB
You might try placing the call in the Form's after update procedure.
in the previous post, I meant subform's after update procedure.
Also, when you make the call, how do you pass the purchase order ID to your global procedure.  further, seeing as to how you have an nz function on the amount field, how are you handling item/po deletes....as negative transactions offsetting the original transaction, or?
Avatar of DavidWare

ASKER

Hello PDB,
1)  The code executes for added records in the 3 instances I described, so I have good reason to believe it is not being called.  I just tested a STOP and it did not execute.
2)  I have tried the Subform's AfterUpdate event.  It does not execute (the STOP is still in there)
3)  I don't pass the purchase order ID to the global procedure - it is read from the Main Form within the procedure (please look at the procedure code I posted in the question)

David
Hi David,
try calling your global proc directly after issuing the delete command.  What happens??
PDB,
I'm not sure what you're saying.
Where, in the code module (and what Form's code module) are you suggesting I place the procedure call?
I have tried several places.  The question is "Where, in the code module, do I place the procedure call?".
So, for the points, "Where Do I Put The Procedure Call?"
David
where do you have your delete command....DoCmd.RunCommand acCmdDeleteRecord or equivalent???Place the call to your global proc on the next line...and tell me what happens.
The record is deleted using the Detail record's Record Selector on the Datasheet subform.
The user selects the record and hits the Delete key.
ASKER CERTIFIED SOLUTION
Avatar of puppydogbuddy
puppydogbuddy

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Hi David,

I have not heard from you. That last code I gave you should have worked.  Did it??

                           PDB
Hello PDB,
I'm visiting the customer this week, and I'll give this code a try.
Thanks,
David