Set Warnings

[Galaxiom]

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

Yes, using parameters is another way to do it. To be pedantic, parameters are not form objects and VBA is being used to pass the value from the form to the parameter. Using Execute, the query is not referencing a form object but a parameter.

The point I was making is that Minty's suggestion that it is a simple change, implying it just requires substituting RunSQL with Execute is not necessarily correct.

 

[Pat Hartman]

It is a logically simple change. The code you need to change is easy to find. The substitute code can be a single line if the queries don't need parameters. Using .execute is safer since you don't have to turn off warnings to keep users from getting warning messages and have the danger of forgetting to turn them back on. That's why I ALWAYS use the macros I suggested because they toggle the Hourglass as a reminder because you really, really don't ever want to forget to turn warnings back on.

The other problem with turning off warnings is that it not only turns off notice type messages, it also turns off error messages so many people have trouble debugging since they don't know their query is failing.

All-in-all, the .execute is better practice and safer.

 

[Gismo]

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.

It is a logically simple change. The code you need to change is easy to find. The substitute code can be a single line if the queries don't need parameters. Using .execute is safer since you don't have to turn off warnings to keep users from getting warning messages and have the danger of forgetting to turn them back on. That's why I ALWAYS use the macros I suggested because they toggle the Hourglass as a reminder because you really, really don't ever want to forget to turn warnings back on.

The other problem with turning off warnings is that it not only turns off notice type messages, it also turns off error messages so many people have trouble debugging since they don't know their query is failing.

All-in-all, the .execute is better practice and safer.

I have already started to amend all my update queries as suggested

the reason why I wanted to use the setwarning is to avoid having the user getting all the updates notifications when action queries are selected in client settings and I did not have the time to ask all the users to sign in to remote desktop so i could untick confirm

 

[Pat Hartman]

I didn't say not to set warnings off. I told you how to do it as safely as possible. Changing the code to set warnings of/on using the macros I suggested means touching every place you run an action query so it is probably no more trouble to change to using the .execute method if you have to change every instance anyway. Even if you're not going to use them at this time, create the two macros.