How to determine which records are being deleted?

Question

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.

@CPerkins I have completely rewritten my question as it was a little confusing. I hope it's better now. — Combinatix
Can't you use a custom button with click event to delete? You can then itarate over the selected records and keep the IDs — Siyon DP
@SiyonDP - It is complicated, see stackoverflow.com/questions/1669946/… . Also, I don't want to develop my own code for something that is built-in. Maybe I'm just missing something — Combinatix
@Combinatix You are not missing anything. The Access data model often has incomplete methods and events. One must perform stupid juggling acts to capture and perform actions on rows selected in the interface. BTW, MS docs notes that AfterDelConfirm can be suppressed in the Access Options. So by setting a UI option, it can thwart the capability of the developer to properly handle data events! — C Perkins

Combinatix Combinatix · Accepted Answer · 2019-04-07T11:36:19

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

How to determine which records are being deleted?

1 Answers