copying a value from list box with a record source to one without

[Prysson]

I have a form with 2 list boxes

The first list box has a query as its record source and so displays the values retrieved from that query.

There is a command button whose purpose it is to take a selected record from that first list box and write that value into the second list box as a record source. There is another button should allow you to copy every value in the first list box into seperate entries in the second list box. Technically you should be able to select one or two or even all of those values into the second list box.


This information is temporary. At the close of the form the record source for the second list box is reset to nothing.

How would you do that in code

 

[dgm]

Private Sub cmdCopySelected_Click()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant, intI As Integer

    Set frm = Forms!frmMyForm
    Set ctl = frm!lstMyList
    Set ctl2 = frm!lstMyList2
    
    For Each varItm In ctl.ItemsSelected
        For intI = 0 To ctl.ColumnCount - 1
            If ctl2.RowSource = "" Then
                ctl2.RowSource = ctl.Column(intI, varItm)
            Else
                ctl2.RowSource = ctl2.RowSource & ";" & ctl.Column(intI, varItm)
            End If
        Next intI
    Next varItm
End Sub

Private Sub cmdCopyAll_Click()
    Dim frm As Form, ctl As Control
    Dim intRow As Integer, intI As Integer

    Set frm = Forms!frmMyForm
    Set ctl = frm!lstMyList
    Set ctl2 = frm!lstMyList2

    For intRow = 0 To ctl.ListCount - 1
        For intI = 0 To ctl.ColumnCount - 1
            If ctl2.RowSource = "" Then
                ctl2.RowSource = ctl.Column(intI, intRow)
            Else
                ctl2.RowSource = ctl2.RowSource & ";" & ctl.Column(intI, intRow)
            End If
        Next intI
    Next intRow
End Sub

Just change these values to your own:

Set frm = Forms!frmMyForm 'Name of your form
Set ctl = frm!lstMyList 'Name of your first Listbox (Copy from)
Set ctl2 = frm!lstMyList2 'Name of your second listbox (Copy to)

Also, make sure your second listbox has the same number of columns as your first listbox, and the second listbox's Row Source Type property should be set to Value List

And you can add:
lstMyList2.rowsource = ""
to the open or close event of your form to clear the listbox for next time.

Dave

 

[Prysson]

Hey thanks...That worked well.