Linkign Excel Sheet t look up data (1 Viewer)

[Shazz]

Hi All,

Can anyone tell me if and how you can use an Excel Spreadsheet with loads of Towns,Counties and The first 4 didgits of all postcode to lookup info in a Access Form.

Basically I have a spreadsheet with the data in, I want to be able to Type a Full postcode in a Field and have the Town and County automatically filled in from the data on teh Excel Spreadsheet.

Can anyone help please, in simple terms though I am still learning all this.

Thnaks in advance.

Shazz
.

 

[DevastatioN]

You want to look at just importing all that data into the database, but if you wish to do it as you described.

You need to create a new "Link Table", referencing that Excel file. In the table window, click "New", Select "Link Table" and press Ok, change the "Files of Type" to Excel and navigate to your file and click "Link"

There is a bit more setup to make sure you get the columns and headings right etc.

Now you can reference this Linked Table in queries and code like you would a normal table.

In the postal code textbox, you would use code like the following in the after update event (or make a button that says "hey fill in the other data" on click event)

Me.txtCountry = DLookup("[CountryField]", "tblLinkTable", "PostalCode = '" & Me.txtPostalCode & "'")

So the textbox txtCountry becomes the lookup of the Excel Column named "CountryField", in the Linked Table named "tblLinkTable", and it grabs the value where typed in postal code from txtPostalCode matches the Excel Column named "PostalCode".