CurrentRecord equivalent in ADO

[mmitchell]

What is the syntax for telling me what current record I am on in a record set using ADO? In DAO it is rst.CurrentRecord, but I can't seem to find the equivalent in ADO!!

Old DAO code:

'If Me.RecordsetClone.BOF = False Then
' Me.RecordsetClone.MoveFirst
'
' For i = 1 To Me.CurrentRecord
' If strItem = Me.RecordsetClone.Fields("ComboCustPartNum") Then
' lngRunningTot = lngRunningTot + Me.RecordsetClone.Fields("QtyGood")
' End If
' Me.RecordsetClone.MoveNext
' Next i
' Me.TotalQtyToRecord = lngRunningTot
'End If

Basically I am trying to find the sum of the QtyGood up to (and including) the current record that I am looking at when on a form. I even tried to change from a recorset clone of the current form to just making a seperate recordset, but nothing seem to work, the code above just keeps adding the first QtyGood to itself. It is like it never sees the next records value.

 

[workmad3]

ok.. if you want to use an ADO recordset, then why not try the following as the recordset command(s)

Dim conn as new ADODB.connection
Dim rs as ADODB.Recordset

conn.connectionstring = <your connection string>
conn.open
Set rs = conn.Execute("SELECT SUM(QtyGood) AS Total FROM <table>")
Me.totalQtyToRecord = rs.Fields("Total")

It would be a good idea to test the above before use with a simple form to make sure the current record is added in.

Hope this helps

 

[mmitchell]

Workmad3, your alternative has no criteria. It will return the sum for the entire recordset. The problem is to sum the records up to a certain point.

Of course the best solution is to always use autonumbers as the pk for your tables. That way you can use the pk of the current record as criteria so you can select all rows with a pk less than that of the current record.

Yes, I agree, but i don't get to modify the tables, just my code.