AutoNumber problem 1

[colinrharris]

I have an Access 2003 database where a form is used to enter details of customers, sites and contacts. The main form is Customers with the other two sub-forms are embedded. Each form has an AutoNumber which has, until now, incremented correctly. However, my business partner, who accesses the database over a vpn connection, made a new entry and the AutoNumber on the main form jumped by 2845!! The two sub-forms incremented correctly. A few entries have been made since then and the AutoNumber has incremented correctly, albeit from its new starting point. To test if it was anything to do with either the vpn or his version of Access he entered another new record today with no problem.
Any idea what may have caused this and how I can recover from it?
Thanks.

 

[GingerR]

Why do you need to "recover" from it? Autonumber is just an ID to build relationships with and identify data, it doesn't matter what it is.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[colinrharris]

Thanks for your response. Yes, I am aware of that but I was also hoping to use the Customer ID as a customer reference or account number. I believe that there can only be one autonumber in a table so thought this was the best method. If there is a better way, I would really like to hear it. I would still like to know what may have caused the autonumber to jump though.

 

[alvechurchdata]

I would still like to know what may have caused the autonumber to jump though.

All I can say is that autonumbers aren't guaranteed to be sequential. I've seen the occassional value skipped before but never such a huge leap.

As for the best way to generate sequential numbers, create a table of key values which holds the next available number for each of the fields that you need to number and use (and update) that number each time that you create a new record. If you do the job yourself then you can write in the code that lets you back out of creating a new record without having to "waste" a number.

Geoff Franklin

http://www.alvechurchdata.co.uk

 

[MasterRacker]

Even if you're using the ID as an account number, who cares if some are skipped? It's still just a unique identifier for an account.

As Geoff said, if you want to guarantee no "missing" numbers, you'll have to do the job yourself.

_____
Jeff
[small][purple]It's never too early to begin preparing for [/purple]International Talk Like a Pirate Day
"The software I buy sucks, The software I write sucks. It's time to give up and have a beer..." - Me[/small]

 

[missinglinq]

Not only can you have gaps, which can be caused by any of a number of reasons, you can, under some versions and circumstances, have duplicate Autonumbers!

John W. Vinson, MVP, said it best of all,"Autonumbers are not fit for human consumption!”

Here’s an excellent paper on the inconsistancies of Autonumbers as well as some tips on "rolling your own!"

http://www.applecore99.com/tbl/tbl012.asp

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[colinrharris]

Thanks for all your replies. I didnt realise the limitation on how autonumbers could be used. I will create my own Account Number field and hide the autonumber on any forms or reports.