[Last Call] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 617
  • Last Modified:

VBA SetWarnings

On each sub, I start the routine w/ docmd.SetWarnings(False) and pair the original statement with DoCmd.SetWarnings(True) at the end of the subroutine before Exit Sub.  I checked each sub to make sure I did not toggle / mix up the True and False settings; yet, when I run the app, I still get confirmattion requests to APPEND data and DELETE  records on various functions.  It does not occur for all DoCmd.RunSQL(str_SQL) statements, just on a few.

What other rules, besides pairing the two SetWarnings statements, do I need to follow to make sure that this app doesn't require user confirmation?

Thank  you,

  • 3
  • 2
  • 2
  • +1
3 Solutions
Dale FyeCommented:
get rid of docmd.runsql

Instead, use:

currentdb.execute strsql, dbfailonerro


currentdb.querydefs("Queryname").execute dbfailonerror

When you use this syntax, you eliminate the warning messages and can actually trap any errors which occur.

Additionally, unless you have exceptional error handling, using docmd.setWarnings will eventually cause you serious problems.
pwdellsAuthor Commented:
So change over to the "currentdb.execute strsql, dbfailonerror" syntax and eliminate the SetWarnings?
Dale FyeCommented:
That's my recommendation.

You still need to institute good error handling, but this will eliminate the warnings.

You will need to have a line in each procedure that executes a query or SQL string this way that processes any errors:


And I generally have a line which initiates the cleanup in the procedure:

    'insert code here to close open recordsets or set objects to nothing
    Exit Sub

    If Err.Number = x  Then
        'do something here.
    Elseif Err.Number = y then
        'do something else for this type of error
        debug.print "Procedure name", err.number, err.description
        msgbox err.number & vbcrlf & err.description, vbOKOnly, "Error in procedure: Procedurename"
        Resume ProcExit
    End IF

End Sub

If you use good error handling, and put your SetWarnings True statement in the code between the declaration of ProcExit: and Exit Sub, and alway ensure that errors that make it to ProcError exit via ProcExit, then you could still use SetWarnings, but that funtionality will only inform you of the problem, and won't let you actually do something about it (handle it).
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

pwdellsAuthor Commented:
I will try that now.  Thank you.
>> So change over to the "currentdb.execute strsql, dbfailonerror" syntax and eliminate the SetWarnings? <

Yes ...

SetWarnings, to me, is not preferred because the setting is static.  That means if you set it, it won't change until you set it back.  So if your app crashes after a setting of False, you are stuck with the setting at False, until you set it to True again.
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
Just Say No To DoCmd.SetWarnings !

If you forget to execute a SetWarnings True ... and/or you code errors out ... leaving a False condition in effect ... you will run into a load of other undesirable issues ... such as ...

You open a form in Design view .... you make some changes ... you decide NOT to keep those changes ... you click the Close button ... expecting to see a WARNING message asking "Do you want to ...."
You WILL NOT see that message and the changes will be saved ... if a prior SetWarnings False is still in effect.  There are many other situations.

DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)Commented:
No points, but here is my full bit on the subject ... excuse an duplication:

Let me suggest a safer way ... that gets away from the SetWarnings False - which can have many undesirable ramifications.  

The problem with SetWarnings False is ... if one (or more) of the queries fail for *whatever* unexpected reason ... you will never know this because SetWarnings False masks out *all* errors, which can lead to you *thinking* that everything ran ok, when in fact it did not.

The approach below has two advantages:

1) You do not get the warning prompts - just like if you use SetWarnings False ... AND ...
2) If an Error DOES occur, it will be trapped and you can act accordingly.

Private Sub btnRunQry_Click()
    On Error GoTo btnRunQry_Click_Error
    CurrentDb.Execute "SomeActionQuery1", dbFailOnError
    CurrentDb.Execute "SomeActionQuery2", dbFailOnError
    MsgBox "Operation completed successfully!"

    Exit Sub

   MsgBox "An error occurred:" & vbCrLf & Err.Number & vbCrLf & Err.Description
   GoTo btnRunQry_Click_Exit
End Sub

pwdellsAuthor Commented:
Thanks DatabaseMX!  You, Fiyed and Capricorn are always life savers!!!  

I haven't had a chance to start coding yet.  But, I will get to it today, make the error handling changes, as recommended by you and Fiyed.  Then get back to you on how it turned out.

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now