By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,968 Members | 1,247 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,968 IT Pros & Developers. It's quick & easy.

Clearing checked check boxes in Access 2003 Table

P: 5
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.
Nov 2 '11 #1

✓ answered by 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:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.OpenQuery "qryDeselectItems"
  3. 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.

Share this Question
Share on Google+
8 Replies


Seth Schrock
Expert 2.5K+
P: 2,951
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:

Expand|Select|Wrap|Line Numbers
  1. UPDATE tblGroceryList
  2. SET CheckBox = False
  3. 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.
Nov 2 '11 #2

NeoPa
Expert Mod 15k+
P: 31,709
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.
Nov 2 '11 #3

P: 5
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)

Expand|Select|Wrap|Line Numbers
  1. Private Sub Command23_Click()
  2.  
  3.     Update tblfood
  4.     Set PlaceOnList = False
  5.     WHERE PlaceOnList = True
  6.  
  7. 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!
Nov 3 '11 #4

Seth Schrock
Expert 2.5K+
P: 2,951
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:
Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. DoCmd.SetWarnings False
  2. DoCmd.OpenQuery "qryDeselectItems"
  3. 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.
Nov 3 '11 #5

P: 5
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?
Nov 3 '11 #6

Seth Schrock
Expert 2.5K+
P: 2,951
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.
Nov 3 '11 #7

P: 5
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!
Nov 3 '11 #8

Seth Schrock
Expert 2.5K+
P: 2,951
You are welcome. If it works, don't forget to choose the best answer.
Nov 3 '11 #9

Post your reply

Sign in to post your reply or Sign up for a free account.