Results from table

[hullstorage]

Hi all,
Can somebody refresh my memory:

I have 2 tables one with full UK Postcode and the other with Customer Address.

I have created a form and trying to when i enter a customers postcode the fields with the address are updated by the street, town and county from the matching record in the postcode table



any ideas please

 

[Access_guy49]

You will want to create a record set and then search for the record using SQL and then populate the desired field.

This code should get you on your way.
It should probably go in your Afterupdate function for your Postalcode textbox on your form. You will have to Modify it to loop through so that it searches for All 3 things you want.

Dim strSQL As String
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset

rst.ActiveConnection = CurrentProject.Connection
rst.CursorType = adOpenStatic
rst.Open "Select * from tbl_Postalcode"

'Your search string
strSQL = "[PostalcodeField] = " & [FormName].[Name of field where postalcode was entered]

rst.Find strSQL

'This will hold the value of your specific cell
Dim myvalue as string

myvalue = rst.Fields("Field you want to get (Street,Town or County)").value

me.Street (or town or county) = myvalue


rst.close
set rst = nothing