I have a table where its records have several Boolean fields that are periodically ALL subject to being reset to false. I know how to brute-force field-by-field in a Recordset loop, but I was wondering how to accomplish that more simply?
I have a table where its records have several Boolean fields that are periodically ALL subject to being reset to false. I know how to brute-force field-by-field in a Recordset loop, but I was wondering how to accomplish that more simply?
run an update query.
set field= false
no code needed.
Works like a champ! How do I run that from code without all the warning messages pertaining to what the query is about to do?
use docmd.setwarnings false before running it and docmd.setwarnings true after
cheers,
Vlad
I don't recommend the Set Warnings method with any action query. If it fails or produces an error, there is no warning (makes sense, yes?).
If an error occurs, (which you may not even know about) you need error handling to reset, otherwise it's still off until you close the db, consciously reset it, or another procedure does it without you knowing. I'd use the Execute method of the CurrentDb object with the dbFailOnError parameter. This would also allow the use of transactions, which is probably not useful here. Just giving all the facts I can.
The more we hear silence, the more we begin to think about our value in this universe.
Paraphrase of Professor Brian Cox.
Yes, I stumbled onto this approach with a simple search.
Code:DoCmd.SetWarnings False DoCmd.OpenQuery "QClearChanges" DoCmd.SetWarnings True
I think it would be readily apparent if the Update query failed to set all of the Boolean fields to False. However, I'm always in favor of covering all my bases should problems arise so I'll run the Update query using the Execute method and add a few line of OnError code to "wake me up".
Code:CurrentDb.Execute "QClearChanges", dbFailOnError
I figured as much, for your case. Just wanted to give you an option and reasons for it.
The main consideration between the two methods mentioned relate to "who uses the app". If it were just me I'd just turn off the warnings, but that is not the case so it would be important to "make some noise" if things "go south" during the query.