I have an address database that lists all our suppliers (tblSuppliers). I'm
tired of entering all the cities AND zipcodes, so I created a second table
(tblZipCode) that lists all the zip codes and what city it is associated
with. I then joined these tables together.

I have a form on which all the suppliers are tracked (frmSuppliers) that is
tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
have the city automatically populate based on the zipcode entered. Is this
possible? If so, how?

--
Thank you! - Jennifer

Try this: http://www.mvps.org/access/forms/frm0009.htm

Not an expert, but incidentally I just read the article before I read
you post. Hope it helps.

-Sam

Jennifer Cali wrote:
> I have an address database that lists all our suppliers (tblSuppliers). I'm
> tired of entering all the cities AND zipcodes, so I created a second table
> (tblZipCode) that lists all the zip codes and what city it is associated
> with. I then joined these tables together.
>
> I have a form on which all the suppliers are tracked (frmSuppliers) that is
> tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
> have the city automatically populate based on the zipcode entered. Is this
> possible? If so, how?
>
> --
> Thank you! - Jennifer

Jennifer, if you create a simple query compiling the zip table(and associated
auto fill information - City, State, County...) with the supplier table, and
then use the query as the data source for your form, the City, State,
County... should auto fill in the form.


"Jennifer Cali" wrote:

> I have an address database that lists all our suppliers (tblSuppliers). I'm
> tired of entering all the cities AND zipcodes, so I created a second table
> (tblZipCode) that lists all the zip codes and what city it is associated
> with. I then joined these tables together.
>
> I have a form on which all the suppliers are tracked (frmSuppliers) that is
> tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
> have the city automatically populate based on the zipcode entered. Is this
> possible? If so, how?
>
> --
> Thank you! - Jennifer

Jennifer,

Probably the "purest" approach would be to remove the chrZipCode field
from the tblSuppliers table. If any given zipcode is always assiciated
with a particular city, then it is redundant to have both fields in both
tables. Then, you can make a query, that includes tblSuppliers table
and tblZipCode table, joined on chrZipCode field, and include the City
field from the tblZipCodes table in the query. Use this query as the
Record Source of your frmSuppliers form (and any other form or report
where a supplier's city is required. I think that will give the
functionality you require. The proviso I would make here is that on
your form, you will probably want to set the Locked property of the City
control to Yes - this is not the place to allow editing of your
city/zipcode lookup master table.

--
Steve Schapel, Microsoft Access MVP

Jennifer Cali wrote:
> I have an address database that lists all our suppliers (tblSuppliers). I'm
> tired of entering all the cities AND zipcodes, so I created a second table
> (tblZipCode) that lists all the zip codes and what city it is associated
> with. I then joined these tables together.
>
> I have a form on which all the suppliers are tracked (frmSuppliers) that is
> tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
> have the city automatically populate based on the zipcode entered. Is this
> possible? If so, how?
>

Hi Steve,
That all seems to make sense except for the very first sentence. If I remove
the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?

--
Thank you! - Jennifer


"Steve Schapel" wrote:

> Jennifer,
>
> Probably the "purest" approach would be to remove the chrZipCode field
> from the tblSuppliers table. If any given zipcode is always assiciated
> with a particular city, then it is redundant to have both fields in both
> tables. Then, you can make a query, that includes tblSuppliers table
> and tblZipCode table, joined on chrZipCode field, and include the City
> field from the tblZipCodes table in the query. Use this query as the
> Record Source of your frmSuppliers form (and any other form or report
> where a supplier's city is required. I think that will give the
> functionality you require. The proviso I would make here is that on
> your form, you will probably want to set the Locked property of the City
> control to Yes - this is not the place to allow editing of your
> city/zipcode lookup master table.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Jennifer Cali wrote:
> > I have an address database that lists all our suppliers (tblSuppliers). I'm
> > tired of entering all the cities AND zipcodes, so I created a second table
> > (tblZipCode) that lists all the zip codes and what city it is associated
> > with. I then joined these tables together.
> >
> > I have a form on which all the suppliers are tracked (frmSuppliers) that is
> > tied to tblSuppliers. I want to enter the zipcode in the chrZipCode field and
> > have the city automatically populate based on the zipcode entered. Is this
> > possible? If so, how?
> >
>

Jennifer,

Just checking to make sure you were paying attention <g>.

Apologies for the slip - thank you, you are exactly right! :-)

--
Steve Schapel, Microsoft Access MVP

Jennifer Cali wrote:
> Hi Steve,
> That all seems to make sense except for the very first sentence. If I remove
> the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
> tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?
>

Jennifer,

The other "slip" I made was that your query should have a Left Join
between the tables, just in case someone enters a zipcode that does not
exist in the tblZipCodes table.

--
Steve Schapel, Microsoft Access MVP

Steve Schapel wrote:
> Jennifer,
>
> Just checking to make sure you were paying attention <g>.
>
> Apologies for the slip - thank you, you are exactly right! :-)
>

Haha - I'm always paying attention (yea, right!). Steve, thank you so much
for your help - this solution worked perfectly.
--
Thank you! - Jennifer


"Steve Schapel" wrote:

> Jennifer,
>
> Just checking to make sure you were paying attention <g>.
>
> Apologies for the slip - thank you, you are exactly right! :-)
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Jennifer Cali wrote:
> > Hi Steve,
> > That all seems to make sense except for the very first sentence. If I remove
> > the chrZipCode from tblSuppliers, how would I join together tblSuppliers and
> > tblZipCodes? Wouldn't I want to removve the chrCity from tblSuppliers instead?
> >
>

Ooh...I don't know how to do this. Can you tell me where the left join goes?
--
Thank you! - Jennifer


"Steve Schapel" wrote:

> Jennifer,
>
> The other "slip" I made was that your query should have a Left Join
> between the tables, just in case someone enters a zipcode that does not
> exist in the tblZipCodes table.
>
> --
> Steve Schapel, Microsoft Access MVP
>
> Steve Schapel wrote:
> > Jennifer,
> >
> > Just checking to make sure you were paying attention <g>.
> >
> > Apologies for the slip - thank you, you are exactly right! :-)
> >
>

Jennifer,

In the design view of the query, using exquisite manual dexterity,
double-click on the line joining the tables. This should open the Join
Properties dialog. Select the 2nd option, which should say something
like "all records from tblSuppliers and only matching records from
tblZipCodes".

--
Steve Schapel, Microsoft Access MVP

Jennifer Cali wrote:
> Ooh...I don't know how to do this. Can you tell me where the left join goes?