?
Solved

How to suppress the Delete Confirm dialog box when using SQL statement to delete record

Posted on 2004-08-20
17
Medium Priority
?
1,363 Views
Last Modified: 2010-05-18
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(Cancel 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.
0
Comment
Question by:cindyrod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 6
  • 5
17 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 11854149
if you use
docmd.setwarnings false

that turns off the confirm

Walt
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11854161
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
0
 
LVL 17

Assisted Solution

by:walterecook
walterecook earned 1000 total points
ID: 11854182
so either:
docmd.setwarnings false
docmd.openquery "Some deletequery"
docmd.setwarnings true

or
currentdb.execute "DELETE * FROM...."

You won't need the deleteConfirm code

Walt
0
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

 
LVL 51

Expert Comment

by:Steve Bink
ID: 11854232
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.  
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11854341
Why isn't the BeforeDelConfirm working? Any ideas?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11854440
Well Cindy,
we're telling you that you don't even need beforeDelConfirm.
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11854881
I know, I just want to know why it didn't work.
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11855022
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
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11855129
But why is the system message still showing up if I use that code?
0
 
LVL 17

Expert Comment

by:walterecook
ID: 11855452
So you get 2 prompts?

0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 11855520
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(Cancel as Integer, Response as Integer)

Cancel = 0
Response = 0

End Sub
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11855617
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)?
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 11855738
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.
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11855798
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.
0
 
LVL 51

Accepted Solution

by:
Steve Bink earned 1000 total points
ID: 11870747
CurrentDB.Execute is generally the preferred method, though suppressing the warning messages with the SetWarnings method only applies to DoCmd.RunSQL.  The CurrentDB method has other methods of error-checking and result reporting.

Turning off the warnings will only suppress WARNINGS regarding your data manipulation.  For example, if you have Table1 with 2 fields (f1 number, f2 required text, no blank values) and run this statement:

DoCmd.RunSQL "INSERT INTO Table1 (f1,f2) VALUES (1,'');"

Access will pop up the warning regarding a violation of validation rules.  If you first run this line:

DoCmd.SetWarnings False

Access will not pop up the warning window, but it will also not INSERT the values requested.  If the warning window is allowed to come up, and the user opts to NOT run the query for whatever reason, a run-time error will be generated (error 2501: RunSQL action was canceled).  Suppressing the window will not generate an error (other than for SQL syntax), but it will not complete any 'invalid' queries, nor will it alert you to this fact.
0
 
LVL 1

Author Comment

by:cindyrod
ID: 11870805
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.
0
 
LVL 51

Expert Comment

by:Steve Bink
ID: 11872469
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."
0

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question