Link to home
Start Free TrialLog in
Avatar of Eddie Antar
Eddie AntarFlag for United States of America

asked on

MS Access How to detect when a record is actually deleted

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
Avatar of John Tsioumpris
John Tsioumpris
Flag of Greece image

Have you tried requerying ?
Avatar of Eddie Antar

ASKER

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

Thanks,
Eddie
There cases where it's done automatically..or it needs some "helping hand"
can you share a sample
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.
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.
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.
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?
Sorry. I'm clicking on the record selector in the subform and hitting the delete key.

Thanks
ASKER CERTIFIED SOLUTION
Avatar of Mark Edwards
Mark Edwards
Flag of United States of America image

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
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.
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.
Thank you Mark Edwards for that clarification.

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.

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.
Thanks again, everyone.