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

asked on

MS Access: How can you detect deletions after they are done, while NOT having Confirm Record Changes set

Hi Experts,

I have a situation where when all the records in a subform are deleted, I need to do things on the main form. Therefore I need to detect when a subform has been emptied, right when it happens.

I must keep Confirm Record Changes set to false in my Options, so After Delete Confirm is not available to me. On Delete fires for each deletions so it's not useful when a group of records has been deleted.

Anyone have any ideas on how to work this???

I'd appreciate it.
Avatar of PatHartman
PatHartman
Flag of United States of America image

There is no event that can be used for this.  As you can see, the form level events in the subform, fire for each delete but no parent form event will fire.  What are you trying to capture?
Avatar of Eddie Antar

ASKER

I just want to change a control value on the main form the moment that a user deletes all the records in a subform.
ASKER CERTIFIED SOLUTION
Avatar of Anders Ebro (Microsoft MVP)
Anders Ebro (Microsoft MVP)
Flag of Denmark 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
"all the records in a subform."
Do you litterally mean ALL the records, or any records?
And seems to me you could use the Form After Delete Confirm event in the subform to - at that point - perform some action on the parent form.

User generated image
You would have to count the remaining records in the subform's AfterDeleteConfirm Event.  If there are no more records, then post a value in the parent form.   You can't "know" which record is the "last" record without counting what is left after each delete.
"There is no event that can be used for this. "
But there is :-)
Problem is, the Before and AfterDeleteConfirm Events don't fire if you turn warnings off.

Another technique like the timer is to set a flag in the OnDelete, then check that in OnCurrent.

Jim.
But there is :-)
Joe please share with us, which event runs ONCE, After ALL  records have been deleted from a subform?  The subform events run after EACH record is deleted, not after ALL records have been deleted.  That is why I said you have to count every time the After Delete Confirm event runs.  Eventually, the count will be 0 and then you can post to the parent record (even though that violates  normal forms).
<<That is why I said you have to count every time the After Delete Confirm event runs.  >>

 Just to be clear, OnDelete fires for each record.   BeforeDeleteConfirm and AfterDeleteConfirm only fire once regardless of the number of records, and they only fire if warnings are turned on, which eantar said he could not live with.

Jim.
Hey guys, I appreciate the help, but as Jim said, I have all warnings turned off so I can't use After Delete Confirm. The timer is an interesting workaround. So is the flag idea. Kind of odd, but even the On Current doesn't fire after they all delete. Surprised that there is no event (that I know of) that fires once a group of records are deleted.

Right now, I'm putting it in the On Exit of the subform and explaining to my user that the stuff to take place on the main form will happen automatically when she leaves that box.

I'll leave this question open a little longer to see if anyone who has encountered this has another answer, then I'll close it.

Thanks.
<<and they only fire if warnings are turned on,>>

 It actually just hit me that I wasn't being clear here....it's not warnings being off,  but the option to confirm record changes is off.

 Which by the way, you can leave on and turn warnings off and you will get the AfterDeleteConfirm event (I just tried and much to my surprise, it worked - don't remember it that way).

Jim.
<<Kind of odd, but even the On Current doesn't fire after they all delete. >>

I went back and looked at what I did the last time I bumped into this and indeed, I did not use OnCurrent, but the OnClose of the form to refresh some records.   Sorry for the mis-direction.

 Assuming you don't want to mess with Confirm Record Changes and Set Warnings (turning them on/off as needed), then the only other way looks to be Ander's timer idea.

Jim.
Given the following code, the only messages I see are the message boxes I put in there.  If you remove the message boxes, you won't see any messages but the events still run.  I put the message boxes in the events so I could be sure the events were running.  I use two macros in all my applications and they are the two you see here.  I use a macro to turn warnings off and on  because I also set the hourglass as a reminder.  It is CRITICAL that you know when warnings are Off unless you have a death wish and always using my macro, I know that when warnings get turned off, the hourglass comes on and vice versa.  That way when I'm testing, if I ever break out of the code, I won't forget to turn warnings back on.

You can also use the Response argument to control what messages get displayed if any.

Joe, turns out you are correct.  I thought the Before and After DelConfirm ran for EACH delete but they don't.  They only run once no matter how many records were selected.

Option Compare Database
Option Explicit

Private Sub Form_AfterDelConfirm(Status As Integer)
    MsgBox "after delete confirm"
    If DCount("*", "your table", "criteris") = 0 Then
        '''update main form
    End If
    DoCmd.RunMacro "mWarningsOn"
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    MsgBox "before delete confirm"
End Sub

Private Sub Form_Delete(Cancel As Integer)
    MsgBox "on delete"
    DoCmd.RunMacro "mWarningsOff"
End Sub

Open in new window

<<Given the following code, the only messages I see are the message boxes I put in there.  If you remove the message boxes, you won't see any messages but the events still run.  >>

 As long as confirm record changes is on, yes that works.   But if it's off, Before and AfterDeleteConfirm won't fire.

Jim.
I agree.  But why would you ever turn confirm record changes off when you can control what messages are displayed?  In an application designed for other people where the interface is completely controlled, there is no reason to turn confirm record changes off since you can display them or not based on your code.  You can even display them for you while you are testing and suppress them for others.  You don't cut off your foot because you have an ingrown toenail, you take an asprin.  Knowing you are updating data is important.  If you don't want to bother the user when you are doing bulk actions, turn warnings off as you need to.

If the OP insists on turning the confirm record changes off, then the Dcount() has to go into the delete event.
<<I agree.  But why would you ever turn confirm record changes off when you can control what messages are displayed? >>

 Because you then have to control them everywhere.   You may also not have total control of the interface.  There's also the issue with leaving the dialog up; you're holding a lock on all the records until the user clicks something, which can cause concurrency issues.

 So it's not a one size fits all type of question here.    If he can live with the setting on, then the AfterDeleteConfirm can be used, but if not, then the timer is the best solution.

Jim.
Hi all, thanks for all the feedback. As a rule, I always turn off the Confirm messages. I use way too many queries that insert, update, delete and to have to constantly turn messages on and off would be a pain. I've never had an issue with this.

I'll tell you one thing I've considered is turning the Confirm setting on when the box is entered so that I could use the After Delete Confirmation event and then turning it back off on exiting the subform.

Anyway, right now I'm going to stick with doing the main form business On Exit. It seems cleanest.
Funny, I've been using Access since version 2.0 and I don't ever turn them off. I use the Response argument of BeforeDeleteConfirm to handle messages regarding deletes and I turn off warnings when I want to suppress Access messages during batch processing and I have never had any complaints.  I'm having trouble envisioning why this is such a burden.  I am very careful to minimize "messages" to the user because if they get too many the users become desensitized and blow by all of them without even reading them - sort of like the way congress passes bills.

The problem with turning them off in the database properties  is that is an all or nothing situation.  You can't selectively turn them back on because toggling the setting requires closing and reopening the app.

I think we're going to have to agree to disagree on this one.

Anyway, right now I'm going to stick with doing the main form business On Exit. It seems cleanest.

Open in new window

It isn't.  Far from it in fact.  The Close event runs only once when the form closes and so it would only check for the current record.  If your form allows scrolling and so can update multiple records, this will not work at all because only the last record (the one that was active when the form was closed) would be updated.  And besides, you are no longer able to update controls by the time the close event runs so at best, you would have to run a separate update query.
Hey Pat, thanks for your input. But I'm not sure we're communicating the same things.

I have my code in the On Exit event of the subform. The moment they move to another control outside the subform, or exit the main form, or even move to another record while in the subform, the On Exit fires. In fact, I can't think of a way that the On Exit doesn't fire. So the results I'm looking for always happen.

The user has no problem with not seeing it immediately, so it's all good.

E
Sorry, I thought you said "close".  I must have been focused on Jim's reply when I posted.  I need to clean my glasses.  Exit probably works be we are going to have to agree to disagree on turning the "Confirm Record Changes" off to begin with.  It's not like you have to turn warnings off and on around every step.  You turn them off at the beginning of the procedure and on at the end, 40 queries later.
<< You can't selectively turn them back on because toggling the setting requires closing and reopening the app.>>

 No, you can do it on the fly.

Jim.
When I do it, I get the "You must close and reopen the current database for the specified option to take effect" message.  Perhaps we're talking about different settings.

User generated image
Through VBA:

 Application.SetOption "Confirm Record Changes", False

does the trick for me.

Jim.
" I have all warnings turned off so"
As a side note, and something that has been said many times by most of us here .... this is not a good idea for a myriad of reasons.

@Pat ... there might have  been some confusion in my response.  I was just saying that you can detect ... in a subform ... that a record(s) have been deleted.
Hey Joe,

I mis-wrote. I DON'T have warnings turned off. I have Confirmation Messages turned off. I understand the very, VERY real dangers of having warnings turned off.

But I'm open to learning here. If there are reasons why Confirmation messages in Options should NOT be turned off, I'd like to understand why. I've been developing for over 15 years in Access and I've never had a problem with it, but I'm open to learning more about this if anyone has a better understanding.

Thanks,
E
Application.SetOption "Confirm Record Changes", False
Curious why using the GUI requires close/open but using code doesn't.  Too bad the Document Window Options don't work that way.
I leave them on because when I am doing stuff manually, I always want to know what is going on so I am quite OK with responding to messages.  When I write code to do stuff for the user, I normally don't want him to see the messages so I turn them off when I specifically want to hide them and leave them on when I don't.  As you can see, by having them off, you are using a less than optimal event in the case where you need to know.  The Exit event runs every time the focus moves from the subform to the main form whether or not the user actually did anything.  If you leave the messages on, you can use the AfterDeleteConfirm event which runs only if one or more records were deleted and since you only need to run the code when a record is deleted, it is more aesthetically pleasing to use a "delete" focused event rather than a general event since this a "delete" related requirement.  Since my background is mainframe, I am conscious of the "cost" of code and without being crazy about it, always attempt to do things efficiently.   In the greater scheme of things, it will hardly matter in an Access database running on a local computer but it does matter in an interactive CICS environment where I came from and where there may be thousands of concurrent users attempting to execute transactions at the same time making it very important that every transaction be as efficient as possible.  So, if only 10% of the time a record is deleted, you are running the DLookup() 100% of the time whereas I would run it only the 10% of the time where something might be relevant.  It's a mindset.  Like I said, I don't go crazy trying to optimize every single line of code but gross things like this just come naturally to me.  I don't even think about them.  I've studied the Access event model for years and have a better than average grasp of which event certain classes of code should be placed in for optimal effect.  Think of it as the "Access Way".  Use what Access gives you and try not to fight with it.
"I leave them on because when I am doing stuff manually, I always want to know what is going on so I am quite OK with responding to messages."
DITTO on that.  I've never ever had a need to turn them off.

If you use the Execute Method of CurrentDB (etc) for Action Queries, you can avoid all confirmation messages, not to mention trap errors you want to know about.
Joe,  The OP isn't running a delete query.  The user is selecting one or more rows in a form and pressing the delete button.
Pat ... I was just making a general comment about how to avoid turning off Confirmations :-)
Andres, i just tried your workaround and it works like a charm. Thank you

And thanks everyone for the additional information on setting the Confirms.