Post: 'Combo Box Showing Table Data From User Selection'

[dantejazz]

Good afternoon.

I have been searching the forum for a couple days now and cannot locate a post wherein an answer was provided to a member for the following:

- (2) Combo Boxs on a single Form
- (1st) Combo Box lists multiple Tables (same DB)
- (2nd) Combo Box - depending on the User selection of (1st) Combo Box - will show the items (Data) associated with the Table selected
- e.g., [Lawn & Garden / rake, shovel, birdseed, etc.],...[Housewares / pots, pans, towels, etc.],...and so on.

The goal is to have one form where a user can access multiple department items for 'Special Order', if not stocked, and update a general record for sorting / reporting.

The post that I read several months ago have the perfect method for doing this and I would love to find it.



As always, thank you in advance for any assistance and guidance.

dj

[pbaldy]

You're having trouble finding a solution because it's unusual to have data in multiple tables like that. Typically you'd want it in one table with a field to denote the "department" or whatever. So in your situation the user would first choose the "department", and then the second combo would restrict to that choice. I'd change the design, but you can adapt the code method here to choose the table if you want to stick with it:

Baldy-Cascading Combos

[dantejazz]

pbaldy,

Thank you for the quick reply.

The only reason that I separated the data in the tables is because I was worried about the size of the table once all of the department items were loaded. There could potentially be several hundred items for each area. Ultimately that could mean thousands of line-items covering every aspect of the store in one gi-normous table.

I am not set on this as a final design and am willing to move on with your advice. A redesign may be the best answer.

What are your thoughts on the overall table size? The final, sorted list (which is normally 20-30 items) is not impacted by the actual table sizes of the departments. The list will be used by one of our interns to place the Special Orders.

Again, thank you for your input.

dj

[pbaldy]

Thousands of records is trivial. If you had said millions, I might begin to worry. I'd still normalize the table; your current design will be a headache in more ways than this one. Wait till they say "Hey DJ, put together a report that includes 'Home and Garden' and 'Hardware', but excludes ...". Easily done with criteria when the data is in one table.

[dantejazz]

I feel much better about this. A redesign it is then!

The 'learning curve' is still holding me back a little. But, I know that everything comes with time and practice.

I greatly appreciate your help, pbaldy.

(p.s. - Had I stayed with this design,...the link you provided was well written and I learned something about the process.)

dj

[pbaldy]

Happy to help, and thanks! You may still want the link; you'd actually be able to use it pretty much as-is without having to modify the "FROM" table.