Combobox Auto Populate issue Access 2003

Combobox Auto Populate issue Access 2003

am 17.04.2008 03:33:51 von Presto

I know you guys like details so here they are. Running Access 2003 but the
database is based on 2002.
I have a nifty new little form to update people's addresses. I want to
select the city and have
the state and zip auto-populate to save time and reduce typos.

My query for the address info:

SELECT tbl_CityStZip.City, tbl_CityStZip.State, tbl_CityStZip.Zip,
tbl_CityStZip.CSZID
FROM tbl_CityStZip
ORDER BY tbl_CityStZip.City;

Some cities in our area have multiple zip codes and the query will show all
records for all cities:
Example results from query:

City St Zip CSZID(Pkey)
Agawam MA 01001 1
Chicopee MA 01020 2
Chicopee MA 01013 3
Springfield MA 01111 4
Springfield MA 01109 5
Springfield MA 01108 6

I have cboAddress1City Row Source is the query qry_lookupCityStZip bound
on column 1 from the query
I have a text box for the State with control source as
=cboAddress1City.Column(1)
I have another box for the Zip with control source as
=cboAddress1City.Column(2)

If I select any city that has a multiple zips, only the first zip code will
populate.
Example: I select Chicopee MA 01013 and the boxes populate with Chicopee MA
01020.
(Well, at least it *kinda* works....It took me three hours to get this
far...)

I do NOT want to use the CSZID to populate in any of the fields-I dont need
it ,
but I know somehow I need the results in the combobox be based on the CSZID
so the rest of the
fields will populate correctly. I'm at my wits end. I'm not sure what I
should change.
If I can get this to work, I can impliment this type of setup for several
fields on my other forms.

Thanks, Presto

Re: Combobox Auto Populate issue Access 2003

am 17.04.2008 06:00:31 von u28780

I've got to tell you that you're going at this from the wrong direction! Not
only do many municipalities have multiple ZipCodes, often a state will have
multiple municipalities with the same name, and mulitple states can have same
name municipalities!

They proper way to set this up would be to enter the ZipCode and then
autofill the city/state from that. That's why the CSRs at most phone order
companies only ask for your Zip, not your city/state.

This holds true not only for this situation; the bound field of any combobox
should be a unique value.

--
There's ALWAYS more than one way to skin a cat!

Answers/posts based on Access 2000/2003

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1

Re: Combobox Auto Populate issue Access 2003

am 17.04.2008 06:26:19 von Presto

Yes, I understand your answer, I am a CSR at a local call center. And yes it
makes sense to do the zip first.

However, this database is for a club membership and most members are local
to the area so I only have about 20 cities listed but as stated, they can
have multiple zips., and the state is always MA so I have a default on that
text box....


"Linq Adams via AccessMonster.com" wrote in message
news:82cb9caa1fa46@uwe...
> I've got to tell you that you're going at this from the wrong direction!
> Not
> only do many municipalities have multiple ZipCodes, often a state will
> have
> multiple municipalities with the same name, and mulitple states can have
> same
> name municipalities!
>
> They proper way to set this up would be to enter the ZipCode and then
> autofill the city/state from that. That's why the CSRs at most phone order
> companies only ask for your Zip, not your city/state.
>
> This holds true not only for this situation; the bound field of any
> combobox
> should be a unique value.
>
> --
> There's ALWAYS more than one way to skin a cat!
>
> Answers/posts based on Access 2000/2003
>
> Message posted via AccessMonster.com
> http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1
>

Re: Combobox Auto Populate issue Access 2003

am 20.04.2008 21:49:37 von Chuck Grimsby

In that case, you can use the code at:
http://www.mvps.org/access/forms/frm0028.htm

to limit the available selections to a specific set of Zip codes.

You might also want to look at:
http://www.mvps.org/access/forms/frm0009.htm

and possibly even:

http://www.mvps.org/access/forms/frm0015.htm
for when new records need to be added in.


On Thu, 17 Apr 2008 00:26:19 -0400, "Presto"
wrote:

>Yes, I understand your answer, I am a CSR at a local call center. And yes it
>makes sense to do the zip first.
>
>However, this database is for a club membership and most members are local
>to the area so I only have about 20 cities listed but as stated, they can
>have multiple zips., and the state is always MA so I have a default on that
>text box....
>
>
>"Linq Adams via AccessMonster.com" wrote in message
>news:82cb9caa1fa46@uwe...
>> I've got to tell you that you're going at this from the wrong direction!
>> Not
>> only do many municipalities have multiple ZipCodes, often a state will
>> have
>> multiple municipalities with the same name, and mulitple states can have
>> same
>> name municipalities!
>>
>> They proper way to set this up would be to enter the ZipCode and then
>> autofill the city/state from that. That's why the CSRs at most phone order
>> companies only ask for your Zip, not your city/state.
>>
>> This holds true not only for this situation; the bound field of any
>> combobox
>> should be a unique value.
>>
>> --
>> There's ALWAYS more than one way to skin a cat!
>>
>> Answers/posts based on Access 2000/2003
>>
>> Message posted via AccessMonster.com
>> http://www.accessmonster.com/Uwe/Forums.aspx/databases-ms-ac cess/200804/1
>>
>

--
Please Post Any Replies To This Message Back To the Newsgroup.
There are "Lurkers" around who can benefit by our exchange!