Multiple Combo Boxes for the Same Field Value. (1 Viewer)

[David Ball]

Hi,
I have a form where the user can select values from combo boxes to populate a Products table, tblProducts. The combo boxes get their values from individual tables for each field, for example tblManufacturer.
I have it set up so that each combo box has the Limit To List property set to Yes and when the user enters a new value in the combo box (eg, a manufacturer that is not already in the list) VBA code enters the value into the table (eg, tblManufacturers) and requeries the combo box so the new value shows up.
The problem is that I want to show three fields; Supplier 1, Supplier 2 and Supplier3.
The idea is that there may be more than one supplier that can provide the product and I want to show them all in these cases.
I am trying to figure out how to get this to work. I tried setting up three combo boxes that all had the same table, tblSuppliers, as the Control Source but I don’t think this will work.
tblProducts has fields, Supplier 1, Supplier 2 and Supplier 3. I am trying figure out what the control sources should be for these combo boxes on the form.
I considered setting up three separate tables for the suppliers but then I really need all suppliers to be available in one place so that no matter which combo box the user is working in all suppliers are an option.
Can anybody please advise how I should proceed?

Thanks very much and Merry Christmas!
David

 

[June7]

That is not a normalized data structure. Should be a record for each product/supplier pair in tblProductSuppliers.

 

[Rafal]

Solution through a relational table:
The product table is the parent table, the product name is unique (if not, then ID is unique). One-to-many relationship. In the child table are the producer names associated with the product or product ID. The child table can be placed in the header of the form with products as a subform in the datasheet view, then the producers can be seen immediately base on actual product record, and any number of producers can be added. You can open the manufacturer's details using producer name in the subform.
In the subform SD view the producers column consist from combo's. By the inside query in combo and proper settings you can choose from existing producers or add new one name the fly.

 

[theDBguy]

Hi David,

You said a product can have more than one supplier, but can a supplier produce more than one product? If so, you would actually have a many-to-many relationship between your products and suppliers tables.