Multiple dependant combo boxes.

  • Thread starter Thread starter The Raven
  • Start date Start date
T

The Raven

OK,
Be gentle this is my first post here and I don't proclaim to be an
Access expert or anything.

Believe it or not I'm trying to help a less Access savvy user who's about a
1000miles from me....

Here's the deal:

Single form with 3 combo boxes.
First combo box picks a value from a table.
Second combo box is supposed to use the value from the first combo as the
table name to be used as the recordsource.
Third combo box does the same.

For example: If 'Bob' is selected for combo1 then combo2 will use table
'Bob' as the recordsource. Combo3 will then use whatever is selected in
combo2 as the recordsource table.

Note that each table is a very simple one or two field table. The guy I'm
helping wants to keep things simple so, building a full blown matrix table
isn't feasible.

I know I could do this myself, given some time........which I don't have. I
can see several ways of doing it, mostly involving some coding. However, the
user I'm helping doesn't code and would struggle with putting together a
simple SELECT statement. Trying to communicate this verbally over the phone
is near impossible....

I tried a very amatuerish technique of having the second combo have a
recordsource SQL statement.

Eg. SELECT * FROM [Forms]![Formname]![Combo1];

Obviously this didn't work as the expression after FROM could not be
processed. Too bad that it doesn't work, it would be an excellent (if crude)
solution.

Any simple way to do this?

Any URLs where I could find such a sample database to explain it all to him?

Regards and thanks in advance.
 
OK,
Be gentle this is my first post here and I don't proclaim to be an
Access expert or anything.

Believe it or not I'm trying to help a less Access savvy user who's about a
1000miles from me....

Here's the deal:

Single form with 3 combo boxes.
First combo box picks a value from a table.
Second combo box is supposed to use the value from the first combo as the
table name to be used as the recordsource.
Third combo box does the same.

For example: If 'Bob' is selected for combo1 then combo2 will use table
'Bob' as the recordsource. Combo3 will then use whatever is selected in
combo2 as the recordsource table.
Click to expand...

WHOA. Stop right there.

If you are storing data in tablenames - a table for Bob, a table for
Fred, a table for Janet - you're WAY off track!

Instead, use *one* table, with an additional field identifying Bob or
Fred or Janet.
Note that each table is a very simple one or two field table. The guy I'm
helping wants to keep things simple so, building a full blown matrix table
isn't feasible.
Click to expand...

Prolifierating additional tables for each person makes things more
*complicated*, not simpler. The time and effort spent building a
properly normalized set of tables, one table for each type of Entity,
will be repaid many times over in your later development work; jumping
into forms and code too soon will just get you bogged down in the
swamp.
I know I could do this myself, given some time........which I don't have. I
can see several ways of doing it, mostly involving some coding. However, the
user I'm helping doesn't code and would struggle with putting together a
simple SELECT statement. Trying to communicate this verbally over the phone
is near impossible....

I tried a very amatuerish technique of having the second combo have a
recordsource SQL statement.

Eg. SELECT * FROM [Forms]![Formname]![Combo1];
Click to expand...

You'll need to write VBA code to construct the SQL string, and assign
the second combo's RowSource property to that text string.
Obviously this didn't work as the expression after FROM could not be
processed. Too bad that it doesn't work, it would be an excellent (if crude)
solution.

Any simple way to do this?
Click to expand...

Yes: restructure your tables so you don't NEED to do this.

John W. Vinson[MVP]
 
John Vinson said:
WHOA. Stop right there.

If you are storing data in tablenames - a table for Bob, a table for
Fred, a table for Janet - you're WAY off track!
Click to expand...

The data isn't in tablenames, it's stored in tables. First combo selection
is from Table A. The value may be BOB so the second combo sources it's rows
from table BOB.

Yes, I appreciate that this isn't necessarily ideal but given the scope of
this users request it's OK.
Instead, use *one* table, with an additional field identifying Bob or
Fred or Janet.
Click to expand...

The problem I see with your suggestion (correct me if I'm wrong) is that you
end up with a mega matrix/array. This becomes onerous to manage for this
simple system, growth would exponentially increase the table size and any
additional combo "levels" would become unmanagable.

What we want is each combo selection to bring up a unique subset of
selections for th next combo box. Simple, linear table selections seem to
be applicable here.
Prolifierating additional tables for each person makes things more
*complicated*, not simpler. The time and effort spent building a
properly normalized set of tables, one table for each type of Entity,
will be repaid many times over in your later development work; jumping
into forms and code too soon will just get you bogged down in the
swamp.
Click to expand...

Hmmm, I do see you point. I could probably deal with this but I'm not sure
this guy could.
I know I could do this myself, given some time........which I don't have.
I
can see several ways of doing it, mostly involving some coding. However,
the
user I'm helping doesn't code and would struggle with putting together a
simple SELECT statement. Trying to communicate this verbally over the
phone
is near impossible....

I tried a very amatuerish technique of having the second combo have a
recordsource SQL statement.

Eg. SELECT * FROM [Forms]![Formname]![Combo1];
Click to expand...

You'll need to write VBA code to construct the SQL string, and assign
the second combo's RowSource property to that text string.
Click to expand...

Yep, got that working today. Works a treat.
Yes: restructure your tables so you don't NEED to do this.

John W. Vinson[MVP]
Click to expand...

Appreciate you comments and will pass on the suggestions.

Oh, stupid me gets it all now....it's basically what I do each day without
realising it, the combo rowsource problem threw me off track.
 
The data isn't in tablenames, it's stored in tables. First combo selection
is from Table A. The value may be BOB so the second combo sources it's rows
from table BOB.
Click to expand...

Then you're storing data - the name BOB - in a tablename. Exactly what
I'm objecting to!
Yes, I appreciate that this isn't necessarily ideal but given the scope of
this users request it's OK.
Click to expand...

Well... No. It's not.
The problem I see with your suggestion (correct me if I'm wrong) is that you
end up with a mega matrix/array. This becomes onerous to manage for this
simple system, growth would exponentially increase the table size and any
additional combo "levels" would become unmanagable.

What we want is each combo selection to bring up a unique subset of
selections for th next combo box. Simple, linear table selections seem to
be applicable here.
Click to expand...

You are in fact WRONG. I'm not suggesting a megamatrix; I'm suggesting
a tall-thin table with three fields.

Right now - if I understand correctly - you have a table named BOB
with two fields (maybe more, but the two you're using for the combo);
another identically structured table named JANET with two fields, and
so on.

I'm suggesting a single table with *three* fields (not thirty, not
three hundred - three!); one of these fields will be called
PersonName, and will have the values "BOB", "JANET", etc.

A Query with a criterion

="BOB"

or, more to the point, a query with a criterion

=[Forms]![yourform]![Combo1]

on the PersonName field will retrieve precisely and exactly the same
information as is now stored in your table BOB.

If your actual problem is different from your example, and I'm
misunderstanding things, please explain with some real table names and
structures. But I will continue to insist that having one combo select
the table name to be used as the rowsource of a second combo is *bad
design*.

John W. Vinson[MVP]
 
Back
Top