Move selected items from one listbox to another

Question

I have two listboxes, named listbox1 and listbox2.

Listbox1 is populated using a SQL query, and contains two columns. Its first column contains values that have commas.

Listbox2 is set as a value list in the "row source type" attribute of the Access property sheet.

My goal is to copy selected items from listbox1 to listbox2 using a control button.

I also need to be aware that listbox1 records contain commas, which act as delimiters during copying. That particular issue has been resolved, though.

I have created two modules to accomplish the copying of selected records from one listbox to another:

Public Sub CopySelected(ByRef frm As Form)

    Dim ctlSource As Control
    Dim ctlDest As Control
    Dim strItems As String
    Dim intCurrentRow As Integer

    Set ctlSource = Me!listbox1
    Set ctlDest = Me!listbox2

    For intCurrentRow = 0 To ctlSource.ListCount - 1
        If ctlSource.Selected(intCurrentRow) = True Then
            'must insert double quote around single quote to escape commas
            strItems = strItems & "'" & ctlSource.Column(0, intCurrentRow) & "'" & ";" 


            Me!listbox2.AddItem (strItems)

        End If
    Next intCurrentRow
End Sub

And

Private Sub cmdAddSelected_Click()

    CopySelected Me

End Sub

I do have the multi-select option on listbox1 set to "extended".

The current problem is that when I click my control button, only the first selection from listbox1 is copied over- with the caveat that it is copied multiple times (copied the same number as those selected records).

Clearly, there is a problem with my For-loop.

R is my main language, and I am only just learning VBA.

AcsErno AcsErno · Accepted Answer · 2018-06-20T08:11:35

In your program you are concatenating all selected values with separator and adding the longer and longer string to Listbox2. The ; acts as a column separator for multicolumn listboxes, i.e. you are kind of "transposing" the selected values from Listbox1 to Listbox2 while the excess items (above the number of columns of Listbox2) are simply ignored. If you want to copy individual values into the single column Listbox2, do this:

For intCurrentRow = 0 To ctlSource.ListCount - 1
    If ctlSource.Selected(intCurrentRow) Then
        strItems = "'" & ctlSource.Column(0, intCurrentRow) & "'" 
        Me!listbox2.AddItem (strItems)
    End If
Next intCurrentRow

Move selected items from one listbox to another

1 Answers