total a column in DataGrid

[Tailgun]

I have a form with a DataGrid one Column is currency I need to show the total of that column in either a txtBox or in the StatusBar. Any idea how I can do this ?

Thanks for any and all help

 

[Tailgun]

Is this possible ? Or am I just wishful thinking ?

 

[lovell811]

Try something like this:

Dim total as currency

total = CCur(gridname.CellText(gridrow, gridcolum)) + CCur(gridname.CellText(gridrow, gridcolum))


txtTotal.text = total ----------------
Joe

 

[Tailgun]

Since I have a number of rows isn't there a way to loop thru the Currency Column only to get a Total for just that column ? The column will contain different numbers of rows depending on the recordset.

 

[CCLINT]

Unfortunately, in a DataGrid, the row property only works on visible rows - in other words, what you can see on the screen and not what was on the previous page or next page.

Use the underlying recordset object to calculate the sum:

dim rs as ADO.recordset
dim dSum as double
set rs = Data1.Recordset.Clone

rs.MoveFirst
Do until rs.eof
dSum =dSum + rs("TheFieldToSumOn&quot
loop

Set rs = nothing

Text1.Text = dSum

 

[Tailgun]

Thanks CCLINT Will try that this morning it looks like your solution will solve my problem. Thanks again

 

[CCLINT]

er...did it work for you?

 

[Tailgun]

CCLINT I was under the weather yesterday so will give it a try today and let you know. Thanks again

 

[Tailgun]

I get an error here--method or data member not found.
set rs = Data1.Recordset.Clone

I'm actually using DataGrid1.Recordset.Clone

Recordset is highlighted when the error occurs

Any thoughts ?

 

[caseye]

Try using a standar collection or array when you populate the data grid populate the collection or array as well then if you use a collection you can loop through and add the

 

[CCLINT]

Two problems.
1. Data1 is the data control object that the grid is bound to - not the grid control it self.
Either the grid is bound to a DC or a recordset. If a DC, then use the DC object; if a recordset, then use the recordset object name.

2. I forgot to add MoveNext. Otherwise you will have an endless loop - it was typed on the fly:

Do until rs.eof
dSum =dSum + rs("TheFieldToSumOn&quot
rs.MoveNext
loop

 

[CCLINT]

If the grid is bound anyways, then why create an extra collection or array. We already have an array of data - the recordset object.....

 

[CCLINT]

Or rather, the Fields collection.

 

[lovell811]

i agree if your just going to clone it you should just use the recordset you already have. don't try and make it harder than it is! ----------------
Joe

 

[Tailgun]

Thanks CCLINT it works just fine now.