[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Recalculate Total on Form when record on Subform is deleted

Posted on 2006-06-08
11
Medium Priority
?
284 Views
Last Modified: 2011-10-03
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
0
Comment
Question by:DavidWare
  • 7
  • 4
11 Comments
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16864381
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16864399
You might try placing the call in the Form's after update procedure.
0
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16864473
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 

Author Comment

by:DavidWare
ID: 16864578
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16864785
Hi David,
try calling your global proc directly after issuing the delete command.  What happens??
0
 

Author Comment

by:DavidWare
ID: 16864901
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16864984
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
 

Author Comment

by:DavidWare
ID: 16871440
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
 
LVL 38

Accepted Solution

by:
puppydogbuddy earned 400 total points
ID: 16871814
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
 
LVL 38

Expert Comment

by:puppydogbuddy
ID: 16905973
Hi David,

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

                           PDB
0
 

Author Comment

by:DavidWare
ID: 16939288
Hello PDB,
I'm visiting the customer this week, and I'll give this code a try.
Thanks,
David
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question