looping listboxes

[skate]

I want to loop through the items of a listbox in my form and add the values from a certain column in the listbox to be displayed in a field on the form. How do I do this? This must be a silly question. This is what I have.

Private Sub Hrs_Calc()
Dim i As Integer
Dim JHrs As Double
Set JHrs = 0
For i = 0 To lstResults.ListCount - 1
JHrs = JHrs + Month.[Job Hrs]
Next i
Me.Job_Hrs = JHrs
End Sub

 

[scottfarcus]

Private Sub Hrs_Calc()
Dim i As Integer
Dim JHrs As Double

For i = 0 To lstResults.ListCount - 1
JHrs = JHrs + lstResults.Column(1, i)
'assumes the data you are looking for is in column 1 (the
'second column in the list box)
Next

Job_Hrs = JHrs

End Sub

 

[skate]

why does it tell me i have a type mismatch?

 

[scottfarcus]

Describe the columns in this list box.

Or email me the database.

 

[skate]

I have 5 columns
MIndex - autonumber
SIndex - number (long int)
MOrder - number (long int)
Month - Text
Job Hrs -number (double)

if that helps

 

[scottfarcus]

which column would you like to sum?

 

[skate]

job hours

 

[scottfarcus]

Try this...

Private Sub Hrs_Calc()
Dim i As Integer
Dim JHrs As Double

For i = 0 To lstResults.ListCount - 1
JHrs = JHrs + lstResults.Column(4, i)
'assumes the data you are looking for is in column 4 (the
'fifth column in the list box)
Next

Job_Hrs = JHrs

End Sub

If you are still getting a mismatch error, the field "Job_Hrs" might not be a number field.

 

[skate]

i must be missing something, but that's not it either. I'll email it too you if you don't mind looking at it? thanks

 

[scottfarcus]

send it on over.

 

[skate]

Thanks a lot! That did it.

 

[scottfarcus]

You're welcome.

Future reference (and for the others on this board):

If you are using Headers in your list box, you must check for an integer when looping through the items, . The first item will return the Header value, NOT the value of the first data item.

Good luck!