Prev: VBMacro to VB (VS 2008)
Next: MSHFlexGrid not scrolling beyond 65,535 rows - see Q253211. Any fix?
From: Jason Keats on 9 Jun 2010 07:16
Raymon wrote:
>
> I have come across a bug in ADO recordset Find method when used in
> conjunction wirth Sort descending.
> To reproduce it try the following code fragment.
>
> Dim rs As ADODB.Recordset
> Dim i As Integer
>
> Set rs = New ADODB.Recordset
> With rs
> .Fields.Append "id", adInteger
> .Open
>
> For i = 1 To 10
> .AddNew
> .Fields("id").Value = i
> .Update
> Next
>
> .Sort = "id Desc"
> .MoveFirst
> .Find "id< 6"
> End With
>
> The data is just representative data. Any other data can be used.
>
> The code orders the data in descending order and is supposed to find
> the first record less than the value in the filter, in the above less
> than 6, which is the record with value of 5.
>
> But no matter what value you use in the filter (2 to 10) the Find
> method always points at the first record, with the value of 10.
>
> In the code above the recordset is constructed in code. But I have
> tried it with a recordset coming from SQL server with the same result.
>
> However, if you rewrite the code so that the records are already
> in descending order, e.g. For i = 10 To 1 Step -1, and REMOVE the
> .Sort statement, then the code works as expected, i.e. in the
> above example the Find method points to the record with value of 5.
>
> Interesting is that the reverse situation works properly, i.e. Sort
> ascending with filter greater than ">".
>
> So the problem is with Sort in descending order.
>
> Is this a documented bug?
>

I've no idea whether it's a documented bug, but if you use

..Fields.Append "id", adInteger, , adFldIsNullable

then the problem seems to go away.

Alternatively, you could use

..Filter = "id < 6"

instead of .Find "id < 6"

HTH
 | 
Pages: 1
Prev: VBMacro to VB (VS 2008)
Next: MSHFlexGrid not scrolling beyond 65,535 rows - see Q253211. Any fix?