Combo box problem

[AJM1949]

I have a 3 column combo box that displays City, State and Postcode. My
problem is that when I select a City, Access always enters the state and
postcode from the 1st record in the combo box, not the one selected. The city
names have some duplicates but in different states. How can I get it to use
the City selected only?

I use this code to fill the State and postcode text boxes
Me.Postcode = Me.City.Column(2)
Me.State = Me.City.Column(1)

Many Thanks in advance
I am a newbie to Access

 

[Al Campagna]

AJM,
When you select a City from a combo, that value, in itself, is not
unique enough to determine which is the appropriate State or Zip. From
among the possible duplicate City entries, Access selects the first to meet
your criteria.
Use the combo to select a Zip, and to determine the proper City and
State.
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."

 

[Christopher Robin]

First, when does your code execute?

Secondly, what is the source for the data in your combo box?

 

[AJM1949]

The code executes afterupdate in the City combo box. Data source is a table
called Localities. It's a bit complicated fro me to split this table. There
are duplicate entries in all fields but combinations of City State and
Postcoed are unique

I guess i can alway move the update to the State Field and manually select
the State and fill the postcode textbox, but would prefer to do it it one
step after selecting a city.

 

[AJM1949]

Thanks Al

Unfortunately we don't always know the postcode when entering new data, but
do know the city and state.

 

[AJM1949]

Hi Christopher

Just a bit more info about the localities table.

Cities exist in more than 1 state.
Postcodes apply to more than one city in the same state

Any assistance in how to split this table would be helpful. The
relationships of this are a bit complicated for my newbie brain to understand
just yet.Access wizard suggest not splitting this table.

Cheers

 

[Christopher Robin]

It's kind of odd that I never saw your reponse until much later. I saw
another response here, before yours.

A quick and dirty solution is to add a new column to your table, so that
Access is able to determine the uniqueness of the rows. A simple ID column
would do the trick, and you could hide it in your combo box.

I'm not sure, how you determine uniqueness in this table, but it seems like
you would have issues updating records without a unique identifier. DB
engines tend to dislike ambiguity.

 

[AJM1949]

Many Thanks Christopher

I used the ID for the table and hid it on the combo box and it works fine.

As I learn more I will look at splitting the table to make things more
efficient

Cheers