Delete Record Button - Changes record before Confirmation Box

[unislick]

I have created a fairly basic form from one table. It contains information to add, edit or delete records with Name & Address information.

I have created a combo box that query's the table based on ID (unique identifier) and First & Last Name. This combo box allows the user to pick a current record that exists in the table and edit or delete this particular record.

My problem - I have created a delete button and whenever I attempt to delete a record it will jump to another record in the table when the delete confirmation box appears. As a result, before clicking YES on the confirmation box that says you are about to delete one record, the form jumps to another record NOT the one the user wishes to delete. The correct record will ultimately be deleted but to the user it appears they are deleting a different record than they intended to delete.

IS THERE ANYWAY TO HAVE THE FORM STAY WITH THE CORRECT RECORD WHEN THE DELETE BUTTON IS CLICKED?

The underlying VBA code is provided below for the delete button. Thanks
Private Sub Command82_Click()
On Error GoTo Err_Command82_Click


DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord

Exit_Command82_Click:
Exit Sub

Err_Command82_Click:
MsgBox Err.Description
Resume Exit_Command82_Click


End Sub

 

[TKaminski]

Try using "ECHO OFF"

as in:

Echo False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Echo True

This will stop the screen from being updated until the code reaches "ECHO TRUE".

Whenever setting "ECHO FALSE", Be sure to always set "ECHO" back to "TRUE" otherwise the screen will never be redrawn and it will look like the program locked up. Also, remember to set "ECHO TRUE" in your error handling in case the code misbehaves while the screen is not being redrawn.

Setting ECHO FALSE can also speed up some actions since the computer doesn't have to spend resources redrawing the screen during long code actions.


Hope this helps.

Tom Kaminski

 

[unislick]

Worked great!! Thanks for the Help Tom!! Modified code looks like this:

Private Sub Command82_Click()
On Error GoTo Err_Command82_Click

Echo False
DoCmd.RunCommand acCmdSelectRecord
DoCmd.RunCommand acCmdDeleteRecord
Echo True
DoCmd.GoToRecord , , acFirst
Exit_Command82_Click:
Exit Sub

Err_Command82_Click:
Echo True
MsgBox "Delete process stopped by user. The record was not deleted.", 0, "Deletion Unsuccessful"
Resume Exit_Command82_Click
End Sub