Solved

Renumbering an Autonumber field

Posted on 2002-05-08
7
687 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
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

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

Find Ransomware Secrets With All-Source Analysis

Ransomware has become a major concern for organizations; its prevalence has grown due to past successes achieved by threat actors. While each ransomware variant is different, we’ve seen some common tactics and trends used among the authors of the malware.

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

708 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now