Delete Record process in VBA code

Topic

New Reply

Using Access 2003 (2000 format)

Behind a Delete button on a form, I have the following code

Code:

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Select Case MsgBox("  Do you really wish to" _
                   & vbCrLf & "   DELETE this record?" _
                   & vbCrLf & "" _
                   & vbCrLf & "This cannot be undone!" _
                   , vbYesNo Or vbExclamation Or vbDefaultButton1, "Delete check")
    Case vbYes
        GoTo DeleteProcess
    Case vbNo
        Exit Sub
End Select
    
DeleteProcess:
    DoCmd.SetWarnings False
    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
    DoCmd.SetWarnings True

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
    
End Sub

Is there any reason that should no longer work? It used to work.

If I select the record, and click on “Delete Record” from the Edit menu, it works there…but not in this VBA code.

I tried using coding

Code:

Docmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

That doesn’t work either. The message I get is “The Delete command is not available now.”

Tom

Reply | Quote

Replies

You can replace

DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70

with just the one line

DoCmd.RunCommand acCmdDeleteRecord

If you get an error message, there must be something else interfering. The code is OK, and I tested it successfully in a demo database. Could you post a stripped down and zipped copy of the database?

Reply | Quote

Hans has identified the issue – my suspicion is that because you are using numeric offsets instead of the intrinsic constants (acDelete and acSelectRecord) your edit menu has been modified in some way and either has commands added or deleted. Also note that DoMenuItem is no longer recommend because of these kinds of issues, and the RunCommand is now preferred.

Reply | Quote

Thanks, Hans and Wendell

I found the issue. It lies with the fact that the record I was trying to delete is on a subform. So once I added the following line to the code it worked fine.

Code:

Me.fsubNewGivings.SetFocus

I also changed to the RunCommand code as you suggested.

Interesting thing is that the secretary at the church – the user – reports that it works fine with her system without the SetFocus command. Strange.

Thanks again.

Tom

Reply | Quote