Set warnings False only for certain types of warnings?

[Margarita]

Hello,
Is it possible to docmd.setwarnings false for some types of warnings but not others. I am running an update query using .execute and before the execution line, I want the warnings to be suppressed if it's just the regular 'you are about to update...' message, but I want it to display if it's a warning about an error.
Thank you!
(Access 2003, vb 6.5)

 

[Galaxiom]

No. However CurrentDb.Execute is an alternative to DoCmd.RunSQL and doesn't give those warnings.

 

[Margarita]

Thank you, Galaxiom. I didn't realize that .execute had that advantage. Will keep that in mind when dealing with warnings in the future. Thanks!

 

[RainLover]

As a follow on from what Galaxiom said, I would advise not to turn Warnings off. If something go wrong how do you get them back.

So as there is an alternative there is no need for it.

 

[Margarita]

I usually set warnings to true immediately after the action line, but you are right- if execute doesn't display 'you are about to update' warnings at all, there is no need to set the warnings off anyway. Thanks!

 

[RainLover]

Your code can still error before turning Warnings back on. And the problem is that you don't know it happened.

 

[Margarita]

Your code can still error before turning Warnings back on. And the problem is that you don't know it happened.

That is exactly what I was worried about- and that's why I asked about turning off warnings only for the update kind of messages, but not for the error messages.
Thanks!

 

[RainLover]

Most Welcome.

 

[Galaxiom]

BTW When using Execute method always include the second argument dbFailOnError.

This throws an error when something goes wrong. Otherwise it quietly ignore any errors such as key violations, validation failures, datatype mismatches etc.

The disadvantage of Execute is that it cannot handle a reference to a Form or Report because it sends the query straight to the database engine. All values read from forms have to be concatenated into the SQL string or passed as parameters.

 

[Margarita]

BTW When using Execute method always include the second argument dbFailOnError.

This throws an error when something goes wrong. Otherwise it quietly ignore any errors such as key violations, validation failures, datatype mismatches etc.

The disadvantage of Execute is that it cannot handle a reference to a Form or Report because it sends the query straight to the database engine. All values read from forms have to be concatenated into the SQL string or passed as parameters.

Thank you, Galaxiom. As you suggested, I included the dbFailOnError argument. For me own informational purposes, would you be able to clarify your statement 'it cannot handle a reference to a Form or Report because it sends the query straight to the database engine'. I have a hard time wrapping my brain around just what that means.
Thanks!

 

[gemma-the-husky]

the problem /difference between the two is that

a) docmd runquery "actionquery" ignores errors

but

b) currentdb.exectute "actionquery" fails on errors


I think if you want to test/allow some errors but not others, you probably need to rewrite your queries to anticipate the successes/failures, so that the final query works correctly in either case. Maybe you need to run multiple queries.

 

[Galaxiom]

would you be able to clarify your statement 'it cannot handle a reference to a Form or Report because it sends the query straight to the database engine'.

DoCmd.RunQuery is processed by Access. It reads reference like Forms!formname.controlname, substitutes the value then sends the query to the database engine that actually does the change to the data.

Currentdb.Execute is processed directly by database engine. The engine knows nothing about the form so the reference is not recognised.

Consequently the values must be given to the command in other ways.

Execute can run a stored query but if a value must be provided it has to be appended to the query's Parameters Collection first. This is quite involved aand beyond the scope of this thread.

Execute can also run an SQL command provided as a string. Values from forms must be concatenated into the SQL string.

CurrentDb.Execute "UPDATE blah blah WHERE somefield=" & Forms!formname.controlname

As Dave hinted, Execute only runs action queries.