show all shades for product

[rookie_user1]

I need Help i showing information on my form, can't firgure out how to show this information. How would you go about displaying all the shades related to a 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'd like to see all the shades that are entered into the database for the product 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.

 

[RuralGuy]

It is easier to read if you name the Foreignkeys the same name as the PrimaryKey they contain. Have you tried a SubForm of a query that joins the [tbl ProductShades] table and the [tbl Shades] table and use [ProductLineID] as the LinkChild/MasterField?

 

[rookie_user1]

Yes i have tried this and it won't work i think my problem is that my form where i enter in the product name and the shade name is based on a query, so i can bound the 2 text boxes. and then when i put in the sub form it does not update. it just shows the new record i entered in for example prod1 , and does not show any other of the shades that prod1 has

 

[RuralGuy]

What is the SQL for the query that your form is bound to?

 

[rookie_user1]

the SQL is based on the 3 tables that make up the relationship ,


SELECT Products.Product_Name, Shade.Shade
FROM Shade INNER JOIN (Products INNER JOIN Product_Shades ON Products.Product_id = Product_Shades.Product_id) ON Shade.Shade_id = Product_Shades.Shade_id;


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

 

[RuralGuy]

What are you trying to achieve with the form? It is not clear to me with the information provided so far.

 

[rookie_user1]

ok i have 2 tables one for product and one for shades,

as there can be many shades for one product and many products canhave different shades its a many to many relationship so i have to use a junction table called prod_shade

on my form i have 2 text boxes the form control source is a query as the product name comes from the product table and the shade name comes from the stade table so the only way i could bound the 2 txt boxes to the column names is through a query,

A user can enter in a product and a shades, Example prod1 shade red thats one record, they can add in another record for prod1 but the shades must be different than red, so they enter in blue,

On the form i would like to see a list of all shades thats listed for prod1 in this case if i am viewing prod1 i should see the shades for the record and all other shades that was enter in for prod1 which in this case would be red and blue..

Is there any way i can email you my database?

 

[RuralGuy]

Before we get to that level let's explore your use of the form. What is the "real world" purpose for the form? To order Products or something? The RecordSource for the form (the SQL) is way too complex to make sense to me. I would think something far simpler would be adequate. Would ComboBoxes work for the selection process?

 

[rookie_user1]

The project is for products and there shades, its used to enter in the information for products and there shade details

The user will enter in the product name and brand.

s/he will then enter in the shade details for that product , a product can have more than one shade, but will only show the list of shades it has you will have to create a new record t, product name etc to add a new shade as there is information that goes along with each shade.

Take an example of 1 record in the database, a user enters in the product name and also a shade for that product, thats one record entered in, product = prod1 and shade = blue

Now i select add and start to enter in a new product so i give it the same name as the first one called prod1 i then need to enter in a shade say red. this now gives me 2 records in the database

Now if i were to go back to the first record to edit it i would see prod1 as product name blue in the shade txt box and i also want to see a list of shades that have been entered in for that product name... prod1 so i would see
red and blue. so the view on my edit screen would show all shades for that given product name.

If i were to add in a 3rd product and give it the name prod1 and a shade of yellow this would be 3 records in my tables and if i were to go back to the first record in my edit screen i see prod1 as product name , shade would be blue and in the list of shades for the product name prod1 i'd see blue, red and yellow.


I've attached a picture of what i like to see

 

[RuralGuy]

And what do you plan to do with the table after you have all of these records?

 

[rookie_user1]

I plan to be able to view them and make a report out of them, That was the edit screen i will send u a print screen of my database if you like ?

 

[RuralGuy]

As I asked earlier, would ComboBoxes work for your selections? It is starting to sound a lot like your project is simply homework for a class of some kind.