Results 1 to 13 of 13
  1. #1
    Join Date
    Dec 2011
    Posts
    6

    Unanswered: Lookup & Autofill Town & County

    Hi,

    Can anyone help me at all, I have an access database with a Table that contains The first 4 digits of a UK postcode, Town & County it relates to.

    I would like to be able to type a postcode in a form and have the Town and County auto fill with the info from the table.

    Does anyone know how I do this, simple terms please as I am a newbie at this.

    Shazz
    xx

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    there's several threads started by jackjsmith88 who has a similar problem. although it may be that his design isn't right for you

    what you need is a postcode database
    if you want the town and country (although county is apparently no longer needed according to the post office) then you need all of the incoming code and the first digit of the outgoing code. FWIW UK postcodes are arranged in two blocks the first block comprises the major sorting office and delivery office, the next block identified by a leading space is 3 characters the first is a number which identifies the town. the final two characters identify a specific delivery location. however you do need the extra two digits if you want the fine location details

    there's some 28 million odd postcodes in use so building up your table reliably may take considerable time. Ideally you should subscribe to the PAF (Postcode Address File)
    you can get downloads of the incoming code identfying the major sorting office for free, but getting reliable data for the first element of the outbound code is tricky.

    back to you app
    depends what you want to do , how you want to access the data
    you could build a list or combo box dynamically
    ie when you add a record, populate the list box with the 124 major sorting offices AB..ZE
    once you encounter the first numeric value, then requery the list box with the delivery office part eg BT01...BTxx
    then flip a switch when you spot a space, then requery when the first non numeric value is entered to retrieve the post town & borough. that woudl inolve placing code behind the controls on key events, querying the db and building the control as expected. unless your network is good I wouldn't suggest going down that route

    the other is to examine the postcode AFTER its been entered and taking appropriate action. that would mean putting some code behind the controls on lost focus, on keypress events and so on.

    bear in mind a postcode is anywhere between 6 and 8 digits
    the outgoing code always take the format of xyy where x is a digit 0..9 & yy is a alphabetic (AA..ZZ)

    so you could search to see if the full post code already exists
    or search to see if the postcode exists for the length of the entered postcode - 1 digit.
    or search to see if the postcode exists for the length of the entered postcode - 2 digits.

    that would mean querying the postcode table to see if the postcode already exists.

    in reality you would be better off using a prexisting solution which is readily available from lots of vendors, but not (AFAIK) for free
    .
    asking your users to type this sort of info in is going to be a pain, especially if your app switches between different windows, asks questions and so on.
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Dec 2011
    Posts
    6

    Lookup & Autofill Town & County

    All I want is to type in a postcode and the first 4 digits only will determin from a table with data in what the Town & County is and then auto fill the Town & County fields in the form.

    I have a table with info as below.

    Postcode Town County
    DA2 Dartford Kent

    So if i where to type DA2 1PY in the Postcode field on my Form, in the Town field Dartford would auotfill and in County Kent would auto fill. I need both Town and County.

    Shazz

  4. #4
    Join Date
    Dec 2011
    Posts
    6
    Sorry I forgot to mention I already have many records already completed so i need to add this step to the databse without corrupting the current data.

    Shazz

  5. #5
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Going to assume a few things here... You'll need to adjust these to the actual names you're using to make it work.

    Form
    1 textbox, called 'inputBox'
    2 labels, called 'outputTown', 'outputCounty'

    Table
    Called: 'postCodeTbl'
    3 fields, called 'PostCode', 'Town', 'County'

    Now, on the textbox (inputBox), you want to go to its properties, then, under the events tab, change the 'On change' event to '[Event Procedure]', and then click the '...' button next to it. This should open up the VBA editor, and you should see something like:

    Code:
    Option Compare Database
    
    Private Sub inputBox_Change()
    
    End Sub
    You're going to have to edit this to look like the following:

    Code:
    Option Compare Database
    
    Private Sub inputBox_Change()
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [Town], [County] " & _
             "FROM postCodeTbl " & _
             "WHERE [PostCode] = '" & Me.inputBox.Text & "'"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If Not ((rs.BOF) And (rs.EOF)) Then
        Me.outputTown.Caption = rs![Town]
        Me.outputCounty.Caption = rs![County]
    End If
    
    rs.Close
    Set rs = Nothing
    
    End Sub
    Then, whenever you write a recognised first section of a UK postcode, the two labels should populate with the correct information.


    This code is written as is, and hasn't been tested, but shouldn't give you any issues.

    Let me know how it goes.
    Looking for the perfect beer...

  6. #6
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Just noticed you've posted a little more detail...

    If the user is going to enter their entire postcode, not just the first four characters, then you'll need to adjust my code a little.

    Replace the line:

    Code:
    "WHERE [PostCode] = '" & Me.inputBox.Text & "'"
    with:

    Code:
    "WHERE [PostCode] LIKE '" & Me.inputBox.Text & "*'"
    Looking for the perfect beer...

  7. #7
    Join Date
    Dec 2011
    Posts
    6
    By saying Inputbox do you mean my current field that says Postcode.

    i am really sorry I am completely new at this and probably seem a bit thick, I need things explained in simple terms.

    I dont suppose you would know of a sample database anywhere I can look at do you?

    Shazz

  8. #8
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    By inputBox I mean the control on your form where the end user is going to type the postcode into.

    I can knock up a quick database to show you a couple of examples if you like..?
    Looking for the perfect beer...

  9. #9
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Having knocked up a demo, I made a couple of slight adjustments to the code, to make it work a little nicer...

    Code:
    Option Compare Database
    
    Private Sub inputBox_Change()
    
    Dim strSQL As String
    Dim rs As DAO.Recordset
    
    strSQL = "SELECT [Town], [County] " & _
             "FROM postCodeTbl " & _
             "WHERE [PostCode] LIKE '" & Me.inputBox.Text & "*'"
    
    Set rs = CurrentDb.OpenRecordset(strSQL)
    
    If (Not ((rs.BOF) And (rs.EOF))) And _
        (Len(Me.inputBox.Text & vbNullString) >= 3) Then
        Me.outputTown.Caption = rs![Town]
        Me.outputCounty.Caption = rs![County]
    Else
        Me.outputTown.Caption = " "
        Me.outputCounty.Caption = " "
    End If
    
    rs.Close
    Set rs = Nothing
    
    End Sub

    To demonstrate this a little better, I have attached a quick demo database I knocked up.

    Pushing ALT+F11 when viewing the form will open up the VBA editor, and allow you to see the code behind this operation.
    Attached Files Attached Files
    Looking for the perfect beer...

  10. #10
    Join Date
    Dec 2011
    Posts
    6
    Thanks very much I'll have a look
    Shazz
    Last edited by Shazz; 12-05-11 at 10:47. Reason: Posted wrong

  11. #11
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    Wow, watching like a hawk.

    Attached now.
    Looking for the perfect beer...

  12. #12
    Join Date
    Dec 2011
    Posts
    6
    There is one problem, When I type a full post code say DA2 7PW nothing comes up, it only works when I only put DA2 in, I need to be able to put the full post code in and still have the Town & County autofill.

    Can this be done.

    Shazz

  13. #13
    Join Date
    Jun 2011
    Location
    Inside your mind
    Posts
    297
    It does do that.

    If you look at the table, I just used 3 postcodes, as it's a demonstration.

    If you type in any of those three postcodes (CT2 8EJ, DA2 1PY or EN10 7BD), then you can type in the entire postcode, part of it, or just the first three characters to get the desired results up.

    If you add that postcode to the table, giving it a County and a Town, then it will work as expected.

    The reason you think that it isn't working is because the table contains the postcode; 'DA2 1PY', so when you are typing 'DA2 7PW', it can match up the 'DA2 ', but then using the 7PW invalidates it.

    You'll have to use that code, but with YOUR data. Not the three postcodes I used in my demo.
    Looking for the perfect beer...

Posting Permissions

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