Results 1 to 5 of 5
  1. #1
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post

    New table begins with AutoNumber higher than 1 (Access 2007)

    It's been a couple of years since I built a database in Access, but I don't recall seeing this behavior. I create a new table and accept the default first field: "ID" (an AutoNumber field). When I start entering data, the first record displays with an ID greater than 1. The second table, for example, started with ID # 9. What's up?

  2. #2
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    This suggests that data have been entered in this table and then records deleted - perhaps in testing? The used and discarded autonumbers won't be re-issued. However, if you clear the table and compact the database, you'll find the autonumbers should reset to 1 in any empty tables.

    Cheers

    AliC

  3. #3
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Alternatively, you might have the New Values property for the AutoNumber field set to Random instead of Increment.

    Cheers

    AliC

  4. #4
    Bronze Lounger
    Join Date
    Jan 2001
    Location
    Virginia, USA
    Posts
    1,560
    Thanks
    37
    Thanked 1 Time in 1 Post
    No, the New Values property is Increment, and I had just created the table. No testing had occurred. Oh, well—it's a small project, and I'm just getting started. Thanks for the tip.

  5. #5
    2 Star Lounger
    Join Date
    Aug 2002
    Location
    Adelaide, South Australia, Australia
    Posts
    146
    Thanks
    5
    Thanked 5 Times in 5 Posts
    Did you try the compact anyway?

    I generally find I don't use autonumbers in a field that is going to be seen by users, because you always get the problem of missing numbers sooner or later. There are ways to give users neatly incrementing numbers if they need them. But I realise this thread might just be a case of wanting to know why it's happening! Anyway, by testing, I meant something as simple as typing one character into a field and then hitting Escape - as soon as the system thinks a record is being created, the autonumber is allocated, and even if the record isn't saved, the number is gone forever...

    Cheers

    AliC

Posting Permissions

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