W
Willy van Vroenhoven
This seems to be an error in the implementation of the access controls. Because if you use the adodb recordset methods to iterate all records more then 100 records are visible and readable. I have been strugling for years to find a workaround for this problem. Lately I came up with a solution.
I have created a method that returns a sorted clone of a given recordset. So the recordset that is returned by the function has exactly the same amount of records but they are sorted different then the orginal recordset. The advantage of this is that after the sort we do not have to set the sort property of the recordset because the records are already in the correct order. And if we apply a filter and bind to an Access control or form the problem with only displaying 100 records will no longer exist.
See Code below.
Public Function GetSortedRecordset(ByRef Source As ADODB.Recordset, ByVal Sort As String) As ADODB.Recordset
'Declare local variables needed for cloning
Dim varFilter As Variant
Dim strSort As String
Dim objStream As ADODB.stream
'Create a new ADODB stream object
Set objStream = New ADODB.stream
'Save the filter into a variable for later use.
varFilter = Source.Filter
'Remove the filter from the recordset.
Source.Filter = ADODB.adFilterNone
'Apply the sort to the recordset.
Source.Sort = Sort
'Save the sorted recordset to an ADODB stream.
Call Source.Save(objStream, adPersistADTG)
'Create a new recordset from the stream object
Set GetSortedRecordset = New ADODB.Recordset
Call GetSortedRecordset.Open(objStream)
'Release the ADODB stream object.
Call objStream.Close
Set objStream = Nothing
'Reapply the filter
GetSortedRecordset.Filter = varFilter
End Function
Greetings,
Willy van Vroenhoven
Opinio B.V.
www.opinio.nl
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng
I have created a method that returns a sorted clone of a given recordset. So the recordset that is returned by the function has exactly the same amount of records but they are sorted different then the orginal recordset. The advantage of this is that after the sort we do not have to set the sort property of the recordset because the records are already in the correct order. And if we apply a filter and bind to an Access control or form the problem with only displaying 100 records will no longer exist.
See Code below.
Public Function GetSortedRecordset(ByRef Source As ADODB.Recordset, ByVal Sort As String) As ADODB.Recordset
'Declare local variables needed for cloning
Dim varFilter As Variant
Dim strSort As String
Dim objStream As ADODB.stream
'Create a new ADODB stream object
Set objStream = New ADODB.stream
'Save the filter into a variable for later use.
varFilter = Source.Filter
'Remove the filter from the recordset.
Source.Filter = ADODB.adFilterNone
'Apply the sort to the recordset.
Source.Sort = Sort
'Save the sorted recordset to an ADODB stream.
Call Source.Save(objStream, adPersistADTG)
'Create a new recordset from the stream object
Set GetSortedRecordset = New ADODB.Recordset
Call GetSortedRecordset.Open(objStream)
'Release the ADODB stream object.
Call objStream.Close
Set objStream = Nothing
'Reapply the filter
GetSortedRecordset.Filter = varFilter
End Function
Greetings,
Willy van Vroenhoven
Opinio B.V.
www.opinio.nl
EggHeadCafe - .NET Developer Portal of Choice
http://www.eggheadcafe.com/default.aspx?ref=ng