I have a question concerning a one-to-many relationship in Access 2007

[George Herrick]

I have a form in Access 2007 with a field that the user will enter a ZIP Code. I have a table/query that contains every ZIP Code I need (well over 80,000). The data contains not only the ZIP Codes for each location (with city, county, state) but also for surrounding communities that use the same ZIP Code i.e. the ZIP Code for the main city is 12345 and the surrounding communities do not have their own ZIP Codes but share the one with the main city.

The problem is that the main tables contains addresses that might reference one of these surrounding communities and we would want to maintain that address as entered. If I had a one-to-one relationship it would be easy... but since a single ZIP Code could refer to multiple cities/towns I don't know how to do it. I want the user performing the data entry to be able to enter a ZIP Code. The ZIP Code is then looked up in the ZIP Code table and if it is a one-to-one correspondence than I want it to populate the fields for the city, county, and state (and display them on the form).

If there is a one-to-many correspondence, then I want the user to be able to select from a list of the possible cities and after the selection, the correct city/county/state fields are populated. Is this possible?

[dsatino]

Yes, it's possible. The real question is how to go about doing it.

I'm sure you'll get several different answers on this so you may want to wait a few days and examine all of them before you proceed, but here is what I would do.

When a Zip is entered, count the number of cities associated with it. If you have only one, then populate the city for the record you are creating. If you have more than one, then populate a combobox with all the related cities.

[NeoPa]

Nice idea DSatino.

Always populate a ComboBox with the results. The relevant (bound) fields would be updated in code if either :

1. The record is unique when the ComboBox is populated.
2. The ComboBox is updated.

[George Herrick]

That was my original approach. But my problem is that once I have the combo box list down and the user selects the city that want, the combo box only lets me return a single value (i.e. the city name). How can I return the other fields in the ZIP Code record like the county and state? I need to populate their corresponding fields in the main table/query and display them on the main form (the invoking form) as well.

[NeoPa]

Use a Multi-Column ComboBox. The code to populate your various TextBox controls would use the ComboBox's .Columns property to take each value to it's proper place.