VBA SetWarnings

Posted on 2011-05-03
Last Modified: 2012-05-11
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,

Question by:pwdells
    LVL 47

    Accepted Solution

    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.

    Author Comment

    So change over to the "currentdb.execute strsql, dbfailonerror" syntax and eliminate the SetWarnings?
    LVL 47

    Expert Comment

    by:Dale Fye (Access MVP)
    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:

    On ERROR GOTO ProcError

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

    Author Comment

    I will try that now.  Thank you.
    LVL 11

    Assisted Solution

    >> 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.
    LVL 75

    Assisted Solution

    by:DatabaseMX (Joe Anderson - Access MVP)
    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.

    LVL 75

    Expert Comment

    by:DatabaseMX (Joe Anderson - Access MVP)
    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


    Author Comment

    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

    What Should I Do With This Threat Intelligence?

    Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

    Join & Write a Comment

    Suggested Solutions

    In Debugging – Part 1, you learned the basics of the debugging process. You learned how to avoid bugs, as well as how to utilize the Immediate window in the debugging process. This article takes things to the next level by showing you how you can us…
    Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
    Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
    Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …

    729 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

    Need Help in Real-Time?

    Connect with top rated Experts

    21 Experts available now in Live!

    Get 1:1 Help Now