Solved

Renumbering an Autonumber field

Posted on 2002-05-08
7
688 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
MS Access Append Query From CSV File Into Multiple Tables 26 49
Help writing a query 6 72
ms/access hyperlink/ftp 7 36
data analyst 3 49
When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server stored procedures 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 Micr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

910 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

22 Experts available now in Live!

Get 1:1 Help Now