Microsoft KB Archive/224333

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

Article ID: 224333

Article Last Modified on 1/29/2007

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q224333



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

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



INTRODUCTION
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).



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

234866 Order of form delete events differs in ADPs and MDBs

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:  Open the sample project file NorthwindCS.adp. Create a new, blank form that is based on the Customers table in Design view. Add all the fields from the Customers table to the form. On the View menu, click Code to view the module of the form.  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  Save the form as frmCustomers, and then close it.</li> Open the form in Form view. Make sure that the form is on the first record (CustomerID = ALFKI).</li> On the Edit menu, click Delete Record.</li> 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.

</li> Click OK.</li> Add several new records to the form, but do not add related records in the Orders table.</li> Try to delete one or more of the newly added records. Notice that you receive the message:

The deletion was successful.

</li></ol>

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: <ol>  In the OnEnter event of the subform object, add the following code. Set rsADO = Me!<SubformControl>.Form.RecordSet Note  is a placeholder for the name of the subform object that appears on the main form. </li>  In the OnExit event of the subform object, add the following code. Set rsADO = Me.RecordSet </li>  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 </li></ol>

Additional query words: inf test check ACC2000

Keywords: kbclientserver kbhowto KB224333

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.