Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2002
    Grand Rapids, MI

    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?


  2. #2
    Join Date
    Nov 2002
    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 :-)

  3. #3
    Join Date
    Jan 2003
    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)

    if(isNull(get_nextid("nId"))) then
    nextid = 1000
    nextid = get_nextid("nId") + 1
    end if

  4. #4
    Join Date
    Dec 2002
    Grand Rapids, MI

    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.

  5. #5
    Join Date
    Apr 2002
    Toronto, Canada
    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?


  6. #6
    Join Date
    Jan 2003
    Bolton, Ontario, Canada

    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 12:35.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts