Sum of a Column in a List box 1

[ScorpioX]

Hello,
This one is driving me nuts. I am trying to get a field to populate the sum of a specific column from within a List box. I can not figure it out. Anyone got any ideas?

The form is: frmMain
The List box on the form is: List58
The Column in the List Box is: Column 6
The Unbound box is: Running_Total

I have tried many ways the latest is:
In the Unbound box I put: =Sum(List58.Column(6))
It does not seem to be working. All it does is error out.

Any help as always is appreciated!

Thanks,
ScorpioX

 

[fneily]

=Sum(List58.Column(6)) is only doing one record. You can't sum one number. The Column property takes two arguments so Column(1,2) for example. Column 1, Row 2. So you have to loop down the rows.
So you can use ListCount to get the number of rows. Then sum as you loop. Here's an example: If the user double clicks the listbox, it'll sum the numbers in Column 6. Remember also row counts start at 0.

Private Sub List7_DblClick(Cancel As Integer)
Dim listrs As Integer
Dim listsum As Integer
listrs = Me![List7].ListCount
x = 1
Do
listsum = listsum + (Me![List7].Column(6, x))
x = x + 1
Loop Until x = listrs
Me![Text9].Value = listsum
End Sub

This is with column headers. If no column headers, take out x = 1.

 

[fneily]

Also, you should post Access questions in the Access forums.

 

[ScorpioX]

Fnelly,
Thank you, that worked like a charm. Also sorry about posting in the wrong forum. I thought I was in the Access forum when I posted.

ScorpioX

 

[arsicfilip]

Thanks to both of You.
First for asking question (even in wrong forum) and second thanks to the fneily.
Been searching this solutin for 2 days

(sorry for bad english)

 

[arsicfilip]

I know this is not ms access forum bur i will ask here question because this solutinon is here.
Here is problem:
when I aplly this formula I get error message "Invalid use of null" when is no records to sum.
How to avoid null error.
Thnx in ahead

 

[arsicfilip]

Got answer on access forum:

Private Sub Command13_Click()
Dim listsum As Long, x As Integer
With Me!List11
.Requery
For x = 0 To .ListCount - 1
listsum = listsum + Nz(.Column(2, x), 0)
Next
End With
Me!suma.Value = listsum
End Sub