Checkbox to pick record & put into another table

i am making a stock control system in MS access 2000.  Currently i am developing a form which will be used to order the stock which is low.
currently i have a main form with a subform on it which shows a list of all the products which r below the specified limit. the list is a datasheet view populated by a query.
Now what i want to do is to have some kind of facility to pick items from that list as i dont want to order everything which computer suggested. what i am thinking is to have a checkbox before every record in the datasheet view which can allow me to pick items from that list & then put the selected records into another table.
Can anybody plz help me.i have no idea how to actually do this.

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Hi arkswt,
If I understand what you are saying correctly, your are selecting items that you DON'T want to order, even if the computer is suggesting them.  Wouldn't it be easiest to make your purchase list out of a query of the table where the items are not checked?  If this changes each time you make an order, you could assign a macro to remove all of your checkmarks from the table when you order the items not checked into stock (or when you recieve them, the point is it would reset the computer reccomendations if you wish.)
The underlying rule is that data should not be stored anywhere twice if you can help it.  Different queries or "views" of the data may be a smarter way to approach this problem.
arkswtAuthor Commented:
hi CiceroBC
 thanks for ur reply. may be i did'nt made myself clear. actually the only reason i am showing the full list of everything which is low is because my cliet wishes to have a look what is below the level & then to choose which one's they wanna order out of that list.
i dont hav eany checkboxes in the table. my query is returning the results where the current stock level is below the limit set by the management for that product. my approach of using checkboxes is just to select items from that view.
my client do want to c what ever is below the level but does'nt wants to order everything out of that.

secondly i am not storing data twice the only reason i want to put the selected items into a diff able is to keep a record of all purchases which ofcourse will be deleted after a certain period of time.

An overview of how I would approach this is to have a query find the items that the computer thinks need to be ordered. This query would append the items to a temp table, the temp table would be the recordsource for the form. One of the fields in the temp table would be a boolean (Yes/No)  so you could select the items that you are going to generate a report from or however you're going to handle them.

The logic would flow like this.

Click a button
  run a query to empty the contents of the temp table
  run another query to append the suggested items to the temp table
  open the form with a view of the items in the temp table

Then with the form open you can scroll down and select the items you want...
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.


You will have to add a yes/no field somewhere. This can be in the original tblProducts table, or in a special one-to-one related "tblProductsSelection" table. Let's try that:

1) Create the table tblProductsSelection:
    klngProductID --- key field, long integer
    ysnSelected --- yes/no

2) Relate the new table to tblProducts

3) Add the new table to the query, e.g.

    SELECT PS.ysnSelected, P.*
    FROM tblProducts As P LEFT JOIN tblProductsSelected As PS
    ON P.klngProductID = PS.klngProductID

4) Observe that you can simply click on the checkbox in query datasheet view: Access creates the record for you. Use the same on your subform, displaying the checkbox.

Now you can, before opening the form, run:

    DELETE FROM tblProductsSelected;    (delete all records)
    UPDATE tblProductsSelected SET ysnSelected = False;   (reset while form is open)

Down the line, you can then use that new table to filter for only selected products.

Does that make sense?
Good luck!
I think p912s and I are generally in agreement as to how this should work.
The temp table isn't a bad idea, but it would seem smoother/easier to store a Yes/No field in the table that stores the data on the products that need to be ordered and then alter the queries of that data to either display or not display checked items.  Then have an update query to remove the checkmarks when items are purchased or brought into stock or whatever your certain period of time is.  If you would like to still have a temporary table for these items you could have an append query that takes the values that are checked and places them in the table, and a delete query for when it is time to clear them out.  Either way, you should be able to store which ones go where based off of a yes/no value in the original table.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
arkswtAuthor Commented:
thanks for ur help guys. now also help me deciding how to give the points.

all three replies actually helped me & gave me a better view of the problem. all of u basically said the same thing but i found CiceroBC's idea more helpful eventually, of having yes/no in the same table instead of creating a temp table.

i am not really sure how to assign points so help me:)

Thanks once again

To put the yes/no field in the main table is easier. However, only one user can use the mechanism at any one point. If your need several users to select products, you will need a temporary table for each one.

> i am not really sure how to assign points so help me:)

To close this question, you would typically either select the most useful answer (use the accept button) or split points among those that helped you (use the "Split" link above the comment box).
If the solution evolved through many posts by various experts, you should always use "Split", but that is not the case here.
As for the actual points distribution, that's entirely up to you, of course.
See also: {http/help.jsp#hs5}, or the Help link at the top right of the page.

Thanks for the points and grade!
Good luck with your project!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.