ACC2000: How to Track Successful Deletions on Bound ADP Forms

ID: Q224333

The information in this article applies to:

Microsoft Access 2000

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

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

SUMMARY

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

MORE INFORMATION

When a user deletes records in a form in a Microsoft Access project (ADP), you may need to determine if the deletion was successful. Just because the Delete event or the AfterDelConfirm event of a form is triggered, it doesn't mean that any records were successfully deleted. For instance, 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 additional information about the event order that occurs when deleting records in a Microsoft Access project, click the article number below to view the article in the Microsoft Knowledge Base:

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

By using the Microsoft ActiveX Data Objects (ADO) object model, you can determine if 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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: The sample code in this article uses Microsoft ActiveX Data Objects. For this code to run properly, you need to reference the Microsoft ActiveX Data Objects 2.1 Library.

How to Track ADO Recordset Events to Determine Successful Deletions

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

Open the sample project file NorthwindCS.adp.

Create a new, blank form 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.

Open the form in Form view. Make sure the form is on the first record (CustomerID = ALFKI).

On the Edit menu, click Delete Record.

Click Yes when Microsoft Access prompts you to confirm the deletion. Note that you receive the following message because deleting this record violates a foreign key constraint with the Orders table:

The deletion was not successful.

Click OK.

Add several new records to the form, but do not add related records in the Orders table.

Try to delete one or more of the newly added records. Note that you receive the message:

The deletion was successful.

Additional query words: inf test check

Keywords : kbdta AccessCS
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbhowto