Results 1 to 9 of 9
  1. #1
    Join Date
    Jun 2004
    Posts
    5

    Question 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!

    Thanks for your time!

  2. #2
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369
    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.

    D.

  3. #3
    Join Date
    Nov 2003
    Posts
    1,487
    In the BeforeUpdate event of your Forms' PostCode entry box:

    Code:
    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:


    Code:
    Me.myFormsStreetComboBoxName.RowSource = _
    "SELECT PostCodeTable.PostCodeTablePostCodeFieldName _
    FROM PostCodeTable WHERE ((PostCodeTable!PostCodeTablePostCodeFieldName = _
    Forms!myFormName!myFormsPostCodeTextName));"
    '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.

    Either or......


    Last edited by CyberLynx; 06-05-04 at 23:57.

  4. #4
    Join Date
    Feb 2004
    Location
    Wales
    Posts
    343
    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

    regards

    Gareth

  5. #5
    Join Date
    Jun 2004
    Posts
    5

    Thank You

    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!

    Many many thanks

  6. #6
    Join Date
    Nov 2003
    Location
    Europe
    Posts
    369

    Question

    Hm. Interesting, out of curiosity:

    - how long/what does the "new" (since when was this code system implemented?) post codes look like? I guess it is the same amount of digits for the whole of - eh - UK or just England?

    Is it very costly to get hold of the whole post code database with the street names?

    D.

  7. #7
    Join Date
    Jun 2004
    Posts
    5

    Reply and some MORE HELP NEEDED!!!! :)

    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

    Strg = DLookup("[PCS3!Street]", "PCS3", _
    "[PCS3!PostCode] = '" & Me.Post_Code & "'")

    If Not IsNull(Strg) Then Me.Address_Line_1 = Strg

    Strg1 = DLookup("[PCS3!Town]", "PCS3", _
    "[PCS3!PostCode] = '" & Me.Post_Code & "'")

    If Not IsNull(Strg1) Then Me.Address_Line_2 = Strg1

    Strg2 = DLookup("[PCS3!County]", "PCS3", _
    "[PCS3!PostCode] = '" & Me.Post_Code & "'")

    If Not IsNull(Strg2) Then Me.Address_Line_3 = Strg2

    End Sub


    So basically, when i type the postcode in, I want a box to come up to ask for a house number and then whatever is typed in is added at the start of the addressline1 field.

    Thanks in advance!

  8. #8
    Join Date
    Nov 2003
    Posts
    1,487
    Try this:

    Code:
     
    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
    End IF
    




  9. #9
    Join Date
    Apr 2010
    Location
    London
    Posts
    3
    Hello
    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.
    Thanks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •