Case Study

The issue
A form contained several combo boxes linked to tables, sub-forms, and a delete button intended to delete the current record.  The client noticed while deleting records that occasionally one of the combo box linked records would get deleted.  After further investigation, we found that when the combo box was active and selected, then the delete button was pressed, the code used in the delete button would actually delete the record from the combo box.  In this case it was deleting a salesperson from the system and we would have to retrieve the salesperson record from a backup copy of the database back end.

The solution
The delete button was simply running the following code:

    If Me.NewRecord And Me.Dirty Then
        DoCmd.RunCommand acCmdUndo
    ElseIf Not Me.NewRecord Then
        DoCmd.RunCommand acCmdDeleteRecord
    End If

Instead of using DoCmd which is the code equivalent of pressing the delete key on the keyboard or selecting Record->Delete Record from the Access menus, we deleted the record ourselves using a SQL Delete statement from the CurrentDb.Execute function.  Then to refresh the form recordsource, we refreshed it using a common VBA method:

    If Me.NewRecord And Me.Dirty Then
        DoCmd.RunCommand acCmdUndo
    ElseIf Not Me.NewRecord Then
        CurrentDb.Execute (“DELETE * FROM tblRecords WHERE [ID] = ” & Me.ID)
        Me.RecordSource = Me.RecordSource
    End If

This does require you to know the name of the table and the primary key that is being used by the form to display the record.