Microsoft KB Archive/234866

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

Article ID: 234866

Article Last Modified on 7/14/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q234866



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).



SYMPTOMS
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:

BeforeDelConfirm-->AfterDelConfirm-->Delete

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

Delete-->BeforeDelConfirm-->AfterDelConfirm



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



MORE INFORMATION
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.

 Open the sample database Northwind.mdb. In the Database window, click Tables under Objects, and then click to select the Customers table. On the Edit menu, click Copy. On the Edit menu, click Paste. In the Paste Table As dialog box, type MyCustomers in the Table name box. Select the Structure and Data option under Paste Options, and then click OK.</li> Create a new form based on the MyCustomers table in Design view.</li> Add all fields to the form.</li> On the View menu, click Code.</li>  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 </li> 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.

</li> Close the Northwind database.</li> Open the sample Access project NorthwindCS.ADP.</li> Repeat steps 2 through 6 to create a copy of the Customers table.</li> Open the Customers table in Design view.</li> Set the CustomerID field as the Primary Key, and then close and save the table.</li> 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.</li></ol>

<div class="references_section">