Hi All.
I have form with command button to delete record. All work fine except after I receive my confirm to delete a record I'm goting MS Access confirm like:
"You are about to delete 1 row(s) from the specified table. One you click Yes, you can't the Undo command to reverse the changes".
How to avoid that confirm and keep only my? Code which I use look like:
Private Sub cmdDelete_Click()
Dim Sql As String

If MsgBox("Do you wish to delete this record?", vbYesNo, "Delete Confirmation") = vbYes Then
If MsgBox("Are you SURE you want to delete this record?" & vbCrLf & _
"This will permanently delete the record.", vbYesNo, "2nd Delete Confirmation") = vbYes Then
Sql = "DELETE FROM Inventory WHERE InventoryID = " + lstRequest.Column(0)
DeleteListItem Me.lstRequest, Sql

End If
End If

DoCmd.RunSQL Sql

End Sub

Execute on the database object in stead (or if you favour ADO, execute on the connection)

CurrentDB.Execute SQL, dbFailOnError

BTW - for regular concatenation, the ampersand (&) is the operator to use, not +.

Add two lines of code one before and the other after your Do Command statement as below

Application.SetOption "Confirm Action Queries", False

DoCmd.RunSQL Sql

Application.SetOption "Confirm Action Queries", True

You might also want to add an error handler so that you can set the confirmation back to true if there is an unexpected error.

Add two lines of code one before and the other after your Do Command statement as below

Application.SetOption "Confirm Action Queries", False

DoCmd.RunSQL Sql

Application.SetOption "Confirm Action Queries", True


While that may work, I think I prefer Roy's code as it is one line of code compared to three lines, and I try to make my code as efficient as possible, so one line beats three lines. Plus, the CurrentDb.Execute method also allows for rollback on failure where DoCmd.RunSQL does not.

Thanks a lot very helpful.