Results 1 to 9 of 9
  1. #1
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Autonumber Problem (2002)

    I've copied a database I created, for use on another site. On a form, I've used Autonumber for the serial number. The original had reached 2126, and having deleted all the records, and useing an append query to reset the serial number to 1, the next autonumber is 2127. What am I doing wrong?
    I've checked all the fields in the table, to make sure they accep null values, so now I'm stuck for an answer.

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

    Re: Autonumber Problem (2002)

    To get all AutoNumber fields to "restart" at the lowest available number, select Tools | Database Utilities | Compact and Repair Database...
    You must be the only user in the database when you do this.

    It is advisable to do this periodically for all your databases. Apart from resetting the AutoNumbers, it removes all the unused empty space in the database and rebuilds the indexes on tables.

  3. #3
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber Problem (2002)

    I've just done that. Should I have cleared the table again before I compact and repair. It's still giving me the next high number.

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

    Re: Autonumber Problem (2002)

    Do you mean that your table now contains only one or two records and that a new record still gets number 2128 or something like that?

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

    Re: Autonumber Problem (2002)

    I found this in the Knowledge Base: AutoNumber Field Is Not Reset After You Compact a Database. It turns out that if you have Jet 4.0 Service Pack 4 or later, compact and repair doesn't reset AutoNumbers any more. The article describes some workarounds. A related article is Reset an AutoNumber Field Value in Access.

  6. #6
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber Problem (2002)

    I've just tried it again. Cleared the table. Compact and repairj, and it seems to have worked. Can't understand why it did'nt the first time.
    Just another thing on the subject. I've been asked to put letters before the number. The letters are going to be the same every time but I want them to show up as part of the number. Can I join them together in a report to show up like this "ABC00001","ABC00002" etc.

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

    Re: Autonumber Problem (2002)

    You can concatenate text and numbers in a query, or directly in a report.

    Say that your AutoNumber is named ID. Create a query based on the table, and add a calculated field (plus all the fields you need, of course):

    NewID: "ABC" & Format([ID], "00000")

    Use this query as record source of the report. If you'd rather avoid the query, you can put a text box on the report and set its Control Source to

    ="ABC" & Format([ID], "00000")

    Remark: don't name this text box ID, that will confuse Access.

  8. #8
    5 Star Lounger
    Join Date
    May 2003
    Location
    Sheffield, Yorkshire
    Posts
    951
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Autonumber Problem (2002)

    Thanks Hans. That's a great help.

    Regards,

    Rob

  9. #9
    New Lounger
    Join Date
    Jul 2003
    Location
    Manhattan, Kansas, USA
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Autonumber Problem (2002)

    (Edited by HansV to make links clickable - see <!help=19>Help 19<!/help>)

    I am glad I finally found the answer to this problem. I had been noticing this while working on my current project for a while now, and was wondering if my Access installation had gone bad. As it turns out, "it's not a bug, it's a feature." For the record, I'd like to state that this totally stinks. The suggested "workaround" are completely not helpful for a large app. Is there a good reason to not regress my Jet libraries to 4.0 SP3? After reading about this "fix", I am sorely tempted to. Excuse me, but I need to go be disgruntled for a while...

    Seth

    Update: OK, so I got so fed up, I wrote a function to reset the autonumber fields in all tables in a given database. I took the code from <!mskb=287756>Microsoft Knowledge Base Article 287756<!/mskb> and modified it heavily to suit my purposes. <shameless plug>You can find the result on my website, http://puma.agron.ksu.edu/~sgsax. Go to the Downloads area and look for ResetAllAutoNumbers.</shameless plug> The code is heavily commented and can be easily modified to suit your own purposes. Feel free to look at my other downloads. I have a few other items for Access and VB. available there. Share and enjoy!

    Seth

Posting Permissions

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