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
Microsoft Access
Last Comment
Eddie Antar
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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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
Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
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.
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
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.