ACC2000: Order of Form Delete Events Differs in ADPs and MDBs

When you delete records in a form in a Microsoft Access project (.adp), the order of events is different than when you delete records in a form in a Microsoft Access database (.mdb).

In a Microsoft Access project, the order of events when you delete records in a bound form is as follows: In a Microsoft Access database, the order of events when you delete records in a bound form is as follows:

Microsoft intentionally changed the order of deletion events in an Access project in order to avoid opening unnecessary transactions on Microsoft SQL Server.

An Access project issues commands to Microsoft SQL Server within transactions. In order to avoid opening unnecessary transactions on SQL Server, Microsoft Access prompts you to confirm the deletion before opening the transaction. If you confirm the deletion, Access opens a transaction on Microsoft SQL Server, issues the DELETE statement to delete the record(s), and fires the form's Delete event. If you click No when prompted to confirm the deletion, Microsoft Access does not open a transaction on Microsoft SQL Server to delete the record and does not fire the form's Delete event. The following sections explain these events in more detail.

BeforeDelConfirm Event

The BeforeDelConfirm event fires just before Access prompts you to confirm the deletion. The only exception to this is when you clear the Record Changes check box on the Edit/Find tab of the Options dialog box, available by clicking Options on the Tools menu. If you cancel the BeforeDelConfirm event, Access does not display the confirmation dialog box but still fires the AfterDelConfirm event afterward.

AfterDelConfirm Event

The AfterDelConfirm event fires after Access has prompted you to confirm the deletion, or if the BeforeDelConfirm event is cancelled. The only exception to this is when you clear the Record Changes check box on the Edit/Find tab of the Options dialog box, available by clicking Options on the Tools menu.

Delete Event

The Delete event fires after the AfterDelConfirm event, except in the following situations:

• You clicked No when prompted by Microsoft Access to confirm the deletion.
• The BeforeDelConfirm event was cancelled programmatically by setting its Cancel argument to True.

If you clear the Record Changes check box on the Edit/Find tab of the Options dialog box, the BeforeDelConfirm and AfterDelConfirm events do not fire; also, the Delete event fires immediately after you try to delete one or more records.

Steps to Reproduce Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

1. Open the sample database Northwind.mdb.
2. In the Database window, click Tables under Objects, and then click to select the Customers table.
3. On the Edit menu, click Copy.
4. On the Edit menu, click Paste.
5. In the Paste Table As dialog box, type MyCustomers in the Table name box.
6. Select the Structure and Data option under Paste Options, and then click OK.
7. Create a new form based on the MyCustomers table in Design view.
8. Add all fields to the form.
9. On the View menu, click Code.
10. Add the following code to the form:

    Option Compare Database
    Option Explicit
    
    Private Sub Form_AfterDelConfirm(Status As Integer)
       MsgBox "AfterDelConfirm"
    End Sub
    
    Private Sub Form_BeforeDelConfirm(Cancel As Integer, Response As Integer)
       MsgBox "BeforeDelconfirm"
    End Sub
    
    Private Sub Form_Delete(Cancel As Integer)
       MsgBox "Delete"
    End Sub
    					

11. Open the form in Form view and try to delete a record. Note that the Delete event fires, followed by the BeforeDelConfirm and AfterDelConfirm events respectively.
    
    
12. Close the Northwind database.
13. Open the sample Access project NorthwindCS.ADP.
14. Repeat steps 2 through 6 to create a copy of the Customers table.
15. Open the Customers table in Design view.
16. Set the CustomerID field as the Primary Key, and then close and save the table.
17. Repeat steps 7 through 11 to create and test a form based on the MyCustomers table with code on the BeforeDelConfirm, AfterDelConfirm, and Delete events.
    
    Note that the BeforeDelConfirm event fires, followed by the AfterDelConfirm and Delete events respectively.

For more information about the order of events, click Microsoft Access Help on the Help menu, type order of events in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.