Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2013

    Unanswered: Vba Code to Change Bound Column

    Good Morning

    I am having a problem with the VBA code to chage the bound Column in a unbound Value List.

    I have used a piece of coding which looks up address from which it all working great. Here's my issue.

    With the onclick command I want to be able to copy the results row into an open form instead of the user copying and pasteing the results of the postcode lookup.

    List2 is the unbound Value box. It has 7 Columns and is currently bound to Column 1.

    What it have found is if I run - docmd****ncommand accmdcopy - it copys the text from only the bound column. I cannot find any other way of copying the text. If I change the bound column it copys that text

    So what I thought I would do is run copy (to copy the first column) then paste into the active other active form, then change the bound column the run copy and so on for the rest of the columns. I am using

    me.List2.BoundColumn = 2

    But it does not change the bound column in the object properties.

    Any help would be much appriciated

    Thank you

  2. #2
    Join Date
    Jun 2005
    Richmond, Virginia USA
    Provided Answers: 19
    To be honest, I'm totally confused by your explanation of what you're trying to do here, but you can access the different Columns of a Listbox (or Combobox) and assign the values to Textboxes with code like this

    Me.txtField1 = Me.YourListBox.Column(0)
    Me.txtField2 = Me.YourListBox.Column(1)
    Me.txtField3= Me.YourListBox.Column(2)

    Notice that the Column Index is Zero-based, i.e the first Column is 0, the second Column is 1, etc..

    Linq ;0)>
    Hope this helps!

    The problem with making anything that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Feb 2013

    Sorry will try again to explain

    Thank you for the reply

    Sorry; So tired probably best not to be trying to work on the database but I have now been set a dead line for a presentation. Please find enclosed the codeing that I am running below which I found online.

    ************************************************** *******
    The following code is a postcode lookup which brings the data into a unbound Text box called "List2". The data within the Text box is split into 7 columns and the bound column is column 1.

    When I click on the first column it highlights the row. I need the row information to populate another active form to complete an address feild. If I then run docmd****ncommand accmdcopy this only copys the text in the bound column 1. But if I change the bound column manually to 2 it will then copy the data in that column.

    What I thought was if I ran accmdcopy to copy column 1 then paste it into the form. Then change the bound coulmn to 2 then copy the text and paste into the form and so on for the other bound columns

    Hopefully this makes more sense

    If this does not make sense I persume I can upload the database for people to look at

    ************************************************** *******

    'Requires reference to WinHTTP.dll This can be found in 'C:\Windows\System32\'

    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
    sStr = ""
    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
    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
    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
    End If

    End Sub

Posting Permissions

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