Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2009
    Posts
    95

    Unanswered: AutoNumber Problem

    I have a subform used to enter new information. The first field is an autonumber. If I create a new record, it assigns the autonumber, but I was testing to make sure it works and it did so I deleted the record. However, the next time I tested the autonumber it skipped the number that I just deleted and went to the next number.

    Why did it skip the next number when I deleted the record?

    Thanks,
    CT

  2. #2
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    That's the way autonumber works.

    Autonumber generates a unique, sequential number when you create a record. They do not revert, they continue on to the next sequential number even if you delete autonumber 6, the next autonumber will still be 7.

    For instance if you input 300 records, and then delete them all, when you input a new record the first autonumber will still be 301, instead of 1.

    Hope that helps!

  3. #3
    Join Date
    Jun 2009
    Posts
    95
    Ok, I only wanted to fix this to appease my OCD, but I guess that isn't possible as it isn't a necessary thing to fix.

    Thanks,
    CT

  4. #4
    Join Date
    May 2009
    Location
    Ottawa, Canada
    Posts
    11
    It is still viable to reset the autonumbers if you are that OCD about it. Solution is must be found HERE:

    How to reset an AutoNumber field value in Access

  5. #5
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    It's just the way AutoNumber works. It's not a problem in 99.99999999% of cases, but if it is, then you have to code the functionality of the autonumber and make the field Number(Long Integer).
    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

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    or to phrase it another way
    autonumber is a bit of code that guarantees to make something unique. its for internal (system) use only, any column set to autonumber MUST not be relied on to have any meaning outside the system. so its not to be relied on for things like invoices, GRN's and so on if there must not be any gaps, and believe me accountants/auditors/taxmen will not like the idea of gaps (becuase they will believe that there a invoices out there). you can get round this limitations of Accountants views of the world by never deleting an autonumber recordbut marking that record as spolied/invalid. that way round you can use autonumber, Accountants can sleep happily in their beds at night.. on seconds thoughts delete lots of records, why should Auditors and taxmen be allowed to sleep happily...

    if you must have contiguous number ranges then you must create your own numbering system. this is not difficult but it does require a bit of coding
    Last edited by healdem; 07-15-09 at 14:45.
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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