Populating a field based on another field's information

%3CLINGO-SUB%20id%3D%22lingo-sub-1681693%22%20slang%3D%22en-US%22%3EPopulating%20a%20field%20based%20on%20another%20field's%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1681693%22%20slang%3D%22en-US%22%3E%3CP%3EHi%2C%3C%2FP%3E%3CP%3EI%20have%20a%20postal%20code%20field.%20With%20that%20postal%20code%2C%20I%20want%20to%20be%20able%20to%20fill%20in%20another%20field%20with%20the%20area%20of%20the%20city%20that%20correlates%20with.%20For%20example%2C%20TZN%20is%20North%20Edmonton.%20Is%20there%20a%20way%20to%20do%20this%3F%20Thank%20you!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-LABS%20id%3D%22lingo-labs-1681693%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EAccess%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1682341%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20field%20based%20on%20another%20field's%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1682341%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798204%22%20target%3D%22_blank%22%3E%40oinorton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EYou%20could%20do%20this%20with%20a%20lookup%20table.%20I%20called%20mine%20Areas%20and%20designed%20it%20like%20this%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Areas.png%22%20style%3D%22width%3A%20468px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219464i566FDCE1CFE8BAFF%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22Areas.png%22%20alt%3D%22Areas.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EFill%20this%20table%20with%20the%20combinations%20of%20Postal%20Codes%20and%20Areas%20you%20need.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENext%2C%20we%20go%20to%20the%20form%20with%20the%20Postal%20Code%20and%20Area%20and%20open%20it%20in%20design%20view.%20The%20Postal%20Code%20field%20should%20be%20a%20combo%20box.%20If%20it%20is%20a%20text%20box%20right%20now%20you%20can%20right%20click%20on%20it%20and%20select%20%3CSTRONG%3EChange%20To%20%26gt%3B%20Combo%20Box%3C%2FSTRONG%3E.%20Now%2C%20open%20the%20properties%20for%20the%20Postal%20code%20combo%20box%20and%20set%2Fchange%20its%20Row%20Source%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22properties.png%22%20style%3D%22width%3A%20505px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219465i58FD418E7DFDF89A%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22properties.png%22%20alt%3D%22properties.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EThis%20sets%20the%20row%20source%20to%20the%20Postal%20Code%20and%20the%20Area%20but%20the%20combo%20box%20default%20only%20shows%20the%20first%20column%20which%20hold%20the%20Postal%20Code.%20The%20Area%20is%20there%2C%20but%20it%E2%80%99s%20hidden.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ENow%20we%20can%20use%20this%20hidden%20column%20to%20fill%20the%20Area%20field%20when%20a%20Postal%20Code%20is%20selected.%20We%20do%20this%20with%20a%20little%20piece%20of%20VBA-code.%20Select%20the%20%3CSTRONG%3EEvent%3C%2FSTRONG%3E%20tab%20in%20the%20Postal%20Code%20combo%20box%20properties%20and%20click%20on%20the%20%3CSTRONG%3EAfter%20Update%3C%2FSTRONG%3E%20event.%20Next%2C%20click%20on%20the%20button%20with%20the%20three%20dots%20en%20choose%20%3CSTRONG%3ECode%20Builder%3C%2FSTRONG%3E.%20The%20VBA%20editor%20opens%2C%20and%20an%20empty%20subroutine%20for%20the%20After%20Update%20event%20is%20filled%20in.%20Add%20the%20line%20of%20code%20like%20beneath%3A%3C%2FP%3E%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22snippet.png%22%20style%3D%22width%3A%20783px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F219466i7C1144C9C6AD9ED9%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20title%3D%22snippet.png%22%20alt%3D%22snippet.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EWhen%20a%20post%20code%20is%20selected%20in%20the%20form%2C%20the%20After%20Update%20event%20fires%20automatically%20and%20sets%20the%20value%20of%20the%20area%20to%20the%20one%20that%20goes%20along%20with%20the%20postal%20code.%20Recall%20the%20row%20source%20for%20the%20Postal%20code%20which%20contains%20two%20columns%3A%20one%20visible%20for%20the%20code%20and%20an%20invisible%20one%20for%20the%20area.%20Addressing%20a%20column%20starts%20at%20zero%2C%20so%20the%20Area%20column%20is%20fetched%20with%20index%201.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3ESave%20the%20form%20and%20open%20it%20in%20View%20mode.%20When%20you%20enter%20a%20postal%20code%20and%20hit%20enter%20(or%20leave%20the%20field)%2C%20the%20Area%20field%20should%20be%20automatically%20filled.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EHope%20this%20works%20for%20you.%3C%2FP%3E%3CP%3E%26nbsp%3B%3C%2FP%3E%3CP%3EBest%20wishes%2C%3C%2FP%3E%3CP%3ETieme%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1684872%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20field%20based%20on%20another%20field's%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1684872%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131546%22%20target%3D%22_blank%22%3E%40Tieme%20Woldman%3C%2FA%3E%26nbsp%3BThanks%20so%20much.%20I%20will%20try%20this!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1796998%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20field%20based%20on%20another%20field's%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1796998%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F131546%22%20target%3D%22_blank%22%3E%40Tieme%20Woldman%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3EI%20put%20it%20the%20following%20code%20and%20I'm%20getting%20an%20error.%20Can%20you%20see%20anything%20wrong%20with%20it%3F%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20image-alt%3D%22Coding%20for%20Postal%20Code%20and%20Area%20of%20city.PNG%22%20style%3D%22width%3A%20785px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F227769i1A68C7A447C49558%2Fimage-size%2Flarge%3Fv%3D1.0%26amp%3Bpx%3D999%22%20role%3D%22button%22%20title%3D%22Coding%20for%20Postal%20Code%20and%20Area%20of%20city.PNG%22%20alt%3D%22Coding%20for%20Postal%20Code%20and%20Area%20of%20city.PNG%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-SUB%20id%3D%22lingo-sub-1797958%22%20slang%3D%22en-US%22%3ERe%3A%20Populating%20a%20field%20based%20on%20another%20field's%20information%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1797958%22%20slang%3D%22en-US%22%3E%3CP%3E%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2Fuser%2Fviewprofilepage%2Fuser-id%2F798204%22%20target%3D%22_blank%22%3E%40oinorton%3C%2FA%3E%26nbsp%3B%3C%2FP%3E%3CP%3ECould%20you%20post%20a%20print%20screen%20of%20the%20error%3F%3C%2FP%3E%3C%2FLINGO-BODY%3E
Occasional Contributor

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!

9 Replies

@oinorton 

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

Areas.png

 

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:

properties.png

 

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:

snippet.png

 

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

@Tieme Woldman Thanks so much. I will try this!

@Tieme Woldman 

I put it the following code and I'm getting an error. Can you see anything wrong with it?Coding for Postal Code and Area of city.PNG

@oinorton 

Could you post a print screen of the error?

@Tieme 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?

 

Row source for Postal Coe.PNG

@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). 

@Tieme Woldman

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

 

Here is my table:

Area of Cit Table.PNG 

 

Here is my coding:

Row source for Postal Code.PNG

 

This is my form:

Form appearance.PNG

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. 

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

 

 

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