cindyrod
asked on
How to suppress the Delete Confirm dialog box when using SQL statement to delete record
I'm using an SQL delete statement to delete a contact from a table. I used the following code to suppress the dialog box but it's not working:
Private Sub Form_BeforeDelConfirm(Canc el As Integer, _
Response As Integer)
' Suppress default Delete Confirm dialog box.
Response = acDataErrContinue ' Display custom dialog box.
If MsgBox("Delete this record?", vbOKCancel) = vbCancel Then
Cancel = True
End If
End Sub
This is just the code from Visual Basic Help. What could be wrong? Thanks.
Private Sub Form_BeforeDelConfirm(Canc
Response As Integer)
' Suppress default Delete Confirm dialog box.
Response = acDataErrContinue ' Display custom dialog box.
If MsgBox("Delete this record?", vbOKCancel) = vbCancel Then
Cancel = True
End If
End Sub
This is just the code from Visual Basic Help. What could be wrong? Thanks.
don't forget to turn it back on
docmd.set warnings true
or if you use the execute command, that in itself suppresses the message
currentdb.execute "Some SQL here"
Good luck
Walt
docmd.set warnings true
or if you use the execute command, that in itself suppresses the message
currentdb.execute "Some SQL here"
Good luck
Walt
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
If you would like to eliminate the warning dialogs permanently, look in [Tools | Options] under the "Edit/Find" tab. There are three confirmation dialogs you can disable. Be warned, though...this disables those dialogs in Access, not just the current db.
ASKER
Why isn't the BeforeDelConfirm working? Any ideas?
Well Cindy,
we're telling you that you don't even need beforeDelConfirm.
we're telling you that you don't even need beforeDelConfirm.
ASKER
I know, I just want to know why it didn't work.
This suppresses the Access system message:
Response = acDataErrContinue
No matter what this line is going to fire:
If MsgBox("Delete this record?", vbOKCancel)
the cancel = true prevents the query from running.
Make sense?
Walt
Response = acDataErrContinue
No matter what this line is going to fire:
If MsgBox("Delete this record?", vbOKCancel)
the cancel = true prevents the query from running.
Make sense?
Walt
ASKER
But why is the system message still showing up if I use that code?
So you get 2 prompts?
Have you placed a breakpoint in your code to ensure the event is firing? Try rebuilding the individual event by deleting the code you have now, selecting the same event from the drop-downs at the top of the code window, and re-enter the code. Any change? Try using this code on a copy (this SHOULD delete records with no warnings) and see if it still shows up:
Private Sub Form_BeforeDelConfirm(Canc el as Integer, Response as Integer)
Cancel = 0
Response = 0
End Sub
Private Sub Form_BeforeDelConfirm(Canc
Cancel = 0
Response = 0
End Sub
ASKER
Actually, I only get the Access prompt (the default one). I guess that the event is not even firing. Is this because the deletion is done through SQL statement (DoCmd.RunSQL deleteStatement)?
LOL! Yeah, that's it. The form's event will only fire when you act on the form's recordset. Executing a separate DELETE query by using DoCmd.RunSQL or CurrentDB.Execute will not trigger the event.
ASKER
Oh, I see. That makes sense. So the only way to suppress this message in my form is by setting the warning to false or using the currendDB.Execute method? Which way is more efficient? Does turning off the warnings suppress other important messages such as runtime errors and errors found during deletion/insertion/update?
Thank you both for your great help.
Thank you both for your great help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
If I use CurrentDB.Execute, then I will still get the error messages regarding validation rules,etc.? I just want to suppress messages like "You are going to delete ...", "You are going to append ...", etc., not messages about Runtime errors, violation of primary keys, etc.
CurrentDB.Execute will not generate ANY message boxes regarding the transaction/statement you send to it. It does, however, provide for returning the number of records affected by the last query. The only error messages it will generate are syntax errors and functionality issues (e.g., requesting functionality the drivers do not support). For validation rule violations, PK/FK violations, and data-typing, you will not see an error. Below is an example of how you would use the RecordsAffected property to find a problem:
set db = currentdb
set rs = db.openrecordset("SELECT * FROM MyTable")
rs.movelast
x = rs.recordcount
rs.close
db.execute "INSERT INTO MyTable2 (f1,f2,f3) SELECT * FROM MyTable"
if db.recordsaffected <> x then msgbox "Records available and records inserted did not match."
set db = currentdb
set rs = db.openrecordset("SELECT * FROM MyTable")
rs.movelast
x = rs.recordcount
rs.close
db.execute "INSERT INTO MyTable2 (f1,f2,f3) SELECT * FROM MyTable"
if db.recordsaffected <> x then msgbox "Records available and records inserted did not match."
docmd.setwarnings false
that turns off the confirm
Walt