dlookup help please

[jon182]

i have the following tables with customer details filled, address, county, town, and post code.

I would like to use the dlookup function in a form so that when the post code is entered the town, county and address are automatically filled thus avoiding errors.

The tables are all linked correctly

Jon

 

[ChrisBelzona]

Hi Jon

Is it your intention to populate the customer address details fields in the customer table or just to display the full address on the form(this may take some time when your moving from record to record)?

I am asuming you have a table with every post code in it?

Once I know what you are after I should be able to give you some code.

Cheers

Chris

 

[Craig0201]

Jon,

This is 3+ DLookups.

It would therefore be quicker to do using a recordset.

Something like this is the post code after update event....

Private Sub txtPostcode_AfterUpdate()

Dim db As Database
Dim rst As Recordset

Set db = CurrentDB
Set rst = db.OpenRecordset("SELECT * FROM PostcodeTable WHERE PostCode = '" & txtPostcode & "'&quot

txtAddress1 = rst!Address1
txtAddress2 = rst!Address2
txtAddress3 = rst!Address3
txtTown = rst!Town
txtCounty = rst!County

End Sub

But.....quick point......

Your post suggests you've got the tables normalised incorrectly. If you've got a table with the post codes.....then all you need to store is the house number or house name and the post code.

Craig

 

[jon182]

Hi thanks for replys

I have a table with post codes in it, once the post code is selected i would like to display the full address on the form

Jon

 

[Craig0201]

Yep....

That's no problem.....

The full address (minus house number) should be stored in the postcode table....

The display should be done via a query....but there's no need to store the data twice....

Craig