detect record delete in vba without dialog box

[uplate]

I've looked all over this forum and others and google, and am amazed that I've found nothing that addresses this issue.

I want to execute some code after a record has been deleted. If I uncheck the "Tools / Options / Confirm Record changes" option (it's extremely disruptive) then the "Before/After Del Confirm" events don't occur. The "On Delete" event occurs before the record is deleted and therefore doesn't help me.

Are the "Before/After Del Confirm" events the only events that occur after record deletion? If so, is there any way to disable the confirmation dialog and still get the events so I can handle it with my code and not disrupt the user every single time a record is deleted?

Thanks...

 

[gemma-the-husky]

Tricky issue isn't it?

I agree that the confirm actionqueries messages is very disruptive - furthermore your users can cancel actions with this, that shouldn't be cancelled.

What are you trying to do after the delete? If you are deleting the current record, presumably you will get a different current record, so presumably you could use the oncurrent event, although clearly that would fire every time, with or without a delete.

 

[uplate]

Thanks for your suggestion.

I didn't want to use the "On Current" event, since it is called all the time for other reasons, but if it is the only event that reliably fires after a deletion, then I guess I really have no other choice. Are there really no better choices?

So, reluctantly, I created a module level flag, set it in Form_Delete(), and checked and cleared it in Form_Current(). I couldn't think of any better ways to control the execution of my "after deletion" code.

Private Sub Form_Current()
...
If m_deleting Then
m_deleting = False
' Here is where I put the code I wanted to execute after deletion.
End If
...
End Sub

Private Sub Form_Delete(Cancel As Integer)
...
m_deleting = True
...
End Sub

Thanks again for your help, I appreciate it greatly, and now I have a means of executing after deletion code.

 

[uplate]

follow up

Actually, that didn't entirely work. It appears to work at first, but on closer inspection, it's not truly working.

The problem is, that even though my code was called after the record visually disappears from the form, and even though the form's RecordCount had decreased, the underlying table still had not been changed. So the true deletion hasn't happened at the time the Form_Current() is called - only a deletion from a buffer copy of the table has been done. Presumably, Access calls Form_Delete(), then Form_Current(), and then finally updates the actual table to reflect the changes after all of these have been called. Since the actual deletion from the table doesn't occur until after Form_Current(), then these event handlers aren't useful for trapping deletions from the underlying table.

Why do I care about the underlying table instead of the form's working copy of the recordset? Well, other parts of the application open recordsets that look at the same table and I want them to see the deletion as soon as it's done.

Therefore it's necessary to force early updating, and I discovered that Me.Requery does the trick!

So I modified my code:

Private Sub Form_Current()
...
If m_deleting Then
m_deleting = False
Me.Requery ' This forces the working copy of the recordset to be written to the underlying table.
' Here is where I put the code I wanted to execute after deletion.
End If
...
End Sub

Cheers...