Combo Box Drop List Including Multiple Fields (1 Viewer)

[RodShinall]

I have a single table database that includes the following fields: Supplier1, Supplier2, Supplier3. On my form I placed three combo boxes, cboSupplier1, cboSupplier2, cboSupplier3, each bound to one of these fields. When I drop down the list for any of these combo boxes I want to see all of the values stored in the three fields.
In other words if Supplier1 contains Wendy's, McDonalds, Taco Bell
Supplier2 contains Sears, Best Buy, Target
Supplier3 contains Joe, Bill, Bob
I want each of the three combo box drop lists to include Wendy.s, McDonalds, Taco Bell, Sears, Best Buy, Target, Joe, Bill, Bob
I tried the following SELECT statement but it only lists values from Supplier1, not from Supplier2 or Supplier3.

SELECT DISTINCT tblShopProjects.Supplier1, tblShopProjects.Supplier2, tblShopProjects.Supplier3 FROM tblShopProjects;

I am just a VBA beginner and this has me stumped. Any help would be appreciated. Thank You.

 

[speakers_86]

I have a single table database that includes the following fields: Supplier1, Supplier2, Supplier3. On my form I placed three combo boxes, cboSupplier1, cboSupplier2, cboSupplier3, each bound to one of these fields. When I drop down the list for any of these combo boxes I want to see all of the values stored in the three fields.
In other words if Supplier1 contains Wendy's, McDonalds, Taco Bell
Supplier2 contains Sears, Best Buy, Target
Supplier3 contains Joe, Bill, Bob
I want each of the three combo box drop lists to include Wendy.s, McDonalds, Taco Bell, Sears, Best Buy, Target, Joe, Bill, Bob
I tried the following SELECT statement but it only lists values from Supplier1, not from Supplier2 or Supplier3.

SELECT DISTINCT tblShopProjects.Supplier1, tblShopProjects.Supplier2, tblShopProjects.Supplier3 FROM tblShopProjects;

I am just a VBA beginner and this has me stumped. Any help would be appreciated. Thank You.

I would say to use a union query to bring the info together, and base your combo boxes on that, but if the combo box is bound to a number field, then you will have problems. If it is bound to a text field, your fine, but your database will become slightly larger, slightly faster.

The other option is to create a table like this:

tblUnion
UnionID (autonumber)
VendorName

Keep tblUnion current with append/update queries. Base your 3 combo boxes on tblUnion, bound to UnionID.

 

[pbaldy]

Having repeating fields like that is a normalization problem. I would strongly suggest reconsidering the design. You will probably run into all kinds of problems like this with that design. Typically if this is a products application, I would have a products table, a suppliers table, and a junction table to keep track of what suppliers can supply which products (each combination of product/supplier is a record).

 

[pbaldy]

Dang! Too slow again. I agree a UNION query is the solution to the posted problem, but I would change the design.

 

[speakers_86]

Having repeating fields like that is a normalization problem. I would strongly suggest reconsidering the design. You will probably run into all kinds of problems like this with that design. Typically if this is a products application, I would have a products table, a suppliers table, and a junction table to keep track of what suppliers can supply which products (each combination of product/supplier is a record).

I don't know if you read all of my post, but I actually did not recommend union query. Just named it as an option.

I see why you say he may have a flawed design, but I had this issue in a db I made. I was keeping track of rental equipment that could have been with an employee, customer, or other. So I did the same thing that I advised this guy to do. Of course, I have not seen his db, so he very well may have a design flaw.

Dang! Too slow again. I agree a UNION query is the solution to the posted problem, but I would change the design.

Heeheehee. Sorry, I have nothing better to do than watch this forum. I have no job, and no cable. In fact, I am 'borrowing' this internet connection from a neighbor.

 

[pbaldy]

I get your point, but sooner or later the powers-that-be will decide they need a 4th supplier, then the db has to be overhauled. Tables, forms, reports etc all built to handle 3 have to be changed. With a normalized design, no changes are required. I've been there/done that with the boss that says "we'll never need x", and sooner or later it's "hey, we need x".

 

[speakers_86]

I don't believe he is saying there are three different suppliers and they are stored in different tables. He is saying there are three different TYPES of suppliers, each having its own table. If you mean that he may eventually have a fourth type, then yes, he may have a problem. There can only be so many types though, I would like to think he can make it work with 3.

 

[RodShinall]

Thank you, speakers 86, your union query suggestion worked perfectly, just what I was needing.