CODEWITHSUNDEEP
ms-accessvba
Combinatix
Combinatix

Reputation: 1216

How to determine which records are being deleted?

Edited - As Leif asked here: When a user deletes multiple records from datasheet view in form I want to be able to determine, within the VBA event routine, which records are being deleted. I need this information to delete some associated files on the file server. If only one record is being deleted I don't have a problem, since it will be the current record. But what if multiple records are being deleted? Is this list available somewhere within the VBA event procedure?

The problem with Form_Delete event is that it's fired when MS Access tries to delete selected rows, and if that fails due something (relations, etc) then it pops up Error Message and/or confirmation dialog. If a user press Cancel, then deleted records "magically" come back. I did not find the way how to trace what was actually deleted and what wasn't.

Form_AfterDelConfirm runs once after confirmation, with no access to deleted records collection

All I need is a collection of record ID's that was really deleted.

Upvotes: 2

Views: 695

Answers (1)

Combinatix
Combinatix

Reputation: 1216

Store all possibly deleted records in a collection and then compare against form's recordset:

Private PossiblyDeleted as New Collection   'Module level collection

Private Sub Form_Delete(Cancel As Integer)
    'If collection doesn't exist it is created
    PossiblyDeleted.Add Str(Me!ID)   'Store every ID
End Sub

Private Sub Form_AfterDelConfirm(Status As Integer)
    If Status = 0 Then         'If Yes pressed
        Dim RecID As Variant
        Dim RS As Recordset
        Set RS = Me.Recordset.Clone
        For Each RecID In PossiblyDeleted    'loop through collection
            RS.FindFirst ("ID = " & RecID)
            If RS.NoMatch Then               'and see what was deleted
                'Perform the action here
                Debug.Print RecID
            End If
        Next
    End If
    Set PossiblyDeleted = Nothing   'Delete collection
    Set RS = Nothing
End Sub

As C Perkins noted above - this might not work when the user disable confirmations. I guess it can be enabled with DoCmd.SetWarnings True

Upvotes: 2

Related Questions