Getting Sum of Amount paid from a ms Access table using modules in vb6.0

[Hiram13]

Am trying to use this code to get the sum of amount paid but its giving me the output as True instead of the sum. where is the problem?

Dim DataEnvironment1 As New ADODB.Command
Dim rs As New ADODB.Recordset
Dim subs As String
 
On Error Resume Next
    DataEnvironment1.ActiveConnection = con
    DataEnvironment1.CommandText = "select sum(Paid) from Payment"
    rs.Open DataEnvironment1, , adOpenDynamic, adLockReadOnly
    If Not rs.EOF Then
    subs = rs.Fields(8)
    lblTotal.Caption = subs
    End If
    subs = txtPaid.Text = ""
    rs.Close

[Rabbit]

Line 10 is your problem. You have no Fields(8). Your query only returns one field, how can it possibly have a Fields(8)? I'm surprised it didn't give you an out of bounds error.

[Hiram13]

yes that's where the problem is but field Paid is number 8 in my database so don't know what to do now. i have a Ms Access table titled Payment with fields PaymentID, StudentID, Name, Surname, AdminNO, Class, Term, Fee, Paid, Bal, ModeOfPayment, Number, PaidOf, Date. i want to get the total from field paid to display on my Vb6.0 Form named frmPayment on lblTotal by press of a command button namely cmdTotal. Which code will i put on this command button and am using module to connect to the database? Please help!

[Rabbit]

It doesn't matter that the field in your table is the 8th field. It only sees the fields returned in the recordset. In this case, you only have 1 field in your recordset, so you need to use Fields(0).

[Hiram13]

thanks so much its working