Repost: DoCmd.RunCommand acCmdDeleteRecord does nothing.

[vainglory]

Hi,

Sorry for the repost, but I didn't get any comments regarding this:

I was revising my code as recommended to avoid using:

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

and use:

DoCmd.RunCommand acCmdDeleteRecord

But as soon as I did, I noticed that my delete button seemed to die.
That is, whenever I violate referential integrity, or attempt to delete

a record that has nulls in a required field or primary key or has
duplicates in a primary key I get no error whatsoever! In fact,
nothing happens! Is this normal? I don't even get warning message
even though warnings is turned on.

Then, I replaced:

DoCmd.RunCommand acCmdDeleteRecord

with

DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

and I got my error messages back. But strangely, I still have no
warning
messages. Please I am dying of curiosity as to why this is happening.
Any ideas?

Thanks in advance,

Paolo

 

[Allen Browne]

You cannot delete the record before it is saved. The approaches you are
using attempt to save the record before it is deleted, and the messages
associated with the failed save differ (or don't exist.)

If you don't want the record, it makes sense to undo any edits in progress.
You can then delete the record if it exists:
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdDeleteRecord
End If

That gives no error if the record can't be saved because it is invalid, and
also avoids the error message if there is no record to delete. (Presumably
there is no point telling the user that the record cannot be saved, since
they want to be rid of it anyway.)

 

[Paolo]

Hi Allen,

Thanks for the tip as you can see I am very new to this. Further on
this topic:

I have a delete button which deletes records on a form. I use the
following code (with your advice). Unfortunately, if the user is going
to delete a record with a required field set to null, the form's error
message kicks in saying that the field needs a required value. Any
ideas as to how I can get the field to be undone and then deleted (if
it's not a new record)?

Thanks in advance,

Paolo


Private Sub Command2_Click()
On Error GoTo ErrHandler
Beep
If MsgBox("Are you sure you want to delete the record?", _
vbQuestion + vbYesNo, "Delete?") = vbYes Then
If Me.Dirty Then
Me.Undo
End If
If Not Me.NewRecord Then
DoCmd.RunCommand acCmdDeleteRecord
End If
End If
Command2_Click_Exit:
Exit Sub
ErrHandler:
Select Case Err.Number
Case 3200
MsgBox "Employee ID is referenced in another form!",
vbCritical, _
"Referential Integrity Error"
Me.Undo
Case Else
MsgBox "Error in Command2_Click()." & vbCrLf & vbCrLf & _
"Error #" & Err.Number & vbCrLf & vbCrLf &
Err.Description
End Select
Err.Clear
Resume Command2_Click_Exit
End Sub

 

[Allen Browne]

If you are seeing that error, it must be due to the fact the the control
currently contains an invalid or unacceptable entry (such as an incomplete
date, text where a number should be, or the entry back-spaced out in a
required control.) As a result, it never even starts to process the code in
the button, because they can't reach the button: they are stuck in the
control with the bad entry.

The simplest solution is to teach the user to press the Esc key to cancel
the entry they were making. If that's too difficult to remember, they can
press the Undo button on the toolbar, or choose Undo from the Edit menu.

Once they undo that control, they will be able to get to the button to run
its code.

If you think it would help, it is possible to build a custom toolbar and add
your own Undo/Delete button there. The user can get to the toolbar, since it
is not a button on their form.