Multi Select list box and the bound column....

[JP]

Multi Select list box and the bound column....

Hello all,
Req any help with the following:

I have a multi select list box that contains data from a
table. The table has two columns. Column 1 is a numerical
value, column two is text.

When a user makes selections from the text (column 1)
displayed in the listbox, the numerical tally (column 2)
of those choices is displayed in a text box. The code for
which is as follows:

Private Sub listBox_AfterUpdate()
Dim ctlList As Control, varItem As Variant
Dim a As Long
' Return Control object variable pointing to list box.
Set ctlList = Me.listBox
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Debug.Print ctlList.ItemData(varItem)
a = a + ctlList.ItemData(varItem)
Next varItem
'Debug.Print a
TextBox.Value = a
End Sub

My question is this: This appears to be the result of
column 1 (numerical values) being the bound column. Is
there anyway to make this happen with column 2 as the
bound column? The reason I ask, is because I need to keep
the tally feature, but also need to display the selected
text items in a combo box.

JP

 

[Douglas J. Steele]

ctlList.ItemData(varItem) should always refer to the bound column. If you
change the bound column, whatever's bound should get picked up.

A couple of comments, though. Presumably you want to be able to read what
you're picking up, so I'd suggest

Private Sub listBox_AfterUpdate()
Dim ctlList As Control, varItem As Variant
Dim strOutput As String
' Return Control object variable pointing to list box.
Set ctlList = Me.listBox
' Enumerate through selected items.
For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Debug.Print ctlList.ItemData(varItem)
strOutput = strOutput & ctlList.ItemData(varItem) & "; "
Next varItem
' Remove the last "; " from the string
If Len(strOutput) > 0 Then
strOutput = Left$(strOutput, Len(strOutput) - 2)
End If
'Debug.Print strOutput
TextBox.Value = strOutput
End Sub

(Note the use of & for concatenation, rather than +. + will work, but can
have unexpected effects when working with Null values)

If you don't want to change which column is bound, try

For Each varItem In ctlList.ItemsSelected
' Print value of bound column.
'Debug.Print ctlList.ItemData(varItem)
strOutput = strOutput & ctlList.Column(1, varItem) & "; "
Next varItem

(the column count starts at 0, so 1 means you'll pick up the 2nd column)

I don't understand how you're going to display the values in a combo box,
though.

 

[JP]

Outstanding - your code provided the answer I needed, and
now everything on the form is working as we need it to.
Thanks. As far as sending the selected text items to a
combo box, the following code for a button does that:

Private Sub cmdCopyItem_Click()
Dim var As Variant
Dim ctl As ListBox
Dim str As String
Set ctl = Me!listbox
For Each var In ctl.ItemsSelected
str = str & "; " & ctl.ItemData(var)
Next var
str = Mid(str, 2)
Me!combobox = str
Set ctl = Nothing
End Sub

I don't know how it all works, I just know that it does.

Thanks again.