Solved

Renumbering an Autonumber field

Posted on 2002-05-08
7
690 Views
Last Modified: 2011-09-20
Hi
I'm following the instructions in the help system in Access 97 to change the starting number of an autonumber field, and when I go to use the resulting table I find it has not worked at all. Does anyone know of any bugs in this procedure in Access 97?

The procedure is as follows:



"Change the starting value of an incrementing AutoNumber field

For a new table that contains no records, you can change the starting value of an AutoNumber field that has its NewValues property set to Increment to a number other than 1. For a table that contains records, you can also use this procedure to change the next value assigned in an AutoNumber field to a new number.

1     Create a temporary table with just one field, a Number field; set its FieldSize property to Long Integer and give it the same name as the AutoNumber field in the table whose value you want to change.

How?

2     In Datasheet view, enter a value in the Number field of the temporary table that is 1 less than the starting value you want for the AutoNumber field. For example, if you want the AutoNumber field to start at 100, enter 99 in the Number field.
3     Create and run an append query to append the temporary table to the table whose AutoNumber value you want to change.

How?

Note   If your original table has a primary key, you must temporarily remove the primary key before running the append query. Also, if your original table contains fields that have the Required property set to Yes, the Indexed property set to Yes (No Duplicates), or field and/or record ValidationRule property settings that prevent Null entries in fields, you must temporarily disable these settings.

4     Delete the temporary table.
5     Delete the record added by the append query.
6     If you had to disable property settings in step 3, return them to their original settings.

When you enter a record in the remaining table, Microsoft Access uses an AutoNumber field value 1 greater than the value you entered in the temporary table.

Note   If you want to compact the database after changing the starting AutoNumber value, make sure to add at least one record to the table first. If you don't, when you compact the database, the AutoNumber value for the next record added will be reset to 1 more than the highest previous value. For example, if there were no records in the table when you reset the starting value, compacting would set the AutoNumber value for the next record added to 1; if there were records in the table when you reset the starting value and the highest previous value was 50, compacting would set the AutoNumber value for the next record added to 51."


Any ideas?

Steve
0
Comment
Question by:SteveC777
  • 2
  • 2
  • 2
  • +1
7 Comments
 
LVL 54

Expert Comment

by:nico5038
ID: 6998237
Hi Steve,

Never use an autonumber to contain a "meaningfull" number.
If you need a "fixed" incremented number, just code it yourself.
Access 97 and 2000 handle autonumbers differently and in A2000 you can even have the numberingsystem "deadlocked" when inserting autonumber values....

I use myself a function to code numbers e.g. starting with a year. To do so just use the DMAX function to extract the highest number and add one for inserting a new row.

Getting an existing table renumbered is done by making it empty followed by a compact/repair of the database.
Then the increment will start from 1 again.

Nic;o)
0
 
LVL 57
ID: 6998654
Steve,

  The procedure works as stated.  There are no bugs in this area.

  Why are you trying to reset it?

Jim.
0
 

Author Comment

by:SteveC777
ID: 6999941
JDettman

Im trying to reset it to get rid of the numbers it counted when people were putting test records into the table.

Nico5038

Thanks, the field IS being used for meaningful data. I'll use a separate field for the autonumbering.


I still do need to know why the instructions in the help dont work. I'll uninstall and reinstall Access. Bye for now.
0
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:SteveC777
ID: 6999946
Im deleting this question because I think my problem is related to my PC and my installtion of Access.
Thank you.
0
 
LVL 57
ID: 7001253
I think you'll have the same problem when you re-install.  I've never seen anything that would not cause the Autonumber to reset.

Jim.
0
 
LVL 54

Expert Comment

by:nico5038
ID: 7220032

No comment has been added lately, so it's time to clean up this TA.
I will leave a recommendation in Community Support that this question is:
 - PAQ'd and pts removed
Please leave any comments here within the
next seven days.

PLEASE DO NOT ACCEPT THIS COMMENT AS AN ANSWER !

Nic;o)
0
 

Accepted Solution

by:
Jgould earned 0 total points
ID: 7240228
Question has been moved to PAQ and points refunded

think if the experts are correct the user has to find it out on its own so that's why i did the refund

JGould-EE Moderator
0

Featured Post

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question