ItemsSelected Property of ListBox control 1

[beckyh]

Does anyone know anything about this property of a list box. I want to be able to calculate a total, based on multiple selections of a list box. BUt I am not sure how to pass the collection of numbers.

 

[RickSpr]

What you do with the collection is you iterate through it with a For Each loop, using each number as the row index parameter to the ItemData or Column property. So for instance, if you want to add up the 2nd columns of the selected items, you use code like this:

Total = 0
For Each itm In ListBox1.ItemsSelected
    Total = Total + ListBox1.Column(1, itm)
Next itm
[code]

If the value you want to total is in the bound column, you can use the ItemData property instead: ListBox1.ItemData(itm).

Rick Sprague
Want the best answers? See faq181-2886
[COLOR=blue]To write a program from scratch, first create the universe.[/color] - Paraphrased from Albert Einstein

 

[PHV]

Simply press the F1 key when the cursor is inside the word ItemsSelected and in the VBA help file you'll see 2 examples.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[beckyh]

Thanks RickSpr, but now I get the "Object Required" error when I try your code, and it errors out on the line where I mention the column see below...


Dim cnn As Connection
Dim rstPSN As New ADODB.Recordset
Dim varPosition As Variant

Set cnn = CurrentProject.Connection
rstPSN.Open "CalculateInvTot", cnn, adOpenKeyset, adLockOptimistic, accmdtabledirect
cnn.BeginTrans
'rstPSN.Index = "SEIiD"
total = 0
For Each varPosition In List15.ItemsSelected
'rstPSN.Find List15.ItemData(varPosition)
total = total + ListBox15.Column(2, varPosition)
Next varPosition
cnn.CommitTrans

Any ideas??

 

[RickSpr]

I guess you're saying it errors out on the line:
total = total + ListBox15.Column(2, varPosition)

Is "total" a control? I don't see a Dim for it. If it is a control, it may be you're having a problem with VBA's assumptions about when you mean the control and when the default property. Try using total.Value instead, or use a temporary variable and assign the result to total.

Also, note that Column(2,...) refers to the third column, not the second. Columns are indexed from 0.

Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[beckyh]

Thanks Rick, this is what worked for me.....
Dim cnn As Connection
Dim rstPSN As New ADODB.Recordset
Dim varPosition As Variant
Dim total

Set cnn = CurrentProject.Connection
rstPSN.Open "CalculateInvTot", cnn, adOpenKeyset, adLockOptimistic, accmdtabledirect
cnn.BeginTrans

total = 0
For Each varPosition In List15.ItemsSelected
total = total + List15.Column(2, varPosition)

Next varPosition
cnn.CommitTrans
MsgBox total





However, now I have another question....similar concept, and I getting the runtime error 3001 - arguments are of the wrong type, are out of acceptable range, or are in conflict with one another."

this is my code Rick....
Dim cnn As Connection
Dim rstPSN As New ADODB.Recordset
Dim varPosition As Variant


Set cnn = CurrentProject.Connection
rstPSN.Open "SEI", cnn, adOpenKeyset, adLockOptimistic, accmdtabledirect
cnn.BeginTrans
'rstPSN.Index = "SEIiD"


For Each varPosition In List15.ItemsSelected

rstPSN.Find List15.Column(0, varPosition)

rstPSN!Paid = 1
rstPSN.Update


Next varPosition
cnn.CommitTrans

THANK YOU!!!!

 

[beckyh]

which errors on the rstPSN.Find List15.Column(0, varPosition) line.

 

[RoyVidar]

Look in the help files on the .Find method, there's two aspects

1 - supply the field name
[tt]rstPSN.find "fieldname=" & <your criterion>[/tt]

2 - it'l need a current/starting row per each search (i e use a rstPSN.movefirst prior to the .find)

Two more things - test for rstPSN.eof and you'd probably need something other than tabledirect (adcmdtable?)

Since you have another thread related to this new question, why not continue the discussion there (thread705-907638)? Have you had the time to read the faq RickSpr links to in his signature yet?

Roy-Vidar

 

[beckyh]

Thanks Roy. I will keep that in mind. I forgot about the previous post from the day before. Oops

I took your advice looking in Help, which I had tried first, but still the same error resulting. I took all your advice and that listed in Help, but still getting that pesky error. Humm??? Any other ideas?

This is what I added...
rstPSN.MoveFirst
rstPSN.Find (SEIiD = List15.Column(0, varPosition))

 

[RoyVidar]

Fieldname and operator need quotes, see my sample above, then the ampersand to concatinate, then your criterion. Parens around the expression is not needed, but I don't know if it matters if they are there.

Roy-Vidar

 

[beckyh]

THAT WORKED!!!!!!! Fabulous! Thank you! I am a newbie at VBA programming. I wasn't sure if the quotes were literally supposed to be there. I appreciate your help sir!