Copy from one List Box to another, only a portion is copied 1

[MrsMope]

Good Morning,
I am using Access '03 and SQL 2000 to create a program to calculate incentive pay for plant employees. Plant supervisors need to be able to group employees, so I have two list boxes one that displays a list of all employees in the following format: LASTNAME, FIRSTNAME, I have the following code to copy the selected values to another listbox:

Public Sub CopySelected(ByRef frm As Form)
Dim ctrlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer



Set ctrlSource = [Form_List Box Example]!List1
Set ctlDest = [Form_List Box Example]!List2

For intCurrentRow = 0 To ctrlSource.ListCount - 1
    If ctrlSource.Selected(intCurrentRow) Then
        strItems = strItems & ctrlSource.Column(1, _
            intCurrentRow) & ";"
    End If
Next intCurrentRow
'Reset destination controls' RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems


Set ctrlSource = Nothing
Set ctlDest = Nothing

End Sub

When the items are copied, only the first name appears in the destination list box, why?

 

[OnTheFly]

Try changing to

For intCurrentRow = 0 To ctrlSource.ListCount - 1
If ctrlSource.Selected(intCurrentRow) Then
strItems = strItems & ctrlSource.Column(0, _
intCurrentRow) & ";"
strItems=strItems & ctrlSource.Column(1,intCurrentRow) & ";"
End If
Next intCurrentRow

You were only pulling only the second column (column 1). This code will pull both columns.

Hope this helps.

OnTheFly

 

[MrsMope]

Alright, now I only get the last name...
There are two columns behind my list box, one is EmployID and the other is EmployName, the name column has entries in the following format

 LASTNAME , FIRSTNAME

With the code you suggested, only the LASTNAME is appearing in the second list box. The code I had previously only displayed the first name, when I look at strItems in the immediate window, I see the following:

?stritems
BAUER,BLAKE                             ;BAUER,LEONARD                           ;

 

[OnTheFly]

Not sure where all the trailing spaces are coming from but I am sorry I misunderstood your list box set up to start. With commas being part of the string value you will need to add single quotes when creating the string.

Add this variable
Dim strQuote as String

Set it equal to this at the begining
strQuote="'"

Use this line to create the string.
strItems = strItems & strQuote & ctrlSource.Column(1,intCurrentRow) & strQuote & ";"


Hope this helps.

OnTheFly

 

[MrsMope]

Worked like a charm, thanks so much

 

[OnTheFly]

I forgot one thing. Since the items in the list box are names you are going to want to change the strQuote variable to

strQuote=""""

This will use the double quote. Otherwise if you have a name like O'Donnell in the data you will have problems. Sorry I forgot about that.

Hope this helps.

OnTheFly