Those Access Gremlins? An extra iteration before EOF

[Rod]

Has anyone experienced anything like this? I constructed a simple test db that included the following.

    Dim rst As Recordset
    Set rst = CurrentDb.OpenRecordset("Table2")
    Do Until rst.EOF
        ' My code in here
        Debug.Print rst!ID
        rst.MoveNext
    Loop

To my horror I found VBA was performing an extra iteration before exiting the Do loop. The extra iteration was for the first record in the result set. I played around with various settings to no avail. The only cure was to delete the table and reconstruct it from scratch - luckily not an arduous task. This implies that the table, not VBA, is the culprit. I have as yet not been able to recreate this error.

[Rod]

Belay that! As usual it is a case of human error: I assumed that VBA would return the records in the same sequence as Access displays them. What apparently was happening is that VBA was returning the records quite nicely in ID sequence except that it placed the first record at the bottom. An 'ORDER BY ID' solves everything.

[Missinglinq]

As you've found, the order in which a Form returns/displays Records is seldom the order that they appear in the underlying Table, which is, of course, the order in which they were originally entered. And your code is looping through the Table!

The most commonly used analogy for describing Records in a Table is that they are like a bag full of marbles!

Also, keep in mind, if your 'ID sequence' is referring to an AutoNumber, this Datatype is intended to be used for one purpose and only one purpose, to provide a unique identifier for each Record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how Autonumbers work:

When using Autonumber, do be aware that there will be gaps in the numbering any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, 1083225152 make such people get very nervous.

Here's a link to a Allen Browne discussion, that may be of interest, on AutoNumbers and how they can turn bad:

http://allenbrowne.com/ser-40.html

Linq ;0)>

[Rod]

Thanks for that. I've been at this long enough to know better! I won't even attempt to describe the thought process that led me into that original post except to say that the table was returned in perfect autonumber sequence except ID = 1 was last. This was just a quick prototype and I was using autonumber to simulate a sequence because I'm lazy and I needed a lot of data.

Agreeed that Allen Browne and John Vinson are worth following. I have read John's posts over many years and even corresponded with Allen regarding a real bug that I found a couple of years ago.