total of a listbox

  • Thread starter Thread starter alecarnero
  • Start date Start date
A

alecarnero

I have a listbox with many rows one of the columns is price , is posible
obtain the sum of the rows in determinated column????

My listbox show this (example)


Item Price Qtd Total
Sock 10 2 40
Shirt 20 4 80
Sweater 30 3 90


How i can I add the third column total???

i use this line in vb for populate the listbox

Me.Lista69.RowSource = "select idvenda,codigo,produto,qtd,unit,total from
tbvenda WHERE nvenda like " & nven & " "
Me.Lista69.Requery


thanks in advance by any ideia
 
alecarnero said:
I have a listbox with many rows one of the columns is price , is posible
obtain the sum of the rows in determinated column????

My listbox show this (example)


Item Price Qtd Total
Sock 10 2 40
Shirt 20 4 80
Sweater 30 3 90


How i can I add the third column total???

i use this line in vb for populate the listbox

Me.Lista69.RowSource = "select idvenda,codigo,produto,qtd,unit,total from
tbvenda WHERE nvenda like " & nven & " "
Me.Lista69.Requery
Click to expand...

Note that the Requery is not necessary. Changing the RowSource property
automatically forces a requery.

I'm not sure if this is what you're looking for or not, but here's a quick
function to sum a particular column of a list box:

'----- start of code -----
Function fncSumListboxColumn(lst As Access.ListBox, colno As Integer) As
Double

Dim lRow As Long
Dim dblTotal As Double

dblTotal = 0

With lst

For lRow = Abs(.ColumnHeads) To (.ListCount - 1)
dblTotal = dblTotal + Val(.Column(colno, lRow))
Next lRow

End With

fncSumListboxColumn = dblTotal

End Function
'----- end of code -----

I'm not sure which column you want to sum. Your rowsource defines the
[total] column as the fifth column -- Lista69.Column(4). If that's the one
you want to sum, you could do it in code by calling the above function like
this:

TotalItems = fncSumListboxColumn(Me.Lista69, 4)

Of course, subforms are more commonly used than list boxes when you want to
display column totals, so it maybe better to use a subform than a contrived
solution like this function.
 
first thanks by the ideia dirk, i have write the function in my access file
, for me the logic is right but when i execute the form i have this error???

Private Sub Comando73_Click()
Dim nven As Integer
Dim totg, ctot, fncsumlistboxcolumn As Double
nven = Me.NVENDA2
DoCmd.SetWarnings False
DoCmd.OpenQuery "inse_ven1"
DoCmd.SetWarnings True
Me.Lista69.RowSource = "select idvenda,codigo,produto,qtd,unit,total from
tbvenda WHERE nvenda like " & nven & " "
Me.Lista69.Requery
totg = fncsumlistboxcolumn(Me.Lista69, 4)
Me.quant = " "
Me.pesquisa = " "
Me.pesquisa.SetFocus
End Sub

is expected a matriz ??? error compilation , what is wrong ??
 
alecarnero said:
first thanks by the ideia dirk, i have write the function in my access
file
, for me the logic is right but when i execute the form i have this
error???

Private Sub Comando73_Click()
Dim nven As Integer
Dim totg, ctot, fncsumlistboxcolumn As Double
nven = Me.NVENDA2
DoCmd.SetWarnings False
DoCmd.OpenQuery "inse_ven1"
DoCmd.SetWarnings True
Me.Lista69.RowSource = "select idvenda,codigo,produto,qtd,unit,total from
tbvenda WHERE nvenda like " & nven & " "
Me.Lista69.Requery
totg = fncsumlistboxcolumn(Me.Lista69, 4)
Me.quant = " "
Me.pesquisa = " "
Me.pesquisa.SetFocus
End Sub

is expected a matriz ??? error compilation , what is wrong ??
Click to expand...


This line is clearly wrong:
Dim totg, ctot, fncsumlistboxcolumn As Double
Click to expand...

You shouldn't be overriding the function name by redefining it as a
variable. Also, that line will declare totg and ctot as Variants, not as
Doubles. I think you probably intended them to have the Double data type,
so your declaration should be:

Dim totg As Double, ctot As Double

You should have pasted the function code itself into a standard module (
assuming you may want to call it from more than one form or code module).
Also, the module name should not be the same as the function name -- that's
a mistake people sometimes make.
 
I have resolve with this simple line

totg = DSum("total", "tbvenda", "nvenda = " & nven)

Me.totalgeral = totg

Me.totalgeral.Requery

Thanks
 
Back
Top