two fields displayed and populated at the same time

[TerranSky]

We have a database that lists city & state separately and represents
local calling areas. That is, for a given city, the list of cities that
are local to it are listed on the same record (horizontally). The
problem is that we're dealing with an area of the country where cities
in one state are very very very often, local to a city in another state.
So, the city AND the state have to be identified. Further complicating
the matter is that the city and the state code have to be kept separate
so I can't combine them. However, due to the complicated nature of the
region, we have to be able to enter both pieces of information. What
I'm trying to figure out is a way to do a drop down box that shows both
the city and state and can populate both fields when a given city-state
combination is selected. Anyone know of VB code that can do this?
Evidently, I'm not very good at VB because I can't figure it out. I'll
be happy to email a section of the data that I'm referring to.

As background, this is telephone information and if there's one thing
that I've learned about telephone data is that Access was never designed
to handle it. Getting one to one and one to many relationships is all
but impossible so I always feel as if I have one hand tied behind my
back.

 

[John Nurick]

I get the impression that you need a structure like this:

tblCities
CityID Autonumber primary key
City Text (name of city)
State Text (2 characters)

tblCitiesLocal
CityID Foreign key into tblCities.CityID
LocalCityID FK into tbkCities.CityID
(primary key consists of both fields)

So if tblCities contains
1,Alphaville,MD
2,Beta City,VA
3,Gammatown,MD

the fact that Beta City is local to Alphaville would be stored in
tblCitiesLocal as
1,2
and the fact that Alphaville and Gammatown are local to Beta City would
be stored as
2,1
2,3

Then there'd be no problem having a combobox with a dropdown showing
both city and state, but you'd only actually store the CityID.

To get a "horizontal" list of local cities (with state) from this
structure, use the technique described at
http://www.mvps.org/access/modules/mdl0004.htm