Drop down lists

[JAMIESTEVELEE]

I have created a database where I need a set of records from one query
to drop down in a column depending on the value in another column.
Specifically, my database is for a company that gives guitar lessons,
it has three tables: customers, bookings and teachers and each of the
teachers teach certain guitars (acoustic, electric, bass). I have
created three queries, one for each type of guitar so one has all of
the teachers who can play acoustic, one for electric and one for bass.
In the bookings table, I want the user to pick the type of guitar they
want to learn in one column (type of guitar) and in the next column
(teacher) all of the teachers who can play that type of guitar. So if
the customer picks acoustic in the 'type of guitar' column, only the
teachers who can play that type of guitar appear in the drop down list
in the 'teacher' column.
I hope that made sense.

Many thanks,
JSLee

 

[Jeff Boyce]

I may be over-reacting to the terms you used. If so, my apologies...

It sounds like you are working directly in the tables. If so, stop! First,
you will not be able to get one combo box to depend on another if you stay
in the tables. This is something, however, that is very easily handled in
forms (see "Cascading Combo Boxes"). In Access, tables store data, forms
display it.

This brings up a second (possible) point. If you are using "drop down"
directly in your tables, you have probably set up a "lookup" data type
field. Check the "tablesdbdesign" newsgroup posts for the many reasons not
to do this. The primary reason, in my mind, is that one thing is stored,
while a different value is displayed - this is confusing!

As it happens, switching over to using Forms to display your data gives you
a way to handle that issue, too. Again, check Access HELP and Google.com
for "Cascading Combo Boxes".

Regards

Jeff Boyce
Microsoft Office/Access MVP