Delete Events

Microsoft Access 2.0 adds two new Delete events, BeforeDelConfirm and AfterDelConfirm, that enable you to customize what the user sees on a delete attempt. You could also use the events to trigger other actions, such as deleting records in another table.

First, a quick description of the events.

Delete: No changes to this event from Microsoft Access 1.1. It gets fired for each selected record before it is deleted. This gives you the chance to examine each record being deleted. The event can be canceled. Canceling will abort the delete for this record only. If the delete isn't canceled, the record is deleted from Microsoft Access (but kept in an undo buffer at the engine level).

BeforeDelConfirm: This event takes two parameters, Cancel and Response. It's fired after all the records are deleted from Microsoft Access (that is, after the sequence of Delete events) but before the Confirm Delete dialog box is shown. If this event is canceled, then the undo buffer will be used to restore the records in Microsoft Access. If this event is not canceled, then the undo buffer will be flushed and the records will stay deleted.

• Canceling this event (setting Cancel to nonzero) will abort the delete and prevent the Confirm dialog box from being displayed (but the AfterDelConfirm event will still fire).

• You can also suppress the dialog box (but still allow the delete) by setting Response to DATA_ERR_CONTINUE and Cancel to 0. This enables you to perform a silent delete or to present the user with your own confirmation dialog box.

• If Cancel is set to nonzero, then the Response argument is ignored. (It doesn't make sense to cancel the delete and still ask the user for confirmation!)

AfterDelConfirm: The event has one read-only parameter, Status. The event is fired after the delete is done. It can't be canceled. Status indicates the result of the delete: the delete worked OK, the programmer canceled the delete, or the user canceled the delete.

Custom Delete Message

In the first example, we will suppress the standard delete confirmation message and replace it with one that gives more information. In this case we have a form used when we fire employees at Northwind Traders. The forms to look at are "ConfirmDelete" and "Fire Employee." The code of interest is in the form module of "Fire Employee."

First we have the code called for each record as it's deleted. We make sure a table to keep track of the deleted employees is ready, and then add the deleted employee's last name to that table.

Sub Form_Delete (Cancel As Integer)
Dim i As Integer

'Create temp table if not yet there
If (Not TempTableOpen) Then 
    'Point to the current database
    Set DB = DBEngine(0)(0)

    'Open the table and clear the current records in the table
    Set TempRS = DB.Tabledefs!TempEmp.OpenRecordset()
    TempRS.MoveFirst
    For i = 1 To TempRS.RecordCount
        TempRS.Delete
        TempRS.MoveNext
    Next i

    'Mark the table as open
    TempTableOpen = True
End If

'Add this employee to the temp table
TempRS.AddNew
TempRS("LastName") = [Last Name]
TempRS.Update
End Sub

With table TempEmp filled, we can now display a customized delete confirmation form to the user that gives the names of each fired employee and asks for confirmation. We use the form ConfirmDelete as a modal form to do this. After this form is closed, we read the user's response and cancel or confirm the delete based on this response.

Sub Form_BeforeDelConfirm (Cancel As Integer, Response As Integer)
'We want to use the names in the temp table to customize what
'we show the user. Do this with a popup modal form based on 
'the temp table. First close the table
TempRS.Close
TempTableOpen = False

'Open the confirmation form
DoCmd OpenForm "ConfirmDelete", A_NORMAL, , , A_READONLY, A_DIALOG

'Get the response to the cancel.
Cancel = GetFireResponse()

'Suppress the standard confirm dialog box
Response = DATA_ERRCONTINUE
End Sub

Finally we display the final message to the user to confirm what has occurred.

Sub Form_AfterDelConfirm (Status As Integer)
'Confirm the delete or the cancel of the delete
If (Status = DELETE_OK) Then
    MsgBox "The terminated employees will be issued pink slips", , "Termination Status"
Else
    MsgBox "Termination of employees canceled", , "Termination Status"
End If
End Sub