MoveFirst seems to be MovingMiddle.... Please help

[bghedtodd]

I'm stumped, and could really use an expert's advice. I have two tables: raw and matrix -- both are sorted ascending on the first row (if that matters). raw has 607,513 rows and 4 fields. Matrix has 383,078 rows and 27 fields. When I run the following code with matrix as my recordset, it moves to the first row, and picks off the value in the first field, displaying it in the messagebox (as desired)... but when I run it with raw it displays the first field value of row 249 -- not the first record. If I try to use a for i= 0 to 248 with MovePrevious, it bombs when i=1. Can anyone think of why it would not move to the first record with one particular table? How can this be fixed? Thanks in advance!

Sub main_Base()

Dim mydb As DAO.Database
Dim raw As DAO.Recordset

Set mydb = CurrentDb
Set raw = mydb.OpenRecordset("2_Base_Demands_by_Week")

raw.MoveFirst
MsgBox raw(0)
raw.Close

End Sub

[gershwyn]

The data in a table is not stored in any particular order. Sorting it by a certain field will change how it is displayed, but won't change the order the records are accessed in a VBA recordset. For that, we need to set an index.

Open the underlying table in design view and add a new index on the field (or fields) that you want the order based on. Then set the index property of the recordset before you call MoveFirst. Something like this:

Set raw = mydb.OpenRecordset("2_Base_Demands_by_Week")
raw.Index = "myIndex"
raw.MoveFirst

If needed, you can also create the index programatically - lookup the TableDef.CreateIndex method in help. I don't use it much, so the code example will probably be easier to follow than my explanation.

[bghedtodd]

Thanks gershwyn!