Subdatasheet related to the current record on a form

[Floorboy]

forgive my naievety i'm a beginner. I have produced a beutiful navigation form. On it in the left navigation I have placed 8 forms which represent different product groups. On each form I have placed a form in subdatasheet view. The main form shows product details from a query, this query separates specific products from the products table. I want to show supplier and price data related to the record that is showing on the main form. I've done it this way because the relationship between tblProducts and tblSuppliers is a many to many. I've created tblPrices and linked that to the supplier table but when I go to the next product record the subdatasheet doesn't change. how do I get the subdatasheet to be specific to the product being shown. I also want the supplier field in the subdatasheet to be a lookup field so that I don't have to type the supplier in every time. I didn't think it would be easy but I didn't think it would be this hard. I keep bashing the books and looking online but I'm at my wits end, any help would be greatly appreciated.......Mike

[ssanfu]

How are the main form and the subform related? What fields?

I don't use navigation forms, so I have to ask: Why do you have 8 forms which represent different product groups? I would have 1 form changing criteria to select which product group I wanted. Since the main form and the sub form would be related by PK/FK, selecting a different product group, both forms would update the data when a different product group was selected.

[Floorboy]

Hi Steve

I'm not sure about the relationship between the forms, basically I have
tblSuppliers
Supplier_ID Pk
Supplier Name
Address
etc.....

tblProducts Suppliers
Product_ID Fk
Supplier_ID Fk

tblProducts
Product_ID Pk
Product_Name
etc....

tblSupplier Prices
ID Pk
Supplier_ID Fk
Product_ID Fk
Terms
Cut Length
Roll
3 Roll
5Roll
etc....

I created a query pulling all the Carpet products data ( needed to separate them cos the attributes/fields for each group of products is different) I then use the data from the query on the main form showing the product details.
Then I created a subdata form from the tblPrices and linked the field Supplier_ID

I started out by making 8 separate tables representing the 8 product groups but upon advice thought that was wrong and so started again. All the navigation form does is give me a nice visual way to get to the other product forms easily

[ssanfu]

I think your table "tblPrices" might need to be modified.

What data is in the field "Terms"?

Carpet products data ( needed to separate them cos the attributes/fields for each group of products is different)

I don't understand. all of the products are not in "tblProducts"?

[Floorboy]

Terms is just a text field wich will hold stuff like list less 10% or cuts @ Roll

[Floorboy]

Yeah all the products are on one table

Product Name
Product Gruop
Product Number
Manufacturer
Description
Notes
etc...

[ssanfu]

How are the main form and the subform related? What fields?

Open the form in design view. Double-Click on the subform control. The linking fields should be shown.


Consider these tables:

tblSuppliers
Supplier_ID_Pk
Supplier Name
Address
etc.....

tblProducts Suppliers
Product_ID_Fk
Supplier_ID_Fk
PricesID_FK
TermsID_FK

tblProducts
Product_ID_Pk
Product_Name
etc....

tblPrices
PricesID_Pk
Quantity (ex. per Cut Length, Roll, 3 Roll, 5Roll) //these also could be in a table for consistency
Price
Notes

tblTerms
TermsID_PK
Terms
Notes


"Cut Length, Roll, 3 Roll, 5Roll" is data, not field names.

[Floorboy]

I'll have a go at your intructions, thanks Steve. They are fields cos each carpet product can be purchased at different ptices. If I buy only the room size carpet I need, I pay say £3.00 if I buy a whole roll I pay £2.75 if I buy 3 rolls I pay £2.25 per square metre etc...... One product many supplirers and one supplier many prices

[ssanfu]

I know you will end up with more records in the tblProducts Suppliers table, but it is essentially the same thing you are doing in the current tblSupplier Prices table.
If you have supplier1 with Product1 that has Cut length, 3 roll and 5 roll options, but you only have one field for price, don't you have to have 3 records?? And if each one has a different term, you end up with lots of records anyway.

And what do you do if a current supplier decides to change his "5 roll" option to "1 Bundle" (2 rolls) or you get a new supplier with different options? You will have to change your table, your queries, your forms and your reports. And possibly code also.

I know this will change the current forms/queries, but I think it will help in the long run.


------------------
You might see http://b62.tripod.com/doc/dbbase.htm

[Floorboy]

This is what it looks like

Table......................................... Query............................................. .....................................Carpet Form


tblProducts............................ QryCarpet products .................................................. .................product details

tblSupplier Prices............................................ .................................................. ......................subdatasheet.
.................................................. ................................................Su pplier /.....Terms...../....cutlength/..roll../..3roll../5roll../..Was retail../.Now Only retail/tradeprice1/tradeprice2
.................................................. ...............................................Sup pler 1/.........list...........£3.00.....£2.75...£2.50..£ 2.25.......£7.99...\.... £6.99...............£4.00.........£4.20
.................................................. ...............................................Sup plier 2/ list less 10%.....£3.10.....etc.......
.................................................. ...............................................Sup plier 3/ cuts @ roll........£3.05....etc.....
.................................................. ...............................................Sup plier 4/ ring rep
and I need the supplier1,2,3,4 name to be a dropdown select field or lookup

Thats what I'm trying to achieve

up to ten different suppliers