Eddie Antar
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
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
Have you tried requerying ?
ASKER
Yes. I just need the point at which I can query the database right AFTER the deletion.
Thanks,
Eddie
Thanks,
Eddie
There cases where it's done automatically..or it needs some "helping hand"
can you share a sample
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.
ASKER
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.
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.
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?
ASKER
Sorry. I'm clicking on the record selector in the subform and hitting the delete key.
Thanks
Thanks
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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.
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.
ASKER
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.
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.
ASKER
Thanks again, everyone.