Why am I getting a run-time error 3021 (no current record) when trying to delete the current record from an Access form?

Question

I have the following VBA code attached to the click event of a 'Cancel' button on my main form. The intention is to delete all the records in the 'entities_subform' before then deleting the related record in the main form.

However, though the entities in the subform are successfully deleted, the main form record is not deleted due to a Run time error 3021 (No current record).

What do I need to do to effectively reset the focus on the main form again to make this work? Before I added the code to delete the records from the entities subform, the command acCmdDeleteRecord was working fine for the main form. I already tried inserting the line Me.SetFocus before the acCmdDeleteRecord below, but this did not make any difference.

Private Sub Cancel_New_Record_Click()
    If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then

        'first we need to delete all the entities in the subform, to prevent orphans being left behind
        entities_subform.SetFocus

        Dim entityRecSet As Recordset
        Set entityRecSet = entities_subform.Form.Recordset.Clone()
        entityRecSet.Delete
        entityRecSet.Close
        Set entityRecSet = Nothing

        'now we can delete the check record
        DoCmd.RunCommand acCmdDeleteRecord
        DoCmd.Close acForm, "checks"
        DoCmd.OpenForm "menu"
    End If

End Sub

Edit: For now I have been able to achieve the functionality I was looking for by using the following instead:

Private Sub Cancel_New_Record_Click()
    '--------------------------------------------------
    'deletes the newly created record from the database
    '--------------------------------------------------
    If MsgBox("Are you sure you want to delete this record?", vbYesNo) = vbYes Then

        'grab the id of the current check record for later
        Dim checkID As Integer
        checkID = Me.Check_ID

        'delete the current check record
        DoCmd.RunCommand acCmdDeleteRecord

        'now delete any orphan entities from the entity table
        CurrentDb.Execute "DELETE * FROM entities WHERE entities.[Check ID] = " & checkID & ";"

        'close the form and return to the menu
        DoCmd.Close acForm, "checks"
        DoCmd.OpenForm "menu"
    End If

End Sub

Gustav Gustav · Accepted Answer · 2017-09-08T13:43:35

First, this just creates a clone of your records:

Set entityRecSet = entities_subform.Form.Recordset.Clone()

Then, this just deletes the first record of the clone and closes the clone:

entityRecSet.Delete
entityRecSet.Close

Thus, your records are left intact.

What you could and should do is to set up Referential Integrity between the two tables. Then, when deleting a master record, all child records will be deleted automatically.

Why am I getting a run-time error 3021 (no current record) when trying to delete the current record from an Access form?

1 Answers