Clearing checked check boxes in Access 2003 Table

[Michael Pratt]

Greetings! I have a simple shopping list database that when the user scrolls through common products purchased from the supermarket there is a check box on the form that "adds the item to the shopping list". Once checked, I have a report that pulls the list using this check box. The question is how can I clear all of the check box's from the previous months order to start a fresh list? Any help would be appreciated.

[Seth Schrock]

If you always want to clear all of the checked records, you can create an update query to update all the records that have the check box checked to make them equal to false.

Without field names, it is a little tricky to give you an example, but here is a try:

UPDATE tblGroceryList
SET CheckBox = False
WHERE CheckBox = True

You can then run this query in an On Click event for a button. It is possible to be much more selective in which ones to uncheck, but without further knowledge of how you have your database setup, I can't make any suggestions.

FYI, it helps people help you if you provide more information such as field, control, table, form, query or report names that are affected.

[NeoPa]

I agree with Seth absolutely. Without a proper question it's hard to provide a matching answer.

I will just add though, as it's still a possibility, that if you have a Shopping type database I would expect that a table would be required that linked the onjects and the users. In such a case then you would not have CheckBoxes, but the records would be deleted instead. New selections would result in newly created records to match the products with the user. Of course you could also keep the history and have the [Date Selected] as part of the key.

[Michael Pratt]

Hello Seth!

Thank you for your help. I tried the following on the on-click command of a button I added to my form (main)

Private Sub Command23_Click()
    
    Update tblfood
    Set PlaceOnList = False
    WHERE PlaceOnList = True

End Sub

And got the following Error:

Compile Error: Sub or Function not defined.

Now, please be easy on the newb! This is my first ever posting to a group! <grin> I will include as much info below as I can.

TableName: food
Checkbox Name: PlaceOnList
Command Button 23
Form Name: Main

Please let me know if you need any more information. Thank You!

[Seth Schrock]

Sorry, the code that I gave you was SQL code and you would need to put that into the query. So you would need to create a new query, go into SQL view (might be called something else depending on the version of access you are using) and then paste exactly what you have into the query. The code that you would need for the button would be:

DoCmd.OpenQuery "qryDeselectItems"

You will get two messages that pop up verifying that you want to change the records. If you don't want to be warned or you want to create your own warning message, then you would do:

DoCmd.SetWarnings False
DoCmd.OpenQuery "qryDeselectItems"
DoCmd.SetWarnings True

I prefer to turn off the warnings because they don't make sense to someone who doesn't know access and I can customize my own messages, but you can do what you want.

Let me know if you have any more trouble.

[Michael Pratt]

Update: I realized that you said to create an update query. I did so and it made sense. I ran the query and it worked, but with lots of pop up errors along the way. Any way to get rid of the pop up errors?

[Seth Schrock]

What are the error messages that you get? There should be two warning you that the changes cannot be undone, but my code in post 4 should get rid of those.

[Michael Pratt]

Hey there! -- yes, sorry. I had not read your reply before I posted my second reply. I will try it out now but I am sure it will work!! Thank you so much for your time and help!

[Seth Schrock]

You are welcome. If it works, don't forget to choose the best answer.