Set Warnings

[Gismo]

Hi,

i am trying to turn warnings off for all action queries and record changes but my code does not seem to work

DoCmd.SetWarnings False

also tried

DoCmd.SetWarnings (False)

does False need to be in brackets? i tried both
I want to hard code it instead of manually deselecting in client settings as we have quite a few users using remote desktop

 

[Minty]

The easiest route is to use CurrentDb.Execute for all your action queries then you don't get the messages, and don't need to dangerously turn off all the other warnings.

 

[Gismo]

The easiest route is to use CurrentDb.Execute for all your action queries then you don't get the messages, and don't need to dangerously turn off all the other warnings.

that would be a difficult task at this point in time to change all the forms and buttons to CurrentDb.Execute
suppose i would have to go with set warnings false but it does not seem to work

 

[Minty]

No extra code. Simply

CurrentDb.Execute "YourQueryName" , dbSeeChanges + dbFailOnError

 

[Gismo]

No extra code. Simply

CurrentDb.Execute "YourQueryName" , dbSeeChanges + dbFailOnError

that would be a difficult task at this point in time to change all the forms and buttons to CurrentDb.Execute
suppose i would have to go with set warnings false but it does not seem to work

 

[Minty]

I suspect it's not as difficult as you think.
How are you running the queries at the moment? What method are you using?

 

[Gismo]

I suspect it's not as difficult as you think.
How are you running the queries at the moment? What method are you using?

some are at start up in VBA, some are using a few macros and also on click
there are quite a few update and append queries, most are run by a macro
not going to be easy to change the different instances i use to update

 

[Minty]

I would standardise them all into VBA, then your problem goes away.

In the vba editor you can do a global replace across all the code modules.
A bit of work now will save you a lot of pain later.

 

[Gismo]

I would standardise them all into VBA, then your problem goes away.

In the vba editor you can do a global replace across all the code modules.
A bit of work now will save you a lot of pain later.

are you referring to

 

[Minty]

That's a start, it will create not particularly verbose code but at least it's automatic.

You will still have to then adjust what it creates as I suspect it will probably still use Docmd.OpenQuery ...

 

[Gismo]

That's a start, it will create not particularly verbose code but at least it's automatic.

You will still have to then adjust what it creates as I suspect it will probably still use Docmd.OpenQuery ...

would i then change all the update queries to

CurrentDb.Execute "YourQueryName" , dbSeeChanges + dbFailOnError

what would the string be to DoCmd.OpenQuery "MyQueryName"

 

[Minty]

CurrentDb.Execute "MyQueryName" , dbSeeChanges + dbFailOnError

Replace MyQueryName with the name of the action query you want to run.

 

[The_Doc_Man]

Just to simplify, though turning off warnings is not always a good choice, don't set warnings to False. Set them to 0. And True should be set as -1.

TRUE and FALSE are valid in SQL context, but there are times when they don't work as well in VBA context. You could also use constants vbTrue and vbFalse, which ARE valid VBA constants. Or you could (for VBA context) define constants of TRUE and FALSE. By the way, the same is true for YES and NO, which are not known to VBA - but vbYes and vbNo do indeed exist.

For some strange reason, VBA sometimes thinks TRUE and FALSE and YES and NO (when not in quotes) are variables. If you have Option Explicit, they won't work. And yet in other contexts, TRUE and FALSE work fine. It has happened to me dozens of times and I have not figured it out, even though when I check, I find that TRUE and FALSE are in fact VBA reserved words. Which version of Access are you using?

I think it is the DoCmd.xxx context, because in that context, TECHNICALLY the TRUE or FALSE values are being used as actual parameters and I'm not sure how the formal parameter for .SetWarnings action is defined.

 

[Gismo]

CurrentDb.Execute "MyQueryName" , dbSeeChanges + dbFailOnError

Replace MyQueryName with the name of the action query you want to run.

i will start the transition this evening

thank you for the assistance and guidance

why did set warning false not work for me?

 

[Minty]

The really correct syntax is as @The_Doc_Man suggests

DoCmd.SetWarnings vbFalse

However normally

DoCmd.SetWarnings False

will also work but, it will only work in the scope of the form module it is in.
If you do it globally in a separate module (Very very dangerous) you won't get any error messages anywhere about anything.
So just don't do it.

 

[MajP]

Setwarnings can be finicky. Google and you will see similar posts where it does not work for some reason. Sometimes moving the code to another place works, and sometimes works on one machine and not another. I seen it once for something I was doing and could never figure it out.

As Minty pointed out Setwarnings is not a good approach for many reasons. Here is from Allen Browne

It is good coding to return a state to the way you found it, but Access does
not let you read the state of SetWarnings.
Your options are to maintain your own flag, or to avoid using SetWarnings.
If you are running action queries, consider using the Execute method (DAO)
instead of RunSQL. Execute does not pop up the warnings, so there is no need
to toggle SetWarnings. It is also more powerful:
- the dbFailOnError switch lets you opt out if there is an error (such as a
concurrency issue that does not allow the action query to complete);
- you have the option to use a transaction around the whole process for an
all-or-nothing result;
- you can read the number of records affected.
Example:
Dim db As DAO.Database
Dim strSQL As String
Set db = dbEngine(0)(0)
strSQL = "INSERT INTO ...
db.Execute strSQL, dbFailOnError
MsgBox db.RecordsAffected " record(s) inserted."
Set db =Nothing

No matter how many places you have to update, I cannot imagine it would take more than 30 minutes max to update.

 

[Isaac]

I've always used DoCmd.SetWarnings False
But then again ... I've probably only used it 20 times in all these years, because as Minty suggested, you really need to use VBA and CurrentDb.Execute instead.

Therefore pay no attention to my syntax, because maybe, as someone suggested, I never realized that the scope was limited (although running it in the immediate window, I definitely don't remember that being the case...)

Anyway, my question is, if the reason that you don't want to use CurrentDb.Execute is because there is too much legacy design involving macros, then why do you need the code for setwarnings? Wouldn't you do it in a Macro step, too?

Last note. Prime importance:

Any time you do something dangerous, like SetWarnings, do not fail to have an error handler in place which toggles such a setting back to normal. Failing this causes regret down the road. Can't tell you how to handle error handlers in macros because I don't use them. Too little control over what is going on and too impossible to 1) troubleshoot, 2) maintain, 3) document/comment, 4) step through

 

[Pat Hartman]

If you set warnings off in the database settings and leave them off, you'll be sorrrrrrrry. Very sorrrrrrry after you loose a form you just spent 2 hours perfecting. When warnings are off, if you close a modified object without first saving it, you will not be prompted. Your changes will just disappear.

NEVER leave warnings off. That is my warning to you.

I usually use the .execute method but for the occasions where I need to use the Access commands, I have created two macros.
1. Warnings = False; Hourglass = True
2. Warnings = True; Hourglass = False
Then I use the macros to toggle the warnings off and on. And as a safety measure, when warnings are off, the Hourglass is on. This is a visual clue to ME because sometimes during testing I might stop the code while warnings are off and I don't ever want to forget to turn them back on again.

You only have to be burned by this once to be a believer in what I'm telling you.

 

[Galaxiom]

I suspect it's not as difficult as you think.

It can be a huge task if the query is referring to objects on the form.

DoCmd.RunSQL is a method of the Application so it can read the controls directly from the form. Execute is a method of the database so its scope does not include the Access objects. Consequently Execute requires any values from the form must be concatenated into the SQL and delimited according to their datatype.

 

[Pat Hartman]

I don't have any trouble at all using the .execute method to run queries that reference form objects. The method requires that you set the arguments prior to the execute. Here's an example:

        Set qd = db.QueryDefs!q835_CopyBeforeDelete_Rebill
            qd.Parameters!EnterBatchID = Me.txtBatchID
        qd.Execute (dbFailOnError + dbSeeChanges)
        Debug.Print "Copy 835 before delete = " & db.RecordsAffected