Limiting choices in a Combo Box based on another field

[RichKorea]

When creating a record in a service table, I want to be able to select a
customer name in the Customer Field from a table of customers (which I can
do), and then based on that Customer Field, I want to limit the choices of
Customer Locations to the locations that are associated with that customer.
I have a Customer Location table that lists Customer A: Location 1, Customer
A: Location 2, Customer B : Location Q, etc.

Is my table setup the optimum way to do this (service table, customer table,
and location table)?

How can I use the Customer name that's selected in the Customer Field to
restrict the locations to the ones that are valid just for that customer? I
was able to use Query Builder from Row Source to connect the two fields, and
it worked once, but if I changed the Customer Field, I the list of locations
in the combo box didn't change, and then when I went back a little while
later, I would get the same mixed list of locations no matter which customer
I selected.

I'm running Access 2003, and this is the first time I've tried to setup a
database with a connected field, so I may be using the wrong terms to
describe what I'm trying to do.

Thanks

 

[KARL DEWEY]

Search the newsgroup on 'Cascading Combos'.

 

[RichKorea]

Thanks Karl for the guidance. The search took me to a post "Cascading combo
boxes in a subform", started by Names, that lead me to an example on
Microsoft at http://office.microsoft.com/en-us/access/HA011730581033.aspx
that I was able to build off of to get what I wanted. It took me a while to
figure out about setting the columns to 2 and the column widths to 0";1" so
the data I wanted would display and not the key, but now I got what I want.
I also figured out that I can put a value in a text box with a related value
from the query by pulling the value from column 3 of the combo box.