Pesky autonumber bug (2007 & 2010)
It has been around for a while; the autonumber bug. So far it hasn’t caused any troubles and Microsoft has a simple workaround for it. Before entering data in the table save the table first. So what is the autonumber bug exactly. When you create a table in datasheetview your first field will be a autonumber field. After you create your first fieldname there is no problem yet. When you create your second field there is still no problem. The problem arises when you start entering data in the records when the table has not been saved yet. When you start entering data in the first custom field you will see that the autonumber is incremented by one. If you enter data in the second field the autonumber is incremented by one again.
Tables in webdatabases
When creating tables for webdatabases this issue might arise more then when creating rich client tables because most of the times you will be designing those in designview. So moral to this post is to save your table first before starting data entry! Because this bug will only show up in datasheetview you will find this bug in Access 2007 and 2010.
Use Autonumbers but do not use them for anything else!
One of those questions in the communities has been “How can I reset my autonumber?”. Most of the times you will see replies which start with “Why would you want to do that?”. The autonumber field should only be used for making the record unique within the table. Because autonumber can be unpredictable it is not wise to use it for any other purpose than that. But to answer the question; is it possible? Yes it is and if you want to reset it you know what the answer will be 😉