One (of two) subforms does not display data

[EarthCareTech]

Hi all

I am building a database (using the native database engine) in LO 5.0.3.2 on Linux Mint. The database is trying to make sense of multiple suppliers of multiple components.

It has 3 tables: Products, Suppliers and ProductSuppliers, linked (I hope) with one-to-many relationships from the former two to the last one (not real sure how to tell the nature of the relationship).

The main ProductSuppliers form uses two subforms, picking up the details of the product (and the supplier) from the Products (or Supplier) table when a particular product name is picked from the drop-down list in the combo-box.

But I’ve stuffed it up somehow; when entering a new item in the ProductSuppliers form selecting the relevant entry from the Product drop-down list and clicking in the sub-form text box displays the description of the product - which is what I want. But then selecting the relevant entry from the Supplier drop-down list does not display the data in the supplier subform.

I’ve checked the controls for the combo-box and the subform and subform text boxes, and eveything looks fine (but then what do I know?).
EarthCareTech

[EarthCareTech]

The matter was solved with a work-around: adding to each subform a refresh button.

[Ratslinger]

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 added 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.

[EarthCareTech]

Thanks for your suggestion.
The entire database is to allow comparison of multiple quotations from suppliers for the components we require.
The Products table lists the products we require, with the names and our detailed specifications. Each is unique, and it is usually our own specification - sometimes our own design.
The Suppliers table lists the suppliers who have quoted: most quote on only one product, but some quote on multiple - enough to make entering the data multiple times not particularly smart.
The ProductsSuppliers lists the quotations - what product is being quoted on (from the Product table), who is quoting (from the Suppliers table), then the details of the quotation, such as minimum order quantities, pricing, the supplier’s specification for the product, and a record of the clarification conversations with the supplier, and finally a judgement of whether the quotation conforms to our requirements.
It seems to me if we structured the database the way you suggest, it implies that the products are the ones offered by the supplier - not the ones we want.
A screenshot of the ProductSuppliers form is at http://www.earthcaretech.com.au/images/Screenshot.png

[Ratslinger]

Is it correct to say that this form is to be used to review quotes from suppliers for products you require? Does this process in any way update your “Products” table?

[EarthCareTech]

No - the products is our internal list of the components we require and our specification for them.