Dan:

John's query will give you a list of all city/zip combinations after rows
have been inserted into tblCitiesAndZips. For a form for viewing these and
for adding new records to tblCitiesAndZips (don't do it directly in the table
in datasheet view) you'll need two combo boxes set up as follows:

ControlSource: CityID

RowSource: SELECT ID, City FROM tblCities ORDER BY City;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert the last one. The important thing is that the first
dimension is zero to hide the first column and that the second is at least as
wide as the combo box.

And:

ControlSource: ZipcodeID

RowSource: SELECT ID, Zipcode FROM tblZipcodes ORDER BY Zipcode;

BoundColumn: 1
ColumnCount: 2
ColumnWidths: 0cm;8cm

A better option, however, would be form (in single form view) based on
tblCities (or better a still a query based on tblCities which orders the
records by city name) and within it a subform (in continuous form view) based
on tblCitiesAndZips. Link the parent form and subform by setting the
LinkMasterFields property to ID, and the LinkChildFields property to CityID.
In the subform you need just one control, the second combo box above. As you
navigate to each city in the main form you'll see its zipcodes in the subform,
and can add a new one by inserting a row in the subform.

Ken Sheridan
Stafford, England

Dan wrote:
John,
Is this a single expression for the Row Source of a single combo box in
tblCitiesAndZips?
Dan

SELECT tblCities.City, tblZipcodes.Zipcode
FROM (tblCities INNER JOIN tblCitiesAndZips
ON tblCities.ID = tblCitiesAndZips.CityID)
INNER JOIN tblZipcodes
ON tblZipcodes.ID = tblCitiesAndZips.ZipcodeID;

--
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...ccess/201005/1