How to filter data in a field based on user entry in other field

[Alphabeta]

I am a new access user and need your helps.

I have two tables: City (primary key: city name) and Branch (primary key:
city name + branch number).

Say I have 2 fields (among others) in a form: City and Branch Offices. When
the user enter in a city, I want a combo box shows a list of branch offices
that are valid for that city only, not all of the branch offices. How can I
do that?

Thanks in advance for all your helps

 

[Rob Oldfield]

If the branch combo is called cboBranch then you need to do something like
this in the AfterUpdate event of the City text/combo box...

cboBranch.RowSource = "Select Branch from tblBranch where City='" +
Me.cboCity + "'"

You would also be better, by the way, not using city names as a PK. You can
guarantee that you're never going to have two Birminghams in different
states/countries? If might be unlikely to happen but it's good practise (in
general terms) not to use data that has a 'real life' meaning as a PK.
You'd be better off having ID numbers for each city (say CityID, an
incrementing AutoNumber field). The branch table would then just have a
number in (field called CityID or fkCityID if you prefer - for foreign key)
which would just have a data type of long integer.

 

[Alphabeta]

Thank you very much for your help...

For using the city name as a PK, it would not be causing any problems for my
application since our company has only offices in major cities.