sum database fields and put on vb form

10-22-2004, 06:04 PM
I have a column of nothing but amounts in currency.
I would like to take that all those amounts and put the sum in a textbox on my form. The amounts show up on a datagrid on my form which is connection using native adodc. My main form is connected using adodc1 controls.
Here is what I have so far.
Public Sub accountlistload() ''datagridload'''
Dim sSql As String
Dim sSql1 As String
dim temp as currency
Dim rsInformation As New ADODB.Recordset
Dim mCnn As New ADODB.Connection
'''''connection for sum query'''
Dim rsInformation2 As New ADODB.Recordset
Dim mCnn2 As New ADODB.Connection

mCnn.Open "mhp"
mCnn2.Open "mhp"

sSql = "SELECT * FROM accountlist WHERE spaceno = " & Adodc1.Recordset("spaceno").Value

sSql1 = "SELECT SUM(amount)as temp from accountlist"
rsInformation.CursorLocation = adUseClient
rsInformation2.CursorLocation = adUseClient
rsInformation.Open sSql, mCnn, adOpenDynamic, adLockOptimistic
rsInformation2.Open sSql1, mCnn2, adOpenDynamic, adLockOptimistic

''''tempsum is the textbox for the sum result'''
Set temp = rsInformation2
tempsum.text = temp
Set DataGrid1.DataSource = rsInformation
If Adodc1.Recordset.EOF Then
End If
Set rsInformation = Nothing
Set mCnn = Nothing
Set rsInformation2 = Nothing
Set mCnn2 = Nothing
End Sub
do I really need two connections for the same table.
tempsum.text is on adodc1 control.

Dennis DVR
10-22-2004, 10:21 PM
what you need is a subquery for the sum of the amount i.e.

Public Sub accountlistload()
Dim sSql As String
Dim rsInformation As ADODB.Recordset
Dim Cnn As ADODB.Connection

sSql = "SELECT Accountlist.*, TheSum.Total FROM Accountlist " & _
"LEFT JOIN (SELECT Sum(Amount) AS Total, Spaceno FROM Accountlist) AS TheSum " & _
"ON TheSum.Spaceno=Accountlist.Spaceno " & _
"WHERE Accountlist.Spaceno = " & Adodc1.Recordset("spaceno").Value

'by using "WHERE Accountlist.Spaceno = " & Adodc1.Recordset("spaceno").Value
'you need to make sure that the Spaceno field is numeric.

Set Cnn = New ADODB.Connection
Cnn.Open mhp 'where did you define mhp? is this a public variable that holds your connection string?

Set rsInformation = New ADODB.Recordset
rsInforamtion.Open sSql, Cnn, adOpenDynamic, adLockOptimistic

Set DataGrid1.DataSource = rsInformation
DataGrid1.Columns("Total").Visible = False 'Hide the Total Column
tempsum.text = rsInformation.Fields("Total").Value
'tempsum should not be bound to any data control
End Sub

EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum