Filtering on Combo Box in Subform

[Tgavin8]

Hi

I am currently attempting to build a databse for a clothing company. The price can differ for this company depending on the size so in the products table I have the sizes listed in a subform linked by ProductID.

The issue is when creating the invoice - on the Sales line items I have the productID pulling through the description for the products table followed by the SIZEID which I am trying to filter down depending on the selection made ProductID. On the combo box query I have the productID criteria based on the selection of ProducID however it is returning no results.

When I remove that criteria I am getting the every size listed in the table which obviously is not satisfactory.

Can anyone help me get round this issue.

Cheers

[Scott Price]

Without knowing what the structure of your tables is:

Normally you will have three tables here. There exists a many to many relationship between Products and Sizes, since one Product can have many Sizes, and conversely one Size may be associated with many Products. So you will have:

tblProducts
ProductID Autonumber PK
ProductName
ProductDesc

tblSizes
SizeID Autonumber PK
Size

tblProductSizes
ProductSizeID Autonumber PK
ProductID Number FK
SizeID Number FK
Price Currency

Now you should find it easier to pull your query that relates Products, Sizes and Prices... Something like

SELECT tblProductSizes.ProductID, tblProductSizes.SizeID, tblProductSizes.Price, tblProduct.ProductName tblProduct.ProductDesc FROM tblProducts INNER JOIN tblProducts ON tblProducts.ProductID = tblProductSizes.ProductID WHERE tblProduct.ProductID = [SomeValue]

Regards,
Scott