Delete Event in VBA (Access 2003 SP1)

Topic

New Reply

I have the following event procedures set for the Delete, BeforeDelConfirm and AfterDelConfirm events in a form.

Private Sub Form_AfterDelConfirm(Status As Integer)
MsgBox “Deletion completed”
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
Response = acDataErrContinue
End Sub

Private Sub Form_Delete(Cancel As Integer)
If MsgBox(“Do you want to delete?”, vbYesNo) = vbNo Then Cancel = True
End Sub

When I delete a record from the form, I encounter the following sequence of events (breakpoints are set on each of the three lines of actual code, to help track progress).

– When Delete is clicked, the message box appears, and No is clicked.
– After completing the Form_Delete procedure, there is no attempt to proceed to the Form_BeforeDelConfirm procedure (this is behaving as I would expect it to.)
– If I again click Delete, the Form_BeforeDelConfirm procedure is immediately initiated, and proceeds to the Form_AfterDelConfirm procedure, before THEN running the new Form_Delete procedure.
– No records are actually deleted during this process.
– This occurs even if I have moved to a new record, but not if I have closed and re-opened the form.

As I understand the Help file, if Cancel is set to True in the Delete procedure, the BeforeDelConfirm and AfterDelConfirm procedures will not run. That is what I want; however, the way it is working now will create a somewhat misleading sequence of messages.

I encountered the problem in a live database, but the code given above is a simplified version set up in an otherwise empty database that has a single table with an autonumber field, a text field and an integer field, with an auto-generated form providing the interface. There are no other objects or code, but the problem remains the same. The same problem occurs if I remove the breakpoints and let it run freely.

I know I can get around the problem by using a Private variable that flags whether to run the Before and After procedures, but would really like to know if I am doing something wrong, or if this is a bug in Access 2003 VBA.

Thanks.

Reply | Quote

Replies

Remove the code in the BeforeDelConfirm event. It have nothing to do there. You just need the code in the AfterDelConfirm event and in the Form_Delete event

Reply | Quote

Ummm – but isn’t that the only way to suppressthe generic “You are about to delete 1 record…” prompt? The BeforeDelConfirm event is the only one of the three that has the Response argument that allows the use of the acDataErrContinue constant.

(And I should perhaps have pointed out that the AfterDelConfirm event in the live database actually goes on to do more than issue a message box.)

Reply | Quote

For that you use the DoCmd.SetWarnings statement :

Private Sub Form_AfterDelConfirm(Status As Integer)
   DoCmd.SetWarnings True
   MsgBox "Deletion complete"
End Sub

Private Sub Form_Delete(Cancel As Integer)
   DoCmd.SetWarnings False
   If MsgBox("Do you want to delete?", vbYesNo) = vbNo Then Cancel = True
End Sub

Reply | Quote

Thanks, yes, that does seem to work without the problem that I originally encountered after cancelling.

However, I still feel that my original code conformed with the instructions given in the Help file for suppressing the generic message box, so although I now have two workarounds (and yours is less clumsy than the one I had worked out), I still feel that there is a bit of a gap between Microsoft’s intentions and reality!

Thanks for your help.

Reply | Quote

I’d ask the question in the BeforeDelConfirm event:

Private Sub Form_AfterDelConfirm(Status As Integer)
MsgBox “Deletion completed”
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
If MsgBox(“Do you want to delete?”, vbYesNo) = vbNo Then Cancel = True
Response = acDataErrContinue
End Sub

Reply | Quote

Thanks, Hans.

The reason I’m asking in the Delete event is because I need to get some information about the record being deleted, which is lost by the time I get to the BeforeDelConfirm event. I guess I could gather the data regardless, and then ask the question in the BeforeDelConfirm event, but it just seemed more efficient not to bother doing so if the delete wasn’t going ahead.

Anyway, I now have plenty of workarounds to choose from!

Reply | Quote