Fill Fields automatically on form based on a control's value

[CM]

Hi everyone,

I am trying to use the code pasted below, which is designed to allow you to automatically select the town and county based on what postcode you enter. I have tried to revise the code based on the database that I have but to no avail.

I have made a table called "tbl-postcodes" which I have three fields, first one is "Postcode", then "Town" then "County"

I have tried the code listed underneath the original, but nothing seems to work.

I also have a second problem.....how can I customise this to be more UK based, i,e if I select SW1 0AA or SW1 2AA it would give the same town, I think it needs to relate to the first 3 or 4 letters instead of the whole postcode, otherwise I would need to include every possible combination of postcode??

Thanks for any help anyone can give me, on this!

Colin


************* Code Start **************
' This code was originally written by Erika Yoxall.
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.
'
' Code Courtesy of
' Erika Yoxall
'
Sub Zip_OnExit(Cancel As Integer)
Dim varState, varCity As Variant
varState = DLookup("State", "tblZipCode", "ZipCode =[Zip] ")
varCity = DLookup("City", "tblZipCode", "ZipCode =[Zip] ")
If (Not IsNull(varState)) Then Me![State] = varState
If (Not IsNull(varCity)) Then Me![City] = varCity
End Sub
'************* Code End **************

The code I have used is the following....

Sub MembersPostcodeTextBox_OnExit(Cancel As Integer)
Dim varCounty, varTown As Variant
varState = DLookup("County", "tblpostcodes", "Postcode =[Postcode] ")
varCity = DLookup("City", "tblpostcodes", "Postcode =[Postcode] ")
If (Not IsNull(varCounty)) Then Me![County] = varCounty
If (Not IsNull(varTown)) Then Me![Town] = varTown
End Sub

 

[Bob Thomas]

This is addressing your second problem where the SW1 2AA & SW1 0AA return the same town. The crieria you requir

Trim( Left ( [PostCode], (Len([PostCode] - 3 ) )

This will eliminate the last three characters of the Postal Code leaving the first 3 or 4 characterer (e.g. SW1 or SW11)
Attempt to build this into the criteria of the 'DLOOKUP'.