Post code lookup

[Guest]

My database has a table with a list of Sources. The sources are the places
where we advertise. Each postal region has its own code. For example BA =
Wales. I would like to create a field that when a post code is entered it
looks at the code name in the table and inserts the correct source.


New Customer Enquiry, Mr Jones postcode BA13 1TF the form sees BA and
inserts Wales in the source field. This is to stop data entry error by some
receptionists.

I am new to access and would appreciate as much help as possible.



Kind Regards

James

 

[Guest]

There are a number of ways this could be done, but I think the most straight
forward would be to create a table that would have two fields. One would be
the region code and the other region location. We will call them
[REGION_CODE] and [REGION_LOCATION] (clever, huh?). Let's call the table
tblRegions.
So now, in the text box where the user enters the postal code, we will use
the Before Update event to look up the code and populate the location in
another text box.

Private Sub txtPostalCode_BeforeUpdate(Cancel As Integer)
Dim varRegionCode as Variant
Dim varRegionLocation as Variant

varRegionLocation = DLookup("[REGION_LOCATION]","tblRegions", _
"[REGION_CODE] = '" & Left(Me.txtPostalCode, 2) & "'")
If IsNull(varRegionLocation) Then
MsgBox "Region Code " & Left(Me.txtPostalCode,2) & " Not Found"
Cancel = True
Else
Me.txtRegionLocation = varRegionLocation
End If
End Sub

So what happens is when the postal code is input, the Before Update event
fires. It looks in your table for the region code which is the first 2
characters of the postal code. If it does not find the code in the table, it
presents a message box saying the code is not found, cancels the update and
leavess the cursor in the postal code text box. If the code is found, it
populates the region locaton text box with the appropriate location.