M
Mac
Any suggestions on how to Sort a recordset and then Seek in that same open
recordset? I have a history table where the recordset must be sorted in the
order in which the records were ENTERED, not in ascending by serial number.
In a previous discussion thread a Sort was suggested but I haven't
sucessfully implemented the Sort yet. I tried to add a Sort to my existing
subroutine but it errors out with "Current provider does not support the
necessary interface for index functionality.". . Any suggestions on how to
add a Sort to the same subroutine with a Seek and get it to not error? I have
inserted my working code with just the Seek for your viewing pleasure. Any
suggestions are appreciated.
Dynamic_Update_Recycle_Repair_Counts()
'Open History recordset.
Dim rsHistory As New ADODB.Recordset
'SORT of incrementing field (not autonumber) MUST GO HERE?
With rsHistory
..Open "History", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
'No primary key because multiple entries of same serial number
..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
recordset? I have a history table where the recordset must be sorted in the
order in which the records were ENTERED, not in ascending by serial number.
In a previous discussion thread a Sort was suggested but I haven't
sucessfully implemented the Sort yet. I tried to add a Sort to my existing
subroutine but it errors out with "Current provider does not support the
necessary interface for index functionality.". . Any suggestions on how to
add a Sort to the same subroutine with a Seek and get it to not error? I have
inserted my working code with just the Seek for your viewing pleasure. Any
suggestions are appreciated.
Dynamic_Update_Recycle_Repair_Counts()
'Open History recordset.
Dim rsHistory As New ADODB.Recordset
'SORT of incrementing field (not autonumber) MUST GO HERE?
With rsHistory
..Open "History", CurrentProject.Connection, _
adOpenKeyset, adLockOptimistic, adCmdTableDirect
'No primary key because multiple entries of same serial number
..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