Multiple dependant combo boxes.

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

 

[Guest]

hi,
what you are refering to is called cascading combos.
see this sites for exact info.

http://www.mvps.org/access/forms/frm0028.htm
http://www.rogersaccesslibrary.com/TableOfContents3.asp

regards

FSt1

 

[John Vinson]

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.

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.

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];

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?

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

John W. Vinson[MVP]

 

[The Raven]

Thanks very much for that.

 

[The Raven]

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!

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.

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.

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];

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

Yep, got that working today. Works a treat.

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

John W. Vinson[MVP]

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.

 

[John Vinson]

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.

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.

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.

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]