multi-select listbox question

  • Thread starter Thread starter jb
  • Start date Start date
J

jb

In access 2000, I have a list box which contains items and their prices. I
want to keep a running total of what the person has selected as they are
selecting/unselecting items in the listbox. Can this be done in the onclick
event somehow with a direct reference to the row just selected or
unselected? Or do I have to use the AfterUpdate event and cycle through
each row to see if it is selected and tally my total that way? Thanks a
lot.

John
 
i used the following on a listbox's Click event to cycle through only the
selected items, and add up the prices. one point: the BoundColumn of the
listbox has to be the column that holds the prices - at least i couldn't get
it to do accurate addition on a non-bound column. in the following code, the
name of my listbox is lstItems, and the running total price is displayed in
an unbound textbox called txtTotal.

Private Sub lstItems_Click()

Dim varItm As Variant, ctrl As ListBox

Set ctrl = Me!lstItems

If ctrl.ItemsSelected.Count = 0 Then
Exit Sub
End If

Me!txtTotal = 0
For Each varItm In ctrl.ItemsSelected
Me!txtTotal = Me!txtTotal + ctrl.ItemData(varItm)
Next

End Sub

hth
 
Hi Tina,

Thanks a lot. That worked great. I checked out help and found out how to
do summing on any column. You can use the Column property. I pasted the
help screen below and changed the code. Again, thanks and hope this helps
you out a bit too. Hope you can read it. It pasted a bit strange.

John

--snip--

For Each varItm In ctrl.ItemsSelected
'''''''Me!txtTotal = Me!txtTotal + ctrl.ItemData(varItm)
Me!txtTotal = Me!txtTotal + ctrl.Column(1,varItm)) ' the 1 could
be any column you want to sum
Next


You can use the Column property to refer to a specific column, or column and
row combination, in a multiple-column combo box or list box. Use 0 to refer
to the first column, 1 to refer to the second column, and so on. Use 0 to
refer to the first row, 1 to refer to the second row, and so on. For
example, in a list box containing a column of customer IDs and a column of
customer names, you could refer to the customer name in the second column
and fifth row as:
Forms!Contacts!Customers.Column(1, 4)Setting

control.Column(column, row)

The Column property uses the following settings.

Setting Description
control A Control object that represents the active combo box or list
box control.
column An integer that can range from 0 to the setting of the
ColumnCount property minus one.
row Optional. An integer that can range from 0 to the setting of the
ListCount property minus 1.


This property setting is only available by using a macro or Visual Basic.
This property setting isn't available in Design view and is read-only in
other views.

Remarks

You can use the Column property to assign the contents of a combo box or
list box to another control, such as a text box. For example, to set the
ControlSource property of a text box to the value in the second column of a
list box, you could use the following expression:

=Forms!Customers!CompanyName.Column(1)If the user has made no selection when
you refer to a column in a combo box or list box, the Column property
setting will be Null. You can use the IsNull function to determine if a
selection has been made, as in the following example:

If IsNull(Forms!Customers!Country)
Then MsgBox "No selection."
End IfNote To determine how many columns a combo box or list box has, you
can inspect the ColumnCount property setting.
 
Thanks a lot. That worked great.

you're welcome. :)
that's only the second time i've ever used the ItemsSelected and ItemData
properties of a listbox in VBA (and the second time within the past couple
weeks), so i'm happy to have a chance to practice!
Me!txtTotal = Me!txtTotal + ctrl.Column(1,varItm))
Click to expand...

i was already familiar with the Column property of listboxes (and combo
boxes), but i couldn't figure out how to get it to work within the context
of ctrl.ItemsSelected. thanks for sharing the correct syntax with me - now
i've learned a few new things out of this thread! :)
 
Back
Top