Access VBA: object invalid or no longer set

Question

I get the following error:

object invalid or no longer set

when my code is running the following string:

DoCmd.RunCommand acCmdDeleteRecord

According to me, I get this error because there is no record selected or the record selected is Nothing.

How can I add a check before executing the code? It would be great to have something like:

If SelectedRecord != Nothing then
   DoCmd.RunCommand acCmdDeleteRecord
End If

Erik A Erik A · Accepted Answer · 2018-01-13T11:30:04

There are multiple ways to go about this.

The easiest way is just to turn on control wizards, and choose records operations -> delete record. That generates an embedded macro. You can just use it, or use it as a reference for the required steps.

A fully VBA solution is the following one:

Dim rs As Recordset: Set rs = Me.Recordset
With rs
    If Not rs.Updatable Then
        'RS is not updateable, delete will fail
        Exit Sub
    End If
    If Not .EOF And Not .BOF Then '0 records
        If Me.NewRecord Then
            If Not Me.Dirty Then
                'Do nothing, unchanged new record so won't be saved
            Else
                'Revert changes on new record
                Me.Undo
            End If
        Else
            .Delete
        End If
    End If
End With

Note that I'm purposefully not using DoCmd.RunCommand acCmdDeleteRecord. This is because I want to be able to specify on which form (e.g. subform) I'm deleting the record.

Access VBA: object invalid or no longer set

1 Answers