Unanswered: Access Postcode look up and auto complete
Hi, hope someone can help.
Have got a database with a form to enter addresses into. Have also got a table that contains all of the UK postcodes plus the relevant street names.
All I want to do is be able to type in the postcode on the form and have it add the street name to another field on that form automatically. Is there anyway I can do this. I'm a bit of an amateur but once I get into it I can do some basic programming!
Technically, I can answer, but first: it sounds strange - do you have only 1 street name corresponding with each post code? Normally you would end up with a list of matching street names that all share that particular post code. The other way around seems more appliccable and time saving: Typing only a part of the street name in a drop-down list, then selecting the desired street from that list not having to complete it manually, and having the correct post code automatically put in another field...
Anyway, technically you could do it like this:
(taking your own post code example for the fields now, even if that way does not make "sense")
Having zipcode as fieldname in both the main table where you want to store new records of clients or whatever it is, AND that field in the lookup-table that contain all the codes/streets etc.:
Create a query based on the main table that includes both the zipcode from the main table, plus the ID field, zipcode field AND the street name field from the lookup table, setting a relationship in the query between the two zipcode fields.
Then, on the form, create a standard drop-down list box that gets the zipcode value from the zipcode field in the LOOKUP table, storing only colunm 1 (ID number representing that entry in the lookup table) in the "zipcode" field of the main table. So far, "standard procedure", not storing other than numeric references to lookup table entries.
Now, do you need to store the second value (here: street name), or only for show/print? If for show, you only include a locked text field bound to the streetname1 field on the query (which comes from the LOOKUP table.)
If, however, you need to store that value (here: streetname), you create a second field, for example streetname2, for which the underlying query links to a streetname field in the main table. Then you hide the streetname1 field and set the afterupdate event of that field to put its value in the streetname2 field (which should be visible, I guess).
Example AfterUpdate event:
me.streetname2 = me.streetname1
(field number one, streetname1, not storing anything, just fetching when selecting in the list, and either showing itself if you do not have to store the value, or set to hidden, auto-copying the value to the second field, streetname2, which is both visible and storing the value in the main table)
You should set the Tab Stop property for the street name field(s) to No.
Now, when typing in the drop-down list, you will get to select all matching values, picking one from the list, and having the street name field(s) automatically receiving their value.
In the BeforeUpdate event of your Forms' PostCode entry box:
Dim Strg As Variant
Strg = DLookUp("[PostCodeTableStreetNameField]", "PostCodeTable", _
"[PostCodeTablePostCodeFieldName] = '" & Me.myFormsPostCodeTextBoxName & "'")
If Not IsNull(Strg) then Me.myFormsStreetTextBoxName = Strg
If you do indeed have more than one street name associated with any particular PostCode then do as suggested earlier and convert the street name textbox into a combo box and populate it with the available street names (instead of the code above) and select the proper street name that way.
You can populate the comboBox by placing this code (instead) into the BeforeUpdate event of your Forms' PostCode entry box:
Me.myFormsStreetComboBoxName.RowSource = _
"SELECT PostCodeTable.PostCodeTablePostCodeFieldName _
FROM PostCodeTable WHERE ((PostCodeTable!PostCodeTablePostCodeFieldName = _
'ALL MUST BE ONE LINE.
When the Post Code is entered and focus has left the entry box then the Street name combo box will be populated. All you need do is select the desired street.
Regarding the POST CODE issue. From my vague knowledge (boy is it vage) the postcode system(UK) is laid out as
Example: CF47 = street group location 3PQ normally applies to a group of approximatly 4 addresses these are normally domestic (houses) Business can have the first digits AND the last 3 purely for that address but i belive its rare. If I remember correctly the Office that allocates new codes to the UK is based in Bournmouth and they can help to clarify.
Using Lookup to help populate is useful but the above has to be considered as well other wise to populate the code will require the address number as well or youll have for instance
Redrow Drive being entered and the first part of the code CF47 coming in but youll have a multiple set of codes for the last 3 digits.......
The user will have to be forced to enter the full start of the address for the code to correctly populate
Hope Ive made this clear enough and more importantly put a safegard in
Thanks you guys, all your messages have been really helpful. I have now managed to get it do do exactly what I want it to.
With regards to UK postcodes, one postcode serves a street or (in the case of longer roads) part of a street. So I can now key in the customers postcode and their house number and the other fields are automatically populated. This saves the customer having to spell out difficult street names and ensures we get their address right when we send stuff out.
We're a small company and I'm really grateful for all your help!
The postcodes are all roughly the same format. They are in two parts. The 2nd part is always a number and 2 letters. The 1st part always begins with a letter and is between 2-4 characters and denotes the postal area/sorting office. Examples are
BN3 2WW - Brighton area (Where I live)
BN1 3PQ - Brighton area (Where I live)
BN15 8JX - Worthing (near Brighton)
N1 6JD - North London area
SW16 1AP - South West London area
You can pick up a copy of the postcode database from Royal Mail for around 500GBP. We have always had the data here on HD, don't know where we got it from lol!
Whilst I'm posting... I have another query. Being rubbish I don't know how to do this... is there any way in my VBA that I can add in a function that asks for a house number and adds it in.... my script so far goes like this...
Private Sub Post_Code_BeforeUpdate(Cancel As Integer)
Dim Strg As Variant
Dim Strg1 As Variant
Dim Strg2 As Variant
If Not IsNull(Strg) Then
Dim HouseNum As String
HouseNum = InputBox("Please enter the related house number...", "House Number?")
If HouseNum <> "" then Strg = HouseNum & " " & Strg
Me.Address_Line_1 = Strg
I hope I have posted this in the right place, I have a similar problem to brighton.
I am using MS Access 2003, I have created a table named Customers, within it are fields asking for a postcode, streetname, city, town and county ect...
I have also created another table in which I have the following fields, streetname, town, city, county and postcode.
What I would like to do is (When in form view) enter a postcode and for that to automatically fill in the Street, Town, City and County fields for me (Where appropiate). An example would be : say W4 1EY is the postcode I would like the street name field to automatically show the corresponding road name Abinger Road, the town field to show the the postal town Chiswick 9in this case) and the city field to show London, the county field will remain blank as there is no county associated with this address.
I hope I have made myself clear and any help in resolving this matter will be so so much appreciated. BTW I have little or no experience in Dbase design so please be kind when replying or email me.