Forms, Subforms, Command buttons

[kalmi]

I am trying to build a database for my department to track our projects. Each project has a number. Each project will involve several different cities and each city can be affected by multiple projects. Each city has an ID number. So I have 3 tables so far:

01 _tbProjects – lists the basics about each project including the Project#
02_tbProjCity – correlates project number with city ID#
03_tbAllCities – list of all cities we are liking to be working with by ID#

I have a form 01_fProjects where someone can input the basics about the project. There is a subform on that form to populate the 02_tbProjCity table. Basically at the top someone types in the project basics and on the bottom they can type in the ID numbers of the cities involved. I would like to make this easier to use so I have two questions:
1. Since the city ID# is not very intuitive I would like to include a box to the side linked to the 03_tbAllCities table where someone can use a series of combo boxes (state, county, community name) to find out the city’s ID#. Can this be done?
2. To make it even easier once the city ID# is determined I would like to insert a command button that will put the city ID# in the 02_tbProjCity when you click it. Any suggestions on how this can be done?

I’m moderately experienced with Access but can’t quite figure out how this can be accomplished. Any suggestions would be appreciated.

 

[Galaxiom]

Search the site for Cascading Comboboxes.

 

[kalmi]

I managed to get the cascading unbound combo boxes to work correctly so someone can look up the city's ID. Now I would like to be able to click a command button to take the value from the last combo box (city ID#) and insert it into the subform that shows what city ID#s are linked to the project number. Any ideas?

 

[Galaxiom]

Do you have the city displayed in the combo but the value helds as the ID?
This is what you want. No need for the user to ever see the ID.

One this is right then:
Set up the subform record source query with the combobox as the criteria in the where clause.
Refer to the combo as:
Forms!formname.comboname

You don't need a button. Use the On Change event of the combo to requery the subform.
This will automatically show the matching records when you change the selection in the combobox.

Just put this line in the combo's On Change event procedure:
Me!subformname.Form.Requery