Autopopulate in Form

[Guest]

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?

 

[Sam]

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

 

[Guest]

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.

 

[Steve Schapel]

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.

 

[Guest]

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?

 

[Steve Schapel]

Jennifer,

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

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

 

[Steve Schapel]

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.

 

[Guest]

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

 

[Guest]

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

 

[Steve Schapel]

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