Populating a field based on another field's information

[oinorton]

Hi,

I have a postal code field. With that postal code, I want to be able to fill in another field with the area of the city that correlates with. For example, TZN is North Edmonton. Is there a way to do this? Thank you!

[#2]

@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

[#3]

@Woldman Thanks so much. I will try this!

[#4]

@Woldman 

I put it the following code and I'm getting an error. Can you see anything wrong with it?

[#5]

@oinorton 

Could you post a print screen of the error?

[#6]

@Woldman The error isn't coming up any more but it's not working. Below is my Property sheet for the Postal Codes. Do I have this right?

[#7]

@oinorton 

In this case you should use Me.Three_digit_PC.Column(0) since Area of City is the first column and the index starts at 0 (zero). 

[#8]

@Woldman

I'm getting closer!! I really appreciate your help!

Here is my table:

 

Here is my coding:

This is my form:

What I want is the Area of City to show up in the Area of city field but the Postal code is showing up instead. I've played with the coding but I'm not getting it right. 

Thanks in advance. 

[#9]

@oinorton 

I would set the Row Source of the Three digit PC-combo box to: 

SELECT [Three Digits PC], [City Location] FROM [Area of City];

And in VBA the Three_digit_PC_AfterUpdate subroutine to:

Me.Area_of_City.Value = Me.Three_digit_PC.Column(1)

Since City Location is the value you want and it's now the second column (with index 1).

Hope this work. Good luck.

[#10]

@Woldman THAT WORKED!!! Thank you so much! I really appreciate it!