We help IT Professionals succeed at work.

MS Access How to detect when a record is actually deleted

Eddie Antar
Eddie Antar asked
on
This is driving me crazy. I have a form with a subform. On the subform, each record has a percentage field. On the main form I have a text box showing the total percentage. On my Adds and Updates it recalculates exactly as expected. BUT I can't seem to get it to work after deleting a record on the subform.

What I want is, after a deletion (after the record is actually deleted!) for the text box on the main form to recalculate. I'm not using Delete Confirm, so please keep that in mind. I've tried using a flag that triggers on deletion and then check that flag during the On Current, but that didn't work either.

There must be a way to do this. I think I've done it before, but can't remember.

At what point AFTER a deletion can I run a procedure that will calculate a field on a table.

Any help would be greatly appreciated.

Thanks,
Eddie
Comment
Watch Question

View Solution Only

John TsioumprisSoftware & Systems Engineer

Commented:
Have you tried requerying ?
Eddie Antar

Author

Commented:
Yes. I just need the point at which I can query the database right AFTER the deletion.

Thanks,
Eddie
John TsioumprisSoftware & Systems Engineer

Commented:
There cases where it's done automatically..or it needs some "helping hand"
can you share a sample
Mark EdwardsChief Technology Officer

Commented:
The After Delete Confirm event runs AFTER the record(s) have been deleted and knows the post-deletion record count.  You can use it to make a call to run your process.
Eddie Antar

Author

Commented:
Hey Mark, thanks for your comment. I tried using Delete Confirmation a while ago and ran into some issue. Is After Delete Confirm the only way? I thought there was another way to detect an After Deletion. Using flags maybe.

Thanks.
Mark EdwardsChief Technology Officer

Commented:
You are not telling us HOW you are deleting the records on the subform.  Is it manually by pressing the Delete button (which brings up the Delete Confirm popup box?  (probably not).
Are you doing it with a code button?  If the code is making the deletion, can't the code also call the recalculation process after the deletion?
Please paint us a picture of what exactly you are doing so we don't start making wild guesses.
Mark EdwardsChief Technology Officer

Commented:
Right now After Delete Confirm is the only event trigger I know that fires AFTER the record is deleted.  What do you have against After Delete Confirm?  If that will work for you, why do you need anything else?
Eddie Antar

Author

Commented:
Sorry. I'm clicking on the record selector in the subform and hitting the delete key.

Thanks
Chief Technology Officer
Commented:
Using that method, you probably have Confirm Delete popup notice.  After you click "OK", then the After Delete Confirm event fires.
If your objective is to NOT get the Confirm Delete popup (which you get when SetWarnings is True, you can use the following code to turn them off, then on again so you don't get that pesky popup:
Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    DoCmd.SetWarnings False
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    DoCmd.SetWarnings True
End Sub

Open in new window

DexterFanDatabase Developer

Commented:
There is an On Delete event. Put some code in to save the record in the subform. The calculation process in the main form should work after that.
Mark EdwardsChief Technology Officer

Commented:
For those of us who have TESTED the On Delete event, we have found that it fires BEFORE the record is deleted, so any recalculation will INCLUDE the record that is about to be deleted.
We tested this by putting in a message box that displayed the recordset recordcount - which was the same as the count BEFORE the delete button was pressed.

Only the After Confirm Delete fires AFTER the record is deleted - the recordset recordcount showed the proper after-delete record count.  It is important that the record is deleted first, before the recalculation on the remaining records.
DexterFanDatabase Developer

Commented:
Thank you Mark Edwards for that clarification.
PatHartman
Distinguished Expert 2017

Commented:

Mark has given you the event you need to use but I'm pretty sure you know that you shouldn't be storing a calculated value.  Wouldn't it be better to do the calculation on the fly?  You can make a tiny subform that shows only a single field (the calculated value).  Remove all the borders and other properties that make the subform look like a subform.  Then you can requery this subform in the After Delete Confirm event.

Eddie Antar

Author

Commented:
Hi all, thank you.

I'll explore the After Delete Confirm event. Yes Mark, I haven't explored Delete Confirmation in a while but from what I remember, I didn't like the messaging that was popping up. I have a kludgy solution working right now, where, from the On Delete, I do the calculation on the table BUT I subtract the value of the record that's being deleted.

But I will definitely be exploring After Del Conf to see if it's cleaner. THANK YOU!


Eddie

PS. No, Pat, I'm not storing the calculation. I've sued the Sub Form method other times, but in this case, the Tex Box is a better solution. Thanks.
Eddie Antar

Author

Commented:
Thanks again, everyone.