How to track successful deletions on bound ADP forms in Access 2000

This article describes how to programmatically determine whether a user has successfully deleted one or more records in a bound form in a Microsoft Access 2000 project (ADP).

When a user deletes records in a form in a Microsoft Access project (ADP), you may have to determine whether the deletion was successful. Although the Delete event or the AfterDelConfirm event of a form is triggered, the records may not have been successfully deleted. For example, consider the following situation.

You are using the Delete event procedure of a form to run custom code whenever a user has successfully deleted one or more records on the form. However, the Delete event of the form is triggered whenever Microsoft Access issues the DELETE statement to Microsoft SQL Server, not when the records are actually deleted. If the user's deletion attempt is not successful for any reason (for example, when constraint violations occur), the Delete event of the form is still triggered because Microsoft Access issued the DELETE statement to Microsoft SQL Server.

For more information about the event order that occurs when you delete records in a Microsoft Access project, click the following article number to view the article in the Microsoft Knowledge Base: By using the Microsoft ActiveX Data Objects (ADO) object model, you can determine whether the deletion was successful. The ADO object model exposes an event model for recordset objects. In Microsoft Access 2000, developers can use the Recordset property of the form to track ADO events that occur.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure. However, they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements.

Note The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run correctly, you must reference the Microsoft ActiveX Data Objects 2.x Library (where 2.x is 2.1 or later). To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft ActiveX Data Objects 2.x Library check box is selected.

How to track ADO recordset events to determine successful deletions

To track the ADO recordset events of a form to determine whether the deletion attempt was successful, follow these steps:

1. Open the sample project file NorthwindCS.adp.
2. Create a new, blank form that is based on the Customers table in Design view.
3. Add all the fields from the Customers table to the form.
4. On the View menu, click Code to view the module of the form.
5. Add the following code to the module.

   Option Compare Database
   Option Explicit
   
   Private boolDeleteAttempt As Boolean
   Private WithEvents rsADO As ADODB.Recordset
   
   Public Property Get DeleteAttempt() As Boolean
       DeleteAttempt = boolDeleteAttempt
   End Property
   
   Public Property Let DeleteAttempt(boolDeleteAttemptIn As Boolean)
       boolDeleteAttempt = boolDeleteAttemptIn
   End Property
   
   Private Sub Form_Open(Cancel As Integer)
       Set rsADO = Me.Recordset
   End Sub
   
   Private Sub Form_Error(DataErr As Integer, Response As Integer)
       'Prevent Access from displaying any error messages.
       Response = acDataErrContinue
   End Sub
   
   Private Sub rsADO_RecordChangeComplete(ByVal adReason As _
       ADODB.EventReasonEnum, ByVal cRecords As Long, ByVal pError As _
       ADODB.Error, adStatus As ADODB.EventStatusEnum, ByVal pRecordset _
       As ADODB.Recordset)
   
       Select Case adReason
           Case adRsnDelete
               Me.DeleteAttempt = True
           Case adRsnUpdate
               If Me.DeleteAttempt = True Then
                   Select Case adStatus
                       Case adStatusOK
                           'The deletion was successful.
                           MsgBox "The deletion was successful."
                           '
                           'Write custom code here if deletion is successful.
                           '
                       Case adStatusErrorsOccurred
                           'The deletion was unsuccessful.
                           MsgBox "The deletion was not successful."
                           '
                           'Write custom code here if deletion is
                           'unsuccessful.
                   End Select
                   Me.DeleteAttempt = False
               End If
       End Select
   End Sub
   					

6. Save the form as frmCustomers, and then close it.
7. Open the form in Form view. Make sure that the form is on the first record (CustomerID = ALFKI).
8. On the Edit menu, click Delete Record.
9. Click Yes when Microsoft Access prompts you to confirm the deletion. Notice that you receive the following message because deleting this record violates a foreign key constraint with the Orders table:
   The deletion was not successful.
10. Click OK.
11. Add several new records to the form, but do not add related records in the Orders table.
12. Try to delete one or more of the newly added records. Notice that you receive the message:
    The deletion was successful.

If the form contains a subform, you may want to determine whether a user successfully deleted one or more records in the subform. To do this, follow these steps:

1. In the OnEnter event of the subform object, add the following code.

   Set rsADO = Me!<SubformControl>.Form.RecordSet

   Note <SubformControl> is a placeholder for the name of the subform object that appears on the main form.
2. In the OnExit event of the subform object, add the following code.

   Set rsADO = Me.RecordSet

3. Add the following code to the subform object.

   Private Sub Form_Error(DataErr As Integer, Response As Integer)
         'Prevent Access from displaying any error messages.
          Response = acDataErrContinue
   End Sub