Combo box problem

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
--
AJM1949

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."


"AJM1949" wrote in message
...
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
--
AJM1949

First, when does your code execute?

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

"AJM1949" wrote:

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
--
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


"Christopher Robin" wrote:

First, when does your code execute?

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

"AJM1949" wrote:

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
--
AJM1949

Thanks Al

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


"Al Campagna" wrote:

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."


"AJM1949" wrote in message
...
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
--
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
--
AJM1949


"Christopher Robin" wrote:

First, when does your code execute?

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

"AJM1949" wrote:

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
--
AJM1949

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" wrote:

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
--
AJM1949


"Christopher Robin" wrote:

First, when does your code execute?

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

"AJM1949" wrote:

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
--
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
--
AJM1949


"Christopher Robin" wrote:

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" wrote:

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
--
AJM1949


"Christopher Robin" wrote:

First, when does your code execute?

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

"AJM1949" wrote:

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
--
AJM1949