Here's the deal: I have a table with a field called 'Random'. I put random numbers in that field and then sorted it by Random (using the Sort button while the table was open) to put it in a random order. I wanted to maintain this random order after another set of records is added to it. So I created another field called 'Order_Num'. I wrote a routine that just stepped through this table an putting an incrementing number in the Order_Num field. Here's the code:
Dim MyDB As Database
Set MyDB = DBEngine.Workspaces(0).Databases(0)
Dim WorkData As Recordset
Set WorkData = CurrentDB.OpenRecordset("tblRandomIntervVisits")
Dim i As Integer
i = 1
WorkData.MoveFirst
Do Until WorkData.EOF
WorkData.Edit
WorkData!Order_Num = i
WorkData.Update
i = i + 1
WorkData.MoveNext
Loop
Problem is, when I go back and look at the table, it is not in the original random order (by Random) anymore. Somehow, when it brought the table into the recordset (WorkData) it imposed its own order on it and thus screwed up the original sorted order. None of the fields are Indexed. There appears to be no other order present, but it IS kinda separating them in blocks based on another field, Population (1/0). A block of 1's, a block of 0's, 1's, 0's, etc.
WTF?