Access equivalent of excel Vlookup

[oldlearner]

Hi all. I have 2 tables. Table 1 has 2 fields PostCode and Area. Table 2 also has 2 fields, postcode2 and Area2 which are populated with data. When I type a post code into table 1, I want it to look at table 2 to match the post code in column 1 and return the value from Area2 into the Area field in table 1. This is achieved in Excel with a Vlookup but I would like to know how to achieve this in access. Many thanks for any assistance anyone can give

[isladogs]

why do you need 2 tables with matching data?
This is normally unnecessary in a database where one principle is that data is stored once and used in multiple ways as necessary.

If you really must do this, you would use an update query
Join the 2 tables using the Postcode field.
Add the table1 Area field to the query and in the Update To row enter Table2.Area.
Run the query to synchronise all matching Area fields in one go.

Another thing - you can obtain all postcode data for free for most countries.
If you want UK postcode data I can supply this for you or tell you places to download it

[pbaldy]

Does this fit the situation?

http://www.baldyweb.com/Autofill.htm

[oldlearner]

Not sure if I explained well enough, to clarify, Table 2 has 24932 records with Post codes and associated Ward areas listed in column 2. When I type a post code in table 1, I want it to look up the identical post code in table 2 and return the associated ward area from column 2 to the ward field In table 1. This is a common task in excel accomplished by using a Vlookup.

[isladogs]

That really doesn't help much.
First you mentioned Area...now its Ward.

Why would you type a postcode in table1 when you have the data in table2?

You're probably wanting to use the DLookup function.

[pbaldy]

Also, most of us never let users work directly in tables or queries, only forms and reports. The link I posted would work in a form, and as Colin and my link state you would not normally save the area in table 1.

[June7]

Another voice of reason, I hope.

I presume Table1 has more than just those 2 fields.

Don't save Table2 Area2 (ward) data into Table1. Build query that joins on the common PostCode fields to retrieve related data. This is a primary reason for using relational database.

If you want to see the Ward next to PostCode on a form, options:

1. pbaldy suggestion using a multi-column combobox for selecting PostCode, then textbox can reference combobox column by its index, index begins with 0: =[cbxPC].Column(1)

2. include Table2 in form RecordSource (RIGHT JOIN) and bind textbox to Table2 Area2 and set Locked Yes and TabStop No

3. DLookup() expression in textbox

[oldlearner]

Thanks for all your comments, they are much appreciated. I was trying to reproduce a Vlookup procedure In access and as you have all noted I was barking up the wrong tree. As you rightly say it was a simple lookup and query to solve it. can you advise if I should index the post code field as there are over 25,000 post codes in it. Many Thanks

[isladogs]

We were all glad to help.
Yes - you should index any field used in searches. It will significantly speed up your searches
even if you had e.g. the full UK postcode set of approx 2.6 million postcodes, indexing means it would still be near instantaneous