Seek in Recordset not working.

[Guest]

It looks like when I open a recordset and search for a specific serial
number in the recordset, using adSeekLastEQ, that the recordset is opening
and sorting the records in some order other than the order they were entered,
ascending possible. The result is that when searching for a record I need the
last one that was entered because it is a history. The table is a history of
activity for all serial numbers. How do I get the table (recordset) to open
in the order (sort) in which it was created Sequentially?

I have included the code below. Thanks for your help.

Dynamic_Update_Recycle_Repair_Counts()

'Open History recordset.
Dim rsHistory As New ADODB.Recordset
With rsHistory
..Open "History", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
..Index = "Serial Number"
..MoveFirst
End With

'Open "PCM Interfaces (Main Table) recordset.
Dim rsMainData As New ADODB.Recordset
With rsMainData
..Open "PCM Interfaces (Main Table)", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
..Index = "PrimaryKey"
..MoveFirst
End With



Do Until rsMainData.EOF = True




'rsHistory.MoveFirst
'find the last instance,if any, of the current interface and update the
recycle count and repair count
rsHistory.Seek rsMainData("Serial Number"), adSeekLastEQ


If Not rsMainData.EOF Or rsMainData.BOF Then

rsMainData("Recycle Count") = rsHistory("Recycle Count")

rsMainData("Repair Count") = rsHistory("Repair Count")

Else


End If

'Update the current record before moving to the next record
rsMainData.Update

'Move to the next record in the "PCM Interfaces (Main Data)" table.
rsMainData.MoveNext


Loop

'Close the recordsets
rsMainData.Close
rsHistory.Close
Set rsMainData = Nothing
Set rsHistory = Nothing

End Sub

 

[Larry Daugherty]

Access/Jet will arrange things to meet its own needs. Data is
maintained in an unordered bucket. You apply a Sort to see it in a
particular order. The only way to be able to unerringly track order
entry is to sequence it yourself - No, don't use an Autonumber
datatype - they are not guaranteed to generate an unbroken sequence.
Create your own sequence by using something like
"Dmax(MySequenceField)+1" Then sort on your sequence field.

HTH

 

[Guest]

Larry, I read up on sorting a recordset and tried to implement a sort.
Because I need to specify the cursorlocation as aduseClient it seems to be
causing problems with my .Seek method on that same recordset in that module.
It is returning an error of "Current provider does not support the necessary
interface for index functionality.". How do I sort the recordset and seek in
the same module without errors? My code is still attached to the initial
posting if you would like to see how I open the recordset and implement the
..seek method.