Results 1 to 9 of 9
  1. #1
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919

    Reset Boolean fields in a table

    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?

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    run an update query.
    set field= false

    no code needed.

  3. #3
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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?

  4. #4
    Gicu's Avatar
    Gicu is offline VIP
    Windows 8 Access 2013
    Join Date
    Jul 2015
    Location
    Kelowna, BC, Canada
    Posts
    4,101
    use docmd.setwarnings false before running it and docmd.setwarnings true after

    cheers,
    Vlad

  5. #5
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    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.

  6. #6
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    Yes, I stumbled onto this approach with a simple search.

    Code:
    DoCmd.SetWarnings False
    DoCmd.OpenQuery "QClearChanges"
    DoCmd.SetWarnings True

  7. #7
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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

  8. #8
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    I figured as much, for your case. Just wanted to give you an option and reasons for it.

  9. #9
    GraeagleBill's Avatar
    GraeagleBill is offline Experienced Old Geezer
    Windows 10 Access 2013 32bit
    Join Date
    Feb 2011
    Posts
    1,919
    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.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Reset Fields After Button Click
    By Eranka in forum Programming
    Replies: 7
    Last Post: 12-24-2017, 09:13 PM
  2. Replies: 4
    Last Post: 09-09-2017, 10:24 PM
  3. Replies: 3
    Last Post: 04-14-2016, 04:20 PM
  4. Unique Values and Boolean Fields
    By Triad in forum Forms
    Replies: 1
    Last Post: 07-15-2010, 06:28 PM
  5. How to query boolean values from table
    By kevdmiller in forum Queries
    Replies: 2
    Last Post: 11-30-2006, 07:41 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums