avoid MS Access message

[eugz]

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

 

[RoyVidar]

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 +.

 

[geekay]

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

 

[jkl0]

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.

 

[boblarson]

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.

 

[eugz]

Thanks a lot very helpful.