Adding values from a List Box

[Guest]

Can I add numbers listed in a List Box and put the sum in a text box?

 

[Douglas J. Steele]

Untested:

Function AddNumbersInListbox(ListboxControl As Listbox) As Long

Dim lngLoop As Long
Dim lngSum As Long

lngSum = 0

For lngLoop = 0 To (ListboxControl.ListCount - 1)
lngSum = lngSum + ListboxControl.ItemData(lngLoop)
Next lngLoop

AddNumbersInListbox = lngSum

End Function

Set the control source of the text box to:

=AddNumbersInListbox(Forms!NameOfForm!NameOfListbox)

(replace "NameOfForm" and "NameOfListbox" to the appropriate names)

 

[Guest]

is there not a sql that can be used in the control source?

 

[Douglas J. Steele]

Depends on the RowSource for the list box.

If, for example, the list box is a simple query of a table (with or without
a Where clause), you could use the DSum function in the control source of
the text box.

 

[Guest]

I used the code and it works with a couple of exceptions. First, how do I
format the sum to a number with decimals...whaqtever i try to do, the sum is
only shown as a whole number and it rounds.? Second, I want to be able to
click on List1 which queries List2 and have the sum from List2 to appear.
How will the code change given this scenario.

 

[Douglas J. Steele]

Sorry, I should have been more explicit:

Function AddNumbersInListbox(ListboxControl As Listbox) As xxxx

Dim lngLoop As Long
Dim lngSum As xxxx

lngSum = 0

For lngLoop = 0 To (ListboxControl.ListCount - 1)
lngSum = lngSum + ListboxControl.ItemData(lngLoop)
Next lngLoop

AddNumbersInListbox = lngSum

End Function

Change xxxx to the appropriate data type (Single, Double. Decimal, Currency,
whatever's appropriate for the data)

 

[Guest]

dude...that's awesome...One more thing though....
How can I get the sum to automatically recalculate based on List2 results
which is based on List1 selections. In other words, I click on List1 and a
sum appears based on List2...then I click another selection on List1 and the
sum automatically updates based on the List2 results. Thanks much

 

[Douglas J. Steele]

Private Sub List1_AfterUpdate()

Me.List2.Requery

End Sub

(or it might be Refresh you need, not Requery)

 

[Guest]

it was requery...and the only modification I had to make was to replace ".",
after Me, with a "!". You have given me reason to sleep good tonight.
Thanks.