Understanding how Access deletes records

It is common to run across questions about how Access handle deletions, particularly from the VBA code. The Access form provides 3 events around the deletions but they are easily misunderstood. I attribute this to how the events are presented in the Property Sheet’s Events tab:

If all we did was just look at this picture, one can understandably conclude that the sequence of the events ought to be as follows:

Before Del Confirm -> Delete -> After Del Confirm

Seems reasonable. Except it’s WRONG! You’d have to go to the Office help article and find the following paragraph:

Deleting records

When you delete a record, the following events occur for the form, and Microsoft Office Access displays a dialog box asking you to confirm the deletion:

Delete -> BeforeDelConfirm -> AfterDelConfirm

If you cancel the Delete event, the BeforeDelConfirm and AfterDelConfirm events do not occur, and the dialog box is not displayed.

This may seem like a weird order of events, but if we think it through it may start to make sense. Let’s look at Access’ behavior, without involving any custom code at all. We’ll use a datasheet, which will help a lot in illustrating how the events work as it won’t be obvious from a single form. Let’s use Northwind’s “Customer List” form as an example, which is a datasheet form, and then set up event procedures for each event:

Private Sub Form_AfterDelConfirm(Status As Integer)
    Debug.Print "AfterDelConfirm", Me.ID.Value, Status
End Sub

Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
    Debug.Print "BeforeDelConfirm", Me.ID.Value, Cancel, Response
End Sub

Private Sub Form_Delete(Cancel As Integer)
    Debug.Print "Delete", Me.ID.Value
End Sub

Let’s then do the deletions. The video below has the immediate window visible while we work with a datasheet form to report the sequence of events.

When we delete just one record, we get the following output (formatted for readability):

Delete            30 
BeforeDelConfirm  31  False              acDataErrDisplay 
AfterDelConfirm   31  acDeleteOK

Two things to note:

1. When the Delete event runs, we are on ID #30, the record being deleted. However, once we reach the BeforeDelConfirm event, Access has already moved the pointer to ID #31 (e.g. the next record). Even though we no longer see the ID #30 still on the screen, by the time the dialog has shown, it’s as if it was already deleted. It’s in a transaction and isn’t actually gone even though we don’t see it. That can impact your VBA code.
2. The record only gets removed from the record set entirely if you click OK on the dialog, which the AfterDelConfirm event then runs.

Let’s try again but delete 3 records instead of one. The output for that portion (formatted) is as follows:

Delete            31 
Delete            32 
Delete            33 
BeforeDelConfirm  34  False              acDataErrDisplay  
AfterDelConfirm   31  acDeleteUserCancel

In this case, we clicked “No” on the dialog, cancelling the deletion. However as seen, by time we click “No”, both Delete event and BeforeDelConfirm event already have run. More importantly, we get a Delete event fired for each record being deleted, so we see it 3 times, processing IDs #31, #32 and #33 before we land on the ID #34 in the BeforeDelConfirm event.

The only way for us to know if the user clicked “No” is to look at the Response of the AfterDelConfirm, which maps to acDeleteUserCancel (aka integer value 2). This is also different from other “after” events such as AfterUpdate which simply don’t fire at all if we cancel the update via the BeforeUpdate event. For that reason, if the BeforeDelConfirm event fires, you can expect the AfterDelConfirm event to eventually follow.

If we repeat the operations but allow the deletion to proceed, we get the following output (again, formatted):

Delete            31 
Delete            32 
Delete            33 
BeforeDelConfirm  34  False       acDataErrDisplay   
AfterDelConfirm   34  acDeleteOK

I want to call your attention to the fact that the pointer moved back to ID #31 because we had cancelled the previous attempt, which effectively restores the record back into the form’s record set and reset the current record pointer. Thus, it repeats the same sequence of Delete events and BeforeDelConfirm events we had before. Because we click “Yes” this time, we got back an acDeleteOK (aka 0) for the AfterDelConfirm event.

Now let’s change the code to cancel the deletion unconditionally:

Private Sub Form_Delete(Cancel As Integer)
    Debug.Print "Delete", Me.ID.Value
    Cancel = True
End Sub

And try to delete multiple records. The output would be then as follows:

Delete            34 
Delete            35 
Delete            36

In this case, the Delete events get fired. However, because all had Cancel set to true, and therefore were cancelled, we ended up with no deletions and therefore BeforeDelConfirm event never gets fired at all. Just to demonstrate this, let’s modify the Delete event handler with the following silly code:

Private Sub Form_Delete(Cancel As Integer)
    If Me.ID.Value Mod 2 <> 1 Then
      Cancel = True
    End If
    Debug.Print "Delete", Me.ID.Value, Cancel
End Sub

And retry the deletions of the 3 records. We’ll see the following formatted output:

Delete            34  True 
Delete            35  False 
Delete            36  True 
BeforeDelConfirm  34  False               acDataErrDisplay    
AfterDelConfirm   34  acDeleteUserCancel

As you see, even though we deleted only one records out of the 3 records, we could then proceed with the BeforeDelConfirm event, and we would then see this on the display:

You can see that only ID #35 gets deleted. Also note that because we did not delete the ID #34, the record pointer remains on the same record rather than moving forward. That shows that we cannot rely on the pointer being moved in all the scenarios we deal with the deletions and therefore should not rely on its position. However, we can capture the pointer on the Delete event to tell us which records are being considered for the deletion. Once we reach the BeforeDelConfirm event, we no longer can assume that the pointer reported will be the one that’s after the last selected record being deleted.

Takeaway

From the above experiments, we can draw the following conclusions:

• Both BeforeDelConfirm and AfterDelConfirm events will only fire only if there is at least a single Delete event with its Cancel set to false. If all Delete events are cancelled, nothing else will happen.
• The AfterDelConfirm event will always fire if the BeforeDelConfirm event has fired, unlike other after events which may not fire at all. This can be then used to confirm whether the deletions were cancelled or executed successfully.
• We should treat the current record position as undefined after handling the Delete event. Therefore, any code that relies on being on a certain record may be suspect
• If the form can be exposed either as datasheet or continuous form, then additional design is necessary to properly handle multiple-row deletions. If the form is always shown as a single-record form, it may be possible to handle all the custom logic within the Delete event alone but that will become annoying on the datasheet/continuous form configuration.

It is possible to handle the deletions using our custom dialogs, but this will require some careful planning, especially for datasheets or continuous form where multiple selections and therefore multiple deletions is possible. Hopefully, this article has helped clarify the sequence and behavior of the deletion events.