List Box Output (again)

[JP]

I have an Access form, and needed help with a list box
that calculated a value assigned to each selection. "Roxie
Aho" was kind enough to provide the following:

(quote)

"The List Box control has a MultiSelect property under the
Other tab on Properties. Help has a good explanation of
how to use it. Select Simple or Extended. My example
works with both.

In the example I added a text box named ListAdd to the
form. The name of the list box is ListBox because that's
what I was able to grab quickly. It just displays name
and a primary key from a table. In the ListBox After
Update event I added the following code:
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
ListAdd.Value = a
End Sub

The user can hold down the Control Key while clicking to
select the items he or she wants. At the same time the
Text Box ListAdd shows the sum of the primary key values
as the user selects.

The Text Box control has many properties you can set in
code for display purposes.

The form and example I created has no real purpose. It is
only to show you some capabilities. The code also comes
from Visual Basic Help: Item Data Property and example."

(end quote)

Well, it worked like a charm, despite the fact that I have
no idea what all that means. In the table this list box
refers to, there are two columns. The first contains
numbers which are totalled in a text box as Roxie Aho
described. The second column contains text items, and now
I need to also list these items, as selected, in a second
text box. So, if I select 3 items from the list of 50,
this second text box will display those 3 items.

I've been through the help guide and examples, but just
cannot figure these things out. Any help?

Again, many thanks!

JP

 

[Roxie Aho]

JP,
I don't like this solution because one part of it is
automatic (calculating a value) and the other requires a
command button (getting the second list). If anything, it
should be the other way around but my path didn't result
in that.

I have renamed the List Box from the first example
from "ListBox" to "lstSource." So in my After Update sub
I have changed any "ListBox" entries to "lstSource"
including the name of the sub.

1. For this example, make sure the MultiSelect property of
lstSource is set to Extended.

2. Add a second List Box named "lstDestination." Set its
RowSourceType property to Value List on the Data tab of
Properties.

3. Add a command button named "cmdCopyItem"

4. Add the following code to the form:

Sub cmdCopyItem_Click()
CopySelected Me
End Sub

Function CopySelected(frm As Form) As Integer
Dim ctlSource As Control
Dim ctlDest As Control
Dim strItems As String
Dim intCurrentRow As Integer
Set ctlSource = frm!lstSource
Set ctlDest = frm!lstDestination
For intCurrentRow = 0 To ctlSource.ListCount - 1
If ctlSource.Selected(intCurrentRow) Then
strItems = strItems & ctlSource.Column(1,
intCurrentRow) & ";"
End If
Next intCurrentRow
' Reset destination control's RowSource property.
ctlDest.RowSource = ""
ctlDest.RowSource = strItems
End Function

These changes should give you an approximation of what you
want. A list of items selected and a calculated field.

This example is from Visual Basic Help - Selected Property
and Selected Property example

Roxie Aho
(e-mail address removed)

 

[JP]

Roxie Aho:

Well sure enough, that did the trick, and I think I'm all
done with this project. Many thanks for all your help.
Agreed with your:

I don't like this solution because one part of it is
automatic (calculating a value) and the other requires a
command button (getting the second list). If anything,
it should be the other way around...

I see your point, and perhaps in the near future I'll
endeavor to change that around.

Thanks again for all your help!

JP