Wrox Programmer Forums
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old September 5th, 2006, 04:00 PM
Registered User
Join Date: Sep 2006
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
Default autonumber problem in Access

Have Access 2000. Have a table with 22 records. First field is autonumber and I've made it a Key Field. After record 19 I deleted a few records but was surprised the next autonumber came up as 22. But I wanted it to be 20. So I deleted that record. Next autonumber comes up as 23. So I search in Help, but can find nothing on re-ordering all the autonumber fields so they will read 19,20,21,22. How do I fix my problem. Thank You.
Sincerely, Pat Crotty

Old September 5th, 2006, 04:04 PM
Friend of Wrox
Join Date: Oct 2004
Posts: 564
Thanks: 0
Thanked 4 Times in 4 Posts

There isn't a straight forward way of doing this, but the following should work for you...

1. Open the table in design view
2. Delete the Autonumber field
3. Save the table
4. You may need to exit and restart Access at this point
5. Open the table in design view
6. Add the field, set it to Autonumber
7. Close and Save and you should be good.

Hope that helps,


Old September 6th, 2006, 10:37 AM
Friend of Wrox
Join Date: Mar 2004
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts

This will resolve your immediate problem, but it will keep happening to you every time you delete a record. The autonumber field keeps track of each number it uses in increments of 1 generally, and will not reuse a number it has used already, regardless of whether the record has been deleted or not. It would be programatically difficult to renumber all of your PK's everytime you deleted a record, although referential integrity could be maintained (cascaded updates).

If you need to use the autonumber field to tell you how many records are in your table, you should consider a query to do this for you. i use a query to tell me the number of active records in some of my databases (I don't delete records usually, just select a "retired" check box.)

Other than personal issues, however, I can't think of a reason to change the non-meaningful PK every time a record is removed (yes, non-meaningful as opposed to meaningless.) Is there a compelling design reason for this? Perhaps there is another solution.


Similar Threads
Thread Thread Starter Forum Replies Last Post
AutoNumber in MS Access iii_carlos BOOK: Beginning VB.NET Databases 6 January 31st, 2007 08:23 PM
Access Autonumber chall90909 Access ASP 1 November 21st, 2004 10:26 AM
Autonumber on Access 2002 pdunning Access 1 August 23rd, 2004 10:33 AM
ASP and Access AutoNumber phungleon Access ASP 2 October 10th, 2003 03:59 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.