Show many shades from the 1 product

Question

Show many shades from the 1 product

sql_Reporter

SSCrazy

Points: 2329
More actions
August 4, 2007 at 1:27 pm

#69322

Hi Can i get a help with my many to many relationship where i have products and shades.A product can have many shades but not of the same colour.
So far i have a form where i can enter in products and a shade if the shade already exist it won't let me enter it in but if not it will so i want a sub form on the page that shows me all the shades related to the product.
Any help would be great ..

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

bledu SSChampion Points: 12335 More actions · Answer 1

so what exactly do you want help on? the query or the form.

you don't necessarily need a separate sub form you can just show the related colours/shades in a data list below or above were u are entering the info.

[Product:] Motor Car [New Shade:] ___________

[Existing Shades]

Blue

Yellow

Red

etc

Everything you can imagine is real.

sql_Reporter SSCrazy Points: 2329 More actions · Answer 2

Hi

The help i need is in showing the information on the form, can't firgure out how to show this information on my form. So how would you go about displaying the shades related to the product on a form. Take in mind that i want to show all the shades for a given product on the screen when am entering in the data on my form. i know i can use the me.requery when i select add product so the list updates..

Here is my tables structure

tblProducts

ProductLineID PK autonumber

strProductName

tbl Shades

ShadesLineID PK autonumber

strShadesName

tbl ProductShades

ProductShadesID PK autonumber

intProductID FK to tblProducts.ProductLineID

intShadesID FK to tblShades.ShadesLineID

My form has product name and shades name on it so the record source is based on a query so i can bound the 2 text field to my form.

jfmccabe SSCrazy Points: 2547 More actions · Answer 3

Do it backward. You want the available / unused shades for a product. You have a form called e.g. fProductShades with a combo box called e.g. cbProductID which displays a product name and hides the productID which is the bound field.

Build a query like this:

SELECT tblProductShades.ProductShadesID, tblProductShades.intProductID, tblShades.ShadesLineID, tblShades.strShadesName

FROM tblProductShades RIGHT JOIN tblShades ON tblProductShades.intShadesID = tblShades.ShadesLineID

WHERE (((tblProductShades.ProductShadesID) Is Null) AND ((tblProductShades.intProductID)=[Forms]![fProductShades]![cbProductID]));

which basically finds the shades in tblShades which do not occur in tblProductShades for that particular product.Use this query as the basis of a combo box to create a new entry in tblProductShades.