Linking Table Data

[Lewis825]

Hi All,

This may be very simple but I cant see it..

I have two tables "All Products" & "Active Products"

I have a simple form, with a list box showing all entries in the "All Products" table which is filtered by a search box. this simply shows the Code, Description & Cost

I want to click on a product in this list to open a form. If that product code already exisits in the "Active Products" Table I want it to show its entry through the form. Standard.
but if that code doesnt exist in the "active" table i need it to add a new record in the "active" table putting the code in the form ready to go.

Thanks in advance

Hope all makes sense

[ranman256]

Why do you have 2 tables? Why wouldn't you just have 1 table,"All Products", with a flag ACTIVE.

[Lewis825]

Thats not a feasable option in this case

[orange]

Please tell us more re the feasibility in plain English.

[Lewis825]

All Products contians all items from all our suppliers, It is bassilcy a stripped down copy of our suppliers price lists.

If we get an order for an item for the first time we havent sold before, I need to be able to pull it from the table and add it to the active table pulling the code and price through with it.

[orange]

Then maybe your table structure is working against you. Getting your tables and relationships to support your business processes is fundamental to database.
If you have multiple suppliers supplying similar or same products, and you want to track active products, then you need to record and monitor which supplier supplied which product.
My gut feeling is that you have jumped deeply into Access without having a database structure that you have tested against your requirements.

You may get some ideas from this free data model from Barry Williams

Good luck.