var sidebar_align = 'right';
var content_container_margin = parseInt('290px');
var sidebar_width = parseInt('270px');
Unanswered: AutoNumber field with missing numbers
I have a table that uses the AutoNumber for a Primary Key and is the critical number in assigning records. I have some records now where there an AutoNumber isn't in the table.
Is there a way to find those 'lost' records?
Records 1-661 are fine. The next record is 667, so I'm missing from 662-666. No before anyone goes apocolypic(sp?) on me, is there anything I can do to get back those records?
These records may never have existed.
If you start entering a new record, an autonumber (662 for example) for that record is assigned. If you then press escape to cancel that new record, that number wil not be used again; the next time you enter a record it will get 663.
PS: apocalyptic :-)
Autonumbers can be a more trouble then they're worth sometimes.
Generally, I just use an integer field to replace and autonumber field and then write the code to select the max number in the field and add 1 to it.
Primary Key is nId which is the integer field
set get_nextid = (SELECT MAX(nId) AS "nId" FROM tablename)
nextid = 1000
nextid = get_nextid("nId") + 1
still doesn't help
We still want to not have to keep a book or sheet or log somewhere as to what the next number to assign a project is. At least with the AutoNumber, the project gets assigned the next available number and I don't have to keep another log somewhere.
If I did that, I'd have no need for the AutoNumber, but no one here wants to keep a log of project numbers and it wouldn't be convenient to have that located in any particular office.
Thanks though, for the reply.
please explain why a missing number is a problem
are there any tables with a foreign key to this autonumber? if so, are there any related records referring to a missing autonumber?
Re: AutoNumber field with missing numbers
This doesn't always work, so try it on a backup copy of your database:
Copy your Original table,
Paste it with a new name (say "xxx"), structure only.
Open xxx; remove input mask(s),
Change your ID field from Autonumber to Number.
Go to "Original", block the "offending" records (#667+), cut,
Go to xxx, block record 1, paste,
Manually correct the numbers.
Close both tables, click on: Tools - Database Utilities - Compact and Repair Database.
Open "xxx", select the corrected records, copy.
Open "Original", remove input mask(s); block the blank record at the end, paste.
Restore input mask(s).
Close both tables; delete "xxx".
Again, gaps in Autonumber is more of a "cosmetic" problem - it isn't critical except when it confuses your users. It will be a recurring problem and eventually they will learn to live with it.
Last edited by garyc; 01-16-03 at