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
DavidWareAsked:
Who is Participating?
 
puppydogbuddyConnect With a Mentor Commented:
Aha, so you need to trap the delete key.  Try this:

In the KeyDown event:

     If KeyCode = vbKeyDelete Then
              'call your global procedure
               KeyCode = 0
     End If


0
 
puppydogbuddyCommented:
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
0
 
puppydogbuddyCommented:
You might try placing the call in the Form's after update procedure.
0
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
puppydogbuddyCommented:
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?
0
 
DavidWareAuthor Commented:
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
0
 
puppydogbuddyCommented:
Hi David,
try calling your global proc directly after issuing the delete command.  What happens??
0
 
DavidWareAuthor Commented:
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
0
 
puppydogbuddyCommented:
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.
0
 
DavidWareAuthor Commented:
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.
0
 
puppydogbuddyCommented:
Hi David,

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

                           PDB
0
 
DavidWareAuthor Commented:
Hello PDB,
I'm visiting the customer this week, and I'll give this code a try.
Thanks,
David
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.