473,322 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

Clearing checked check boxes in Access 2003 Table

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.

8 4443
Seth Schrock
2,965 Expert 2GB
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
32,556 Expert Mod 16PB
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
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
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
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
2,965 Expert 2GB
You are welcome. If it works, don't forget to choose the best answer.
Nov 3 '11 #9

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

Similar topics

2
by: aaj | last post by:
Hi all I have a continuous bound form and on each record is a tick box. The user ticks the boxes and these boxes define the batch. for future operations before they leave the page I count...
0
by: cbielich | last post by:
Here is my problem I create new db in access Link tables from a MySQL server on remote machine to access As long as I dont close access everything works fine. As soon as I close access and...
6
by: Peter Neumaier | last post by:
Hi, I am trying to select some data through a stored procedure and would like to store the result in a local access table. Is that possible? Can somebody provide an example? Thanks&regards!...
10
by: antheana | last post by:
Hi there, I am currently redesigning a database, which includes a contracts table that captures information about each contract e.g. ContractID (PK) ContractReference ContractTypeID (FK) ...
1
by: jjjoic | last post by:
Hi, I use Access 2003 to generate the back-end data for a ColdFusion report at work. The report is sorted by a column and based on the sorting, rankings are assigned to each row(i.e. the biggest...
7
by: AkosBeres | last post by:
I’m in the process of setting a sample local Access table called "Fruits" to store data in the following sample fileds: Week Amount Description Version or sequence The data will be loaded into...
1
by: KrazyKasper | last post by:
MS Access 2003 – table inside a report I have an Access report that I use in a mail merge (export to Excel and then mail-merge into a Word document). The report currently shows: CustomerId...
4
by: ckpoll2 | last post by:
Ok, so I'm wondering if what I need to do is even possible because I haven't been able to figure out how to do it and I haven't seen any similar posts on here. In a table, I have a series of...
15
by: krunshah | last post by:
Hi, I have only 2 table (1- Client contact & 2- billing details) I am designing a database for billing in which i need to have the below fields SrNo Description of goods Qty Rate PerPcs...
0
by: evilbungle | last post by:
Good Morning, I am trying to build an application that will take the details from an access database and use them as log in details but I can not get the App to open the table as I keep getting...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.