rs.addnew doesn't add records in the correct order

[MatthewGrace]

Hello, thank you for considering my question.



I have some code below which loads a recordset object (rs) from a stored query.
I also have a second recordset object (rsBlank) with the same Field structure as rs.
The goal is to take the records from rs and copy them into rsBlank.

rs is a massive table being distilled down to just a few dozen records.
rsBlank is a blank table... whose sole job is to copy those few dozen records for scrutiny in more involved code later.

Seems simple enough:

Dim rs As Recordset
    Set rs = CurrentDb.OpenRecordset("qryAssignInvoiceNumbers_UnInvoiced", dbOpenSnapshot) 'Get all live orders with no invoice yet.
    Dim rsBlank As Recordset
    Set rsBlank = CurrentDb.OpenRecordset("tblAssignInvoiceNumbers", dbOpenDynaset) 'I have tried dbOpenTable, which I thought should be sufficient, but I get this same problem.


    'Clear the local table.
    CurrentDb.Execute "DELETE * FROM tblAssignInvoiceNumbers"


    'Collect into local table.
    If rs.EOF = False Then rs.MoveFirst 'I added this line hoping it would help avoid the problem.  It does not.

    Do While Not rs.EOF
        rsBlank.AddNew
        rsBlank![CompanyName] = rs![CompanyName]
        rsBlank![OrderID] = rs![OrderID]
        rsBlank![OrderDate] = rs![OrderDate]
        rsBlank![Jobname] = rs![Jobname]
        rsBlank![Product] = rs![Product]
        rsBlank![Description] = rs![Description]
        rsBlank![Price] = rs![Price]
        rsBlank.Update
        rs.MoveNext
    Loop

I don't get an "error", but this is the problem:
If you double click on tblAssignInvoiceNumbers, which is the table rsBlank is connected to, the records are not in the same order as they are in rs.

So for example in rs, the first record could have the OrderID 1000 and the last record could be 1100. However, in the tblAssignInvoiceNumbers the middle record might be OrderID 1000.

It get's weirder: It only happens sometimes. I'd say 80% of the time it runs perfectly and rsBlank is a direct copy of the recordset, row-by-row. Other times, it's as if you cut a deck of cards in half: the bottom 50 records starts on top of the table, and the top 50 records are on the bottom! Not sure why.

All I know for sure is: rs is reading fine. I've stepped through the code in break mode and read each field value through each Loop iteration, it's always sorted perfectly in accordance with the ORDER BY clause of the rs Query. But then when the code is finished and you look at the grid being written to, sometimes it's just not in the same order.

Any ideas why this could happen? I can find a workaround perhaps, but I'd like to learn.

Thank you,
Matt

[CJ_London]

data is stored in tables randomly, there is no order. If they do appear in the expected order then that is purely a coincidence. To view in an ordered way, you need to sort it

[MatthewGrace]

Thank you sir.

[Welshgasman]

If you are only adding records, why bother opening the table with data?
Also why not Delete that data first, THEN open the recordset?
A query would also be far more efficient.

[MatthewGrace]

Welsh...
I'm not sure what you mean, and while I understand what some code means, I seldom understand what makes one way more efficient than another. I'm willing to learn if you feel what I've done is unwise.

So far, I'm thinking what you're suggesting is that I transfer the code below to before I instantiate the rsBlank variable. Is that right?

CurrentDb.Execute "DELETE * FROM tblAssignInvoiceNumbers"

That's my interpretation of your first two questions. The third line you wrote, about a query being more efficient, I'm not following.

Final question if you're willing: Is it meaningful to do rs.movefirst the way that I did? Sometimes I'll see advanced coders type this before even using a recordset... even thought they're already on the first record. I think it has something to do with forcing a dynaset to be "loaded" but I'm not entirely sure.

matt

[Welshgasman]

Not an expert by any means, but I have see people open a recordset on a table (no idea as to how many records in that table), only to add records.?
So you read in a bulk of records needlessly, just to add records.? I used WHERE 1=0 when I had to, but there is an option on the open just to add, but you would need to google the syntax.
As you are deleting the records anyway, why not delete first, then you open to an empty table for the recordset?

Just my logic perhaps?

Re the query, you could just as easy use a simple update query which is always faster than a recordset process. Not wrong, but up to you.
I would have done it that way a few years back, but have learnt different from others.

I personally never MoveFirst on opening a recordset, as I firmly believe you are at the first record if it exists when doing so. I also only test for EOF when doing that, not BOF as well, but again that is just me.

[Micron]

Where I usually go for rs refresher http://allenbrowne.com/ser-29.html

WGM - I can tell you from experience that if your loops have always started at the first record, it is only a fluke if any of them were based on tables and unordered queries. I have seen it on a few occasions while creating code for forum responses. In fact, it happened yesterday over at Mr. Excel. I agree with Ajax (post 2). Perhaps it's just a matter of time? Depending on the situation, it might not matter as all the records will be accessed if nothing acts to prevent that from happening.

[Welshgasman]

When I said first record, I meant just the first record presented, regardless of how it has been sorted?
I have yet to open any file (or recordset) and not be on the first record if it exists.