question

Khurram-7911 avatar image
0 Votes"
Khurram-7911 asked BobLarson-6601 answered

Access - Related products are not showing in the combo on subform2

Hello everyone,

I have created following forms in my database based on the following tables.

frmSupplier (Main Form)
sfrmSupProduct (Subform1)
sfrmProductPrice (Subform2)

tblSupplier
SupplierID
SupplierCode
SupplierName
Address
Telephone
Fax
Mobile
ContactPerson
Email
Website
PaymentTerms
DeliveryTime

tblSupplierProduct
SupplierProductID
SupplierID
ProductID
ProductCode
ProductDescription
BrandID
Packing
UnitID

tblProductPrice
ProductPriceID
SupplierID
SupplierProductID
PriceDate
UnitPrice

tblSupplier referes to frmSupplier (Main Form), tblSupplierProduct refers to sfrmSupProduct (Subform1) and tblProductPrice refers to sfrmProductPrice (Subform2).

All related records are showing in the fields except in SupplierProductID (combo). The related products are showing in the combo list but not showing on the subform2.

Below is the RowSource of the combo.

SELECT tblSupplierProduct.SupplierProductID, tblSupplierProduct.ProductDescription
FROM tblSupplierProduct
WHERE (((tblSupplierProduct.SupplierID)=[Forms]![frmSupplier]![SupplierID]));

office-access-dev
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Just a guess but try requerying the combo.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Khurram-7911 avatar image
0 Votes"
Khurram-7911 answered

Thanks for your response. I tried to requery the combo but despite products are not showing in the combo. I am sharing below link of my database so that you can check and understand what I am trying to explain.

https://1drv.ms/f/s!Apg-0LB3x1ielzpyWNnQ6AkaNVOy

I have added products of one supplier so far and therefore only one supplier products are showing in the subform2.

The Product Description is the SupplierProductID that is FK in the "tblProductPrice" and this is the table subform2 is bound.

I am using SupplierProductID to show the Product Description on the Subform2. Although, all related products are showing in the combo list but it is not showing in the subform2.

What should I do to show the related products of the supplier in the subform2 ?

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

thedbguy avatar image
0 Votes"
thedbguy answered

Hi. I tried to download your file, but my work firewall is blocking access. I will have to try it later when I get home.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

BobLarson-6601 avatar image
0 Votes"
BobLarson-6601 answered

Your structure is a bit wacky, shall we say. So, your main form is what should have the data that is only to be entered once. Your Products form (and table) should have the product information. You apparently want to have the Products price be separate, which I would guess is so you can keep the latest prices but also have historical data. That means that you need to have your first subform linked to the main form via the supplier ID. But the second subform needs to be linked to the first SUBFORM, not the main form and linked via the Product ID. And the syntax for that is Forms!frmSuppliers.Form.sfrmProducts.Form.ProductId.

And the product description in the product prices subform should only be referencing the current selection in the product subform that has been selected.

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.