Populating a field based on another field's information

oinorton 

You could do this with a lookup table. I called mine Areas and designed it like this:

Fill this table with the combinations of Postal Codes and Areas you need.

Next, we go to the form with the Postal Code and Area and open it in design view. The Postal Code field should be a combo box. If it is a text box right now you can right click on it and select Change To > Combo Box. Now, open the properties for the Postal code combo box and set/change its Row Source:

This sets the row source to the Postal Code and the Area but the combo box default only shows the first column which hold the Postal Code. The Area is there, but it’s hidden.

Now we can use this hidden column to fill the Area field when a Postal Code is selected. We do this with a little piece of VBA-code. Select the Event tab in the Postal Code combo box properties and click on the After Update event. Next, click on the button with the three dots en choose Code Builder. The VBA editor opens, and an empty subroutine for the After Update event is filled in. Add the line of code like beneath:

When a post code is selected in the form, the After Update event fires automatically and sets the value of the area to the one that goes along with the postal code. Recall the row source for the Postal code which contains two columns: one visible for the code and an invisible one for the area. Addressing a column starts at zero, so the Area column is fetched with index 1.

Save the form and open it in View mode. When you enter a postal code and hit enter (or leave the field), the Area field should be automatically filled.

Hope this works for you.

Best wishes,

Tieme