How can I get a field to auto-fill based on another field?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

artemis1027 - 10 Aug 2006 16:49 GMT

I have a form in which I enter consumer information. My co-workers would
like it if when they enter a zip code it automatically fills in a region
letter (i.e. zip code =19111, region=A, zip code=19149, region=B, and so
forth). Each region has about 10 zip codes assigned to it. Do I need to
create another table and/or subform, or can I just take care of this with an
event, code, or macro? Please be very detailed when replying as I am new
with this kind of thing.

Thanks!!

Reply to this Message

Al Camp - 10 Aug 2006 17:10 GMT

Artemis,
Well, somewhere, you need the info as to which Region is associated with which
ZipCodes...

This solution is based on the premise that Regions and Zips might change in the future
(reorganization, office closings, etc..)
Try adding the RegionCode to your ZipCode file. The ZipCode field on your form should
be combobox (ex. cboZipCode). Add the Region field to that combo in the second column.
Using the AfterUpdate event of cboZipCode, update the Region field...
Me.Region = cboZipCode.Column(1)
(combos are numbered col 0, 1, 2, 3, etc.)

If Regions NEVER change, then a calculated Region field on the form would be bset..
The Region ControlSource would be...
=cboZipCode.Column(1)
That will always "display" the correct Region for the Zip selected. Since you capture
ZipCode, you can always "re-derive" the Region in any subsequent query, form, report...
"on the fly."

Signature

hth
Al Camp
Candia Computer Consulting - Candia NH
http://home.comcast.net/~cccsolutions

>I have a form in which I enter consumer information. My co-workers would
> like it if when they enter a zip code it automatically fills in a region
[quoted text clipped - 5 lines]
>
> Thanks!!

Reply to this Message