how do you identify a record in a recordset?

[Jonathan Snyder via AccessMonster.com]

Is there a way to identify a record in a recordset by row number instead of
using a criteria? I want to add a field to a table with sequential numbers
for each recordset. For the most part the recordsets have three records.
How can I reference record #1, #2, # 3 to set a field value to 1, 2, and 3
respectively ?

Thanks

 

[Brendan Reynolds]

You can't identify records in a Jet database by 'row number', because
records in a Jet database don't have any row numbers. If you specify a
particluar order in the SQL statement, however, you can process records in
that order. For example ...

Public Sub TestSub()

Dim rst As ADODB.Recordset
Dim lngLoop As Long


Set rst = New ADODB.Recordset
With rst
.ActiveConnection = CurrentProject.Connection
.Source = "SELECT * FROM tblTest ORDER BY TestNum"
.CursorLocation = adUseClient
.CursorType = adOpenKeyset
.LockType = adLockOptimistic
.Open
Do Until .EOF
lngLoop = lngLoop + 1
.Fields("TestText") = CStr(lngLoop)
.Update
.MoveNext
Loop
.Close
End With

End Sub