Adding values in postcode lookup form to another form

[robertmarkdudley95]

Hi, I found this code on another forum for a postcode lookup form:

Private Sub Text0_AfterUpdate()
    Me.List2.RowSource = ""
    Dim Pcode, sStr As String
    'First up, Format the postcode
    Pcode = UCase(Replace(Text0, " ", ""))
    Select Case Len(Pcode)
        Case 5
            Pcode = Mid(Pcode, 1, 2) & " " & Mid(Pcode, 3, 3)
        Case 6
            Pcode = Mid(Pcode, 1, 3) & " " & Mid(Pcode, 4, 3)
        Case 7
            Pcode = Mid(Pcode, 1, 4) & " " & Mid(Pcode, 5, 3)
    End Select
    
    'Now create the search string
    'http://www.192.com/places/ab/ab10-1/ab10-1an/
    sStr = "http://www.192.com/places/"
    For a = 1 To Len(Pcode)
        If IsNumeric(Mid(Pcode, a, 1)) Then
            sStr = sStr & Mid(Pcode, 1, a - 1) & "/"
            a = Len(Pcode)
        End If
    Next a
    sStr = sStr & Mid(Replace(Pcode, " ", "-"), 1, InStr(Pcode, " ") + 1) & "/"
    sStr = sStr & Replace(Pcode, " ", "-") & "/"
    
    'Now I create a WinHTTP request to get the information from the server
    
    Dim winReq As WinHttpRequest
    Dim HTM, Address As Variant
    Dim Add1, Add2, Add3, Add4 As String
    Dim sCount As Integer
    
    Set winReq = New WinHttpRequest
    With winReq
        .Open "GET", sStr, False
        .Send
        HTM = Split(Replace(.ResponseText, """", "'"), "<")
        If .Status <> 200 Then
            MsgBox ("Address not found")
            Exit Sub
        End If
    End With
    
    'Now I have the entire web page including tags just without the '<' at the beginning of each line
    'Split this down to find the address lines
    For Each i In HTM
        If InStr(i, "td class='address'>") > 0 Then
            'You can the assign the address to a listbox as below
            Me.List2.AddItem (Replace(i, "td class='address'>", ""))
            
            'Or you can split the address in to variables
            Address = Split((Replace(i, "td class='address'>", "")), ",")
            sCount = 0
            For Each j In Address
                sCount = sCount + 1
            Next
            Select Case sCount
                Case 3
                    Add1 = Address(0)
                    Add4 = Address(1)
                Case 4
                    Add1 = Address(0)
                    Add3 = Address(1)
                    Add4 = Address(2)
                Case 5
                    Add1 = Address(0)
                    Add2 = Address(1)
                    Add3 = Address(2)
                    Add4 = Address(3)
                Case 6
                    Add1 = Address(0) & " " & Address(1)
                    Add2 = Address(2)
                    Add3 = Address(3)
                    Add4 = Address(4)
            End Select
            'Put code here to assign these variables to anything you like
            
        End If
    Next
End Sub

The result appears as follows:



What I'm wanting to do is this:

• The house number and the street name need to go in a field called Street on the form frmCustomerDetails
• The town/city needs to go in a field called Town/city on the same form
• The region needs to go in a field called Region on the same form
• The postcode needs to go in a field called Postcode on the same form.

However, the Postcode field has an input mask L?09 0LL, and so I am questioning whether or not this will work efficiently with all postcodes unless I remove the input mask?

Thanks in advance for any help

[June7]

What you should probably do is just save the AddressID for the selected postcode record into the CustomerDetails table. Then two ways to display the related address info.

1. In query join the postcode table to the customer details table on the AddressID fields. Jointype 'show all records from CustomerDetails and only those from Postcode that are equal'. This query can be the RecordSource for frmCustomerDetails. All info from the related records will be available. Don't allow edit of the postcode info. Set textboxes as Locked Yes and TabStop No.

2. Multi-column combobox has all the fields from PostCode table. Textboxes on form have ControlSource that references the columns of combobox.

Otherwise, you will duplicate data and a basic principle of relational database is to not duplicate data.

[robertmarkdudley95]

Sorry it's taken so long to reply. I've been away.

Um... how would I do either of these?

[June7]

What do you not understand about No. 1?

For number 2, textbox ControlSource refers to columns of combobox: =comboboxname.Column(1)

Column indexing starts with 0 so the first column is index 0, second column is index 1.