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

[Guest]

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

 

[Al Camp]

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