Results 1 to 8 of 8
  1. #1
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    problem with repeating autonumbers (Access 97)

    Hello, I built a simple database for a friends dog boutique so she could keep track of her customers.

    She told me that when she goes to add a new contact that she receives a message that the record cannot be added because it duplicates an existing index. Looking at her customer table I noticed that when a new record is added the autonumber that is being inserted is a number that has already been used. For instance there are 436 records in the table, and the new record I tried to add had an autonumber of 18. The field is set to increment.

    Any ideas on how to fix this (and keep it from happening again) would be appreciated. Thanks

  2. #2
    5 Star Lounger
    Join Date
    Jan 2001
    Location
    Vancouver, Br. Columbia, Canada
    Posts
    632
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: problem with repeating autonumbers (Access 97)

    Try compacting the database. That should reset the Autonumber.
    --------------------------------------------------
    Jack MacDonald
    Vancouver, Canada

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: problem with repeating autonumbers (Access 97)

    This is a known and recurring problem. The cause is often doing an append query in which the autonumber field is specified, rather than allowing Access to assign it. Make sure you have all updates to Access and Jet.

    To fix this, you will need to go to my website below, on which I have a utility you can download. The easiest one to use is the DAO one.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: problem with repeating autonumbers (Access 97)

    Does your utility work in Access 97? (See subject line)

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: problem with repeating autonumbers (Access 97)

    The db on my website is an A2000 database. However, the technique will work regardless, you'd just need to convert it back.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: problem with repeating autonumbers (Access 97)

    I just updated my website with an Access97 version. Interesting, though, as I thought this was only an Access2000 problem?
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: problem with repeating autonumbers (Access 97)

    Thanks for that!

    The problem with AutoNumbers is a known and documented Access 2000 problem, this is the first time I've seen it mentioned for Access 97.

  8. #8
    Lounger
    Join Date
    Jan 2001
    Location
    Denver, Colorado, USA
    Posts
    39
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: problem with repeating autonumbers (Access 97)

    Thanks, your tool solved the problem. It was my mistake but this was for an Access 2000 issue. Sorry for misleading everyone. Take care

Posting Permissions

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