Auto Numbering Stopped Auto Numbering

[JCMcAbee]

Recently I imported about 200 records from a spreadsheet into an existing database. These records all had unique numbers in a number field that were sequentially in order with the MDB auto generated numbers. Now when I add a new record I no longer get the 'next' autonumber. Instead I get something like 1E+08 and when the field is viewed on a form it has about 10 digits and is not sequential. Hindsight being 20-20, I think I probably should have appended the records instead. The first 2550 records are numbered correctly sequentially, including the imported ones, but new records have the 1E+ thing going on.
How can I correct this problem?

 

[genomon]

The "1E+ thing" usually happens when the display field is not physically wide enough to display the value. Not sure about the autonumber issue, though there is code in the FAQs that you can run to reseed/renumber these fields.

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[JCMcAbee]

Thanks for the reply. The '1E+ thing' was a real 'DUH!' on my part. However, I still have incorrect sequential problem. The next record should have been four digits starting with 25xx, but instead it is ten digits starting with 99xxxxxxxx. Any help is appreciated.

 

[GingerR]

Hi--what does it matter what the Autonumber ID is? It's only for table relationships.

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

 

[genomon]

Are you saying the autonumber does not go from lesser to greater values, or just that the first new one is not consecutive? Either way it's as Ginger says - no reason to print it back; if it's the primary key it will obviously need to be unique, though...

Money can't buy happiness -- but somehow it's more comfortable to cry in a Corvette than in a Yugo.

 

[JCMcAbee]

Thanks for the reply. What you say makes sense. It is for reference only. So might as well leave it alone.

 

[SeeThru]

There are a couple of setting for the autonumber field in the table - play with these for sequential or pseudo random numbering.

Also, copact the database to set the nmubering back to the next available number if using sequential numbering.

If it still starts at 99xxxxxx then you may have a high number hidden in there somethere - find it and delete/change the record. Note that you cannot change an autonumber, so you may need to import the records into a new table, fix it and then change the field back to autonumber.

 

[boblarson]

An Autonumber should not be used for meaningful purposes unless you can live with the fact that it might not be consecutive, it may jump around, and it could be negative. It ONLY guarantees a UNIQUE number and generally is considered "not fit for human consumption." It is for generating a unique ID, really nothing more.

If you need consecutive numbers, use a DMAX + 1 setup.


Bob Larson
A2K,A2K3,A2K7,SQL Server 2000/2005,Crystal Reports 10/XI,VB6, WinXP, and Vista
Free Quick Tutorials and Samples:

http://www.btabdevelopment.com