Ask Your Question

Revision history [back]

click to hide/show revision 1
initial version

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier".

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier"."Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

Sample: ProductSuppliers.odb

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

Sample: ProductSuppliers.odb

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

UPDATED Sample: ProductSuppliers.odb - has macros.

Edit 11/27/16:

Using the screenshot provided, I don't see any way to create the same type of form without using macros. The Sample above has been replaced retaining the original form (PRODUCTSUPPLIERS) which used a filter and a push button. There is an added form (PRODUCTSUPPLIERSMacros) which is based on the screenshot provided (details/fields do not correspond but easily modified to fit needs) and displayed data is done through selections in the list boxes. The top row is the "Products" data, the next two are "Supplier" data and the last row is "SuppliersProducts". Choosing a Product will give all suppliers with a quote for that product. The specific quote will be displayed based upon the Supplier list box selection.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

UPDATED Sample: ProductSuppliers.odb - has macros.

Edit 11/27/16:

Using the screenshot provided, I don't see any way to create the same type of form without using macros. The Sample above has been replaced retaining the original form (PRODUCTSUPPLIERS) which used a filter and a push button. There is an added form (PRODUCTSUPPLIERSMacros) which is based on the screenshot provided (details/fields do not correspond but easily modified to fit needs) and displayed data is done through selections in the list boxes. The top row is the "Products" data, the next two are "Supplier" data and the last row is "SuppliersProducts". Choosing a Product will give all suppliers with a quote for that product. The specific quote will be displayed based upon the Supplier list box selection.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

UPDATED 11/28 Sample: ProductSuppliers.odb - has macros.

Edit 11/27/16:

Using the screenshot provided, I don't see any way to create the same type of form without using macros. The Sample above has been replaced retaining the original form (PRODUCTSUPPLIERS) which used a filter and a push button. There is an added form (PRODUCTSUPPLIERSMacros) which is based on the screenshot provided (details/fields do not correspond but easily modified to fit needs) and displayed data is done through selections in the list boxes. The top row is the "Products" data, the next two are "Supplier" data and the last row is "SuppliersProducts". Choosing a Product will give all suppliers with a quote for that product. The specific quote will be displayed based upon the Supplier list box selection.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

UPDATED 11/28 Sample: ProductSuppliers.odb - has macros.

macros. Reduced code and added comments.


Edit 11/27/16:

Using the screenshot provided, I don't see any way to create the same type of form without using macros. The Sample above has been replaced retaining the original form (PRODUCTSUPPLIERS) which used a filter and a push button. There is an added form (PRODUCTSUPPLIERSMacros) which is based on the screenshot provided (details/fields do not correspond but easily modified to fit needs) and displayed data is done through selections in the list boxes. The top row is the "Products" data, the next two are "Supplier" data and the last row is "SuppliersProducts". Choosing a Product will give all suppliers with a quote for that product. The specific quote will be displayed based upon the Supplier list box selection.

It is not exactly clear on what the end result is to be. From the question, you have suppliers and these suppliers have products. It appears that multiple suppliers can have the same product and each supplier has multiple products. This would mean that the "Products" table should be linked to the "Suppliers" table since each supplier can have multiple products.

What is not quite clear is the "ProductSuppliers" table and what its' purpose is. On the surface, it is a collection of the products used and who supplies them. If so, only a link to the "Product" is needed since the product links back to the "Supplier".

So in you example it would appear that to obtain a "Product" you also need a "Supplier". In other words, a combination of the two to access the detail and probably a refresh of the "ProductsSupplier" record once a "Supplier" is chosen.

More info on the "ProductSupplier" table purpose might help.

Edit:

Based upon further info provided a sample is attached. The list box contains the choices from the "Products" table. Select with Push button. This displays, in a table control, the products quoted upon from the "Suppliers" and is the data in the "SuppliersProducts" table. Whichever item is selected will display the appropriate data from the "Suppliers" table in the fields below the table control.

The "suppliers" data is on a subsubform and linked to the "SuppliersProducts" table.

The selection using the push button can be eliminated by using a macro. Again, the form result you want is still not clear.

UPDATED 11/28 Sample: ProductSuppliers.odb - has macros. Reduced code and added comments.comments and delete old routines.


Edit 11/27/16:

Using the screenshot provided, I don't see any way to create the same type of form without using macros. The Sample above has been replaced retaining the original form (PRODUCTSUPPLIERS) which used a filter and a push button. There is an added form (PRODUCTSUPPLIERSMacros) which is based on the screenshot provided (details/fields do not correspond but easily modified to fit needs) and displayed data is done through selections in the list boxes. The top row is the "Products" data, the next two are "Supplier" data and the last row is "SuppliersProducts". Choosing a Product will give all suppliers with a quote for that product. The specific quote will be displayed based upon the Supplier list box selection.