Recordset order

[Margie]

I have a module with a recordset that references an
indexed table, but when I am updating the recordset by
first using a .movefirst and subsequently a .movenext, it
looks like the records are being updated in a random order
(not by the index order). I've tried adding a .index to
reference the desired sort prior to traversing the
recordset, but it doesn't seem to help. When looking at
the table, the records appear to be in the desired sort
order.

Any ideas why the records don't update sequentially?

 

[Douglas J. Steele]

How are you creating the recordset? Unless you're using SQL with an ORDER BY
clause, you can't assume anything about the order of records. The order they
appear in when you open the table is not necessarily the order in which
they'll appear in a recordset based on that table.

 

[Allen Browne]

Specify the sort order when you open the recordset.

Example:

Dim strSQL As String
Dim rs As DAO.Recordset

strSQL = "SELECT [MyTable].* FROM [MyTable] ORDER BY [MyTable].[ID];"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
....