Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8

    Unanswered: Autonumber Not Next number in record

    Have a database which has over 7000 records in it

    This morning I go to add a new record it give a ID record is a number that has been used before

    Yes its a primary Key
    yes its a Long Integer
    Yes its Increment
    yes its Indexed yes ( No Duplicates)

    if I go into the table and manaully add a record
    I get a number 4083 but the record be for that is 9754
    if I esc esc which get rid of the record and try to add a new one
    it will add auto number of 4084 and so only
    I readly don't to do this a other 4000 time

    I can't delete the index as its link to other tables

    any ideas to fix
    Attached Thumbnails Attached Thumbnails Error1.JPG  
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  2. #2
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Autonumbers are intended to be used for one purpose and only one purpose, to provide a unique identifier for each record. Here's a post I've archived from a gentleman named John Vinson, MVP, explaining how autonumbers work:

    Quote Originally Posted by John Vinson
    When using Autonumber, do be aware that there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.

    In short... it's best NOT to use Autonumbers for human consumption, and particularly not for consumption by accountants and auditors. Invoice sequences like 319, 321, 322, 385, 386, 221841246, -1083225152 make such people get very nervous.
    If you want to your ID numbers to be consecutive you'll have to redefine the datatype of the field to Text or Number/Integer (despite the "numbers" it contains, it really should be Text) and develop a hack for generating an auto-incrementing ID "number."

    The behavior you describe makes me wonder if your are using Replication. As John mentions, this will change the New Values Property in the Table Design View from Incrementing to Random. Or perhaps this property as been inadvertently changed. You probably need to check this.
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  3. #3
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Compact and Repair?

    That is a weird one. I sure haven't seen it before.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  4. #4
    Join Date
    Jun 2005
    Location
    Richmond, Virginia USA
    Posts
    2,763
    Provided Answers: 19
    Thanks, Trekker! I meant to add the possibility of corruption at the end of my post, but got distracted by the election returns up here in the Northern Hemisphere!
    Hope this helps!

    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  5. #5
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Quote Originally Posted by StarTrekker
    Compact and Repair?

    That is a weird one. I sure haven't seen it before.
    Yes I have done this

    and yes its a weird one

    all 10 + year in msaccess nevery seen it before

    Will try change the Incrementing to Random HOPING THIS WILL WORK
    and yes I will take a backup of if LOL
    Last edited by myle; 11-05-08 at 02:22.
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    Good! Glad to see you're backing up!

    If Compact and Repair didn't stop this behaviour, then you're kinda left with only manual corrections...

    Try copying the database and import the table in again, first with structure only and then with the data. See if either helps.

    Try creating a new table with the identical structure of the existing table and import all the data from the existing table to the new one.

    Try exporting the data, deleting the table, recreating the table structure, and finally import the data.

    Those kinds of things.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  7. #7
    Join Date
    Feb 2004
    Location
    New Zealand
    Posts
    1,424
    Provided Answers: 8
    Thanks

    I set it to Random and that working so far.
    and keeping my eye it.

    StarTrekker

    It was broken on the Backend Data
    though about the exporting of the data and inporting
    only is problem is I Use the ID to link to other tables and
    I don't want to break the old links.

    Thanks to all for the help and ideas
    I still have my job for another day
    hope this help

    See clear as mud


    StePhan McKillen
    the aim is store once, not store multiple times
    Remember... Optimize 'til you die!
    Progaming environment:
    Access based on my own environment: DAO3.6/A97/A2000/A2003/A2007/A2010
    VB based on my own environment: vb6 sp5
    ASP based on my own environment: 5.6
    VB-NET based on my own environment started 2007
    SQL-2005 based on my own environment started 2008
    MYLE
    YOUR PASSWORD IS JUST LIKE YOUR TOOTHBRUSH DON'T SHARE IT.

  8. #8
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    though about the exporting of the data and inporting
    only is problem is I Use the ID to link to other tables and
    I don't want to break the old links.
    Interestingly, you should not have broken any links. Importing data with values for an autonumber field, access uses the values rather than the next autonumber.

    Also, I hope you haven't just delayed the issue... random MIGHT have fixed it, but I would not be confident of that. It's still a corrupted table in my book and if left unchecked, it could result in some serious concerns later.

    Besides, I hate the random autonumber thing. Your data is going to look funny to users especially -- IE

    1
    2
    3
    4
    5
    6
    7
    8
    -2394798734
    4989839829

    ... does not make sense!
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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