none
How to catch form event after record is deleted? RRS feed

  • Question

  • I've tried Form_AfterUpdate and Form_AfterDelConfirm

    AfterDelConfirm seems to fire before the record is actually deleted, and AfterUpdate doesn't fire.

    I have an order entry form with a subform for order lines. The subform is bound to a local table(named locOrderLines) in the Access project. The user can right click the record selector and delete an order line or select the record selector and click the Delete key to remove the order line. After the line is removed, I want to recalculate the order price and update the parent form. To recalculate the order price, I sum the Extended_Price field on locOrderLines and update the parent form's Me.txtPrice control.

    If I use AfterDelConfirm or Form_Delete, the price is calculated before the record is removed.

    Thanks for your time!


    • Edited by Anthony Sylvia Tuesday, November 27, 2018 7:40 PM added "or Form_Delete"
    Tuesday, November 27, 2018 7:27 PM

All replies

  • Hi Anthony,

    Just a thought but how about trying to use the Current event?

    Tuesday, November 27, 2018 7:44 PM
  • Why are you apparently storing the total order amount rather than computing it from the order lines data?  By storing the amount you are introducing redundancy and the consequent risk of update anomalies.

    At its simplest the order total can be returned in an unbound control in the subform's footer.  This will automatically reflect any changes to the order.  You'll find an example in DatabaseBasics.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link, copy the link (NB, not the link location) and paste it into your browser's address bar.

    This little demo file includes a basic orders form in the section on 'entering data via a form/subforms'.  A more realistic example can be found in InvoicePDF.zip in the same OneDrive folder.  While this relates to invoices rather than orders the methodology is identical.  In this the invoice total is computed in a separate subform, taking account of differential rates of VAT (Value Added Tax) attracted by each product ordered.

    Ken Sheridan, Stafford, England

    Tuesday, November 27, 2018 11:36 PM
  • I concur with KS. You want to rethink the fundamental design: "After the line is removed, I want to recalculate the order price and update the parent form."

    This type of total is often best to be a calculated display rather than a stored value (the users don't know the difference).  In which case one just needs to refresh the unbound field that does the calculation at most - typically it will auto recalc.

    For overview reporting i.e. see the sums of all orders last month; one uses an aggregate query to sum each order for display in a report.


    Wednesday, November 28, 2018 2:38 PM