Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Unanswered: Copying items from one list box to another

    Hi,
    Issue:
    I have two list boxes on a form.
    I want to copy items that are selected from the first one (MListSource) to the second one (MListFinal).


    Problem
    I am able to only copy the value from the "Bound" clomun of my first list box, but my list boxes have two columns "ID" and "Name" and I need to copy an array to the other list box.

    Do you know how?



    Here is the code to copy the bound column:
    ----------------------------------------------------
    Dim MListSource As ListBox, MListFinal As ListBox
    Dim itm As Variant

    Set MListSource = Me!MListSource
    Set MListFinal = Me!MListFinal
    ' Check selected items.
    For Each itm In MListSource.ItemsSelected
    ' Set RowSource property for first selected item.
    If MListFinal.RowSource = "" Then
    MListFinal.RowSource = MListSource.ItemData(itm)
    Else
    ' Check whether item has already been copied.
    If Not InStr(MListFinal.RowSource, MListSource.ItemData(itm)) > 0 Then
    MListFinal.RowSource = MListFinal.RowSource & ";" & MListSource.ItemData(itm)
    End If
    End If
    Next itm
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

  2. #2
    Join Date
    Apr 2003
    Posts
    42

    There may be an easier way but...

    Can you include code to query the DB to get the related field you wish to display in the second column?

  3. #3
    Join Date
    Apr 2003
    Posts
    42

    Extract it from the string...

    Or extract it from the code:

    MListSource.RowSource

  4. #4
    Join Date
    Apr 2003
    Posts
    42

    Thumbs up Voila

    Code:
    Public Function getColTwo()
    
        Dim intStrLen As Integer
        Dim MyPos As Variant
        Dim strPosStart As Variant
        Dim strPosEnd As Variant
        Dim strName As String
        Dim strId As String
        
        
        'used to test code
        strId = "4"
    
    
    
        '                               This is the char positions in the string
        '                               ----------------------------------------
        '                               123456789012345678901234567890123456
        ' this is the row source:       1;"cat";2;"dog";3;"mouse";4;"ant"
        
        strRowSource = "1;""cat"";2;""dog"";3;""mouse"";4;""ant"""  'lstBox.RowSource
        
        ' A comparison starting at position 1, looking for the string strId (your BoundColumn value)
        MyPos = InStr(1, strRowSource, strId, 1)
        'MsgBox ("Position in string : " & MyPos)
        
        'find the length of the string in the second column
        strPosStart = MyPos + 3
        strPosEnd = InStr(strPosStart, strRowSource, """") - 1
        
        'MsgBox ("Position that text ends at : " & strPosEnd)
        
        'needed string starts at strPosStart and ends at strPosEnd
        'use these to extract the string
        strName = Left(strRowSource, strPosEnd)
        strName = Right(strName, ((strPosEnd - strPosStart) + 1))
        
        
        'voila
        MsgBox ("The name of the string in column 2 is: " & strName)
        
    
    End Function

    This isn't exactly what you want but shows how you can extract the Second Column string, by using the string of the Bound column.

    I haven't tested it, but i think it's pretty sound.

  5. #5
    Join Date
    Dec 2002
    Location
    Washington D.C.
    Posts
    164

    Re: Voila

    Thanks for your reply,
    My main problem is ussing the additem property of the list box,
    the below code that I wrote works, but it has one problem:
    If one of the strings has a "," character it will mess up the columns and add the characters after the "," in the next column. I'm using ";" to seperate columns, here is the code:

    Dim VarItem As Variant
    Dim ID As String
    Dim Name As String

    For Each VarItem In Me!MListSource.ItemsSelected

    ID = Me!MListSource.ItemData(VarItem)
    Name = Me!MListSource.Column(1, VarItem)

    Me!MListFinal.AddItem ID & ";" & Name

    Next VarItem




















    Originally posted by ventelation
    Code:
    Public Function getColTwo()
    
        Dim intStrLen As Integer
        Dim MyPos As Variant
        Dim strPosStart As Variant
        Dim strPosEnd As Variant
        Dim strName As String
        Dim strId As String
        
        
        'used to test code
        strId = "4"
    
    
    
        '                               This is the char positions in the string
        '                               ----------------------------------------
        '                               123456789012345678901234567890123456
        ' this is the row source:       1;"cat";2;"dog";3;"mouse";4;"ant"
        
        strRowSource = "1;""cat"";2;""dog"";3;""mouse"";4;""ant"""  'lstBox.RowSource
        
        ' A comparison starting at position 1, looking for the string strId (your BoundColumn value)
        MyPos = InStr(1, strRowSource, strId, 1)
        'MsgBox ("Position in string : " & MyPos)
        
        'find the length of the string in the second column
        strPosStart = MyPos + 3
        strPosEnd = InStr(strPosStart, strRowSource, """") - 1
        
        'MsgBox ("Position that text ends at : " & strPosEnd)
        
        'needed string starts at strPosStart and ends at strPosEnd
        'use these to extract the string
        strName = Left(strRowSource, strPosEnd)
        strName = Right(strName, ((strPosEnd - strPosStart) + 1))
        
        
        'voila
        MsgBox ("The name of the string in column 2 is: " & strName)
        
    
    End Function

    This isn't exactly what you want but shows how you can extract the Second Column string, by using the string of the Bound column.

    I haven't tested it, but i think it's pretty sound.
    Last edited by Sia; 07-03-03 at 17:23.
    The intellect is better than desire, for the intellect makes you a king over your destiny, and desire makes you a slave of your destiny.

Posting Permissions

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