Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Checkbox to pick record & put into another table

Posted on 2006-04-14
9
Medium Priority
?
618 Views
Last Modified: 2012-06-27
hi
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.

Thanks
0
Comment
Question by:arkswt
  • 3
  • 2
  • 2
  • +1
9 Comments
 
LVL 1

Expert Comment

by:CiceroBC
ID: 16454604
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.
0
 

Author Comment

by:arkswt
ID: 16454774
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.

Thanks
0
 
LVL 18

Assisted Solution

by:p912s
p912s earned 332 total points
ID: 16454812
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...
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 58

Assisted Solution

by:harfang
harfang earned 332 total points
ID: 16454891
Hello.

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!
(°v°)
0
 
LVL 1

Accepted Solution

by:
CiceroBC earned 336 total points
ID: 16454934
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.
0
 

Author Comment

by:arkswt
ID: 16460890
hi
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
0
 
LVL 58

Expert Comment

by:harfang
ID: 16463200
Hello,

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.

Cheers!
(°v°)
0
 
LVL 18

Expert Comment

by:p912s
ID: 16486417
Thanks for the points and grade!
0
 
LVL 58

Expert Comment

by:harfang
ID: 16487030
Good luck with your project!
(°v°)
0

Featured Post

Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
The Relationships Diagram is a good way to get an overall view of what a database is keeping track of. It is also where relationships are defined. A relationship specifies how two tables connect to each other. As you build tables in Microsoft Ac…
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question