Improve company productivity with a Business Account.Sign Up

x
?
Solved

Renumbering an Autonumber field

Posted on 2002-05-08
7
Medium Priority
?
714 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 59
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 

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 59
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Sometimes MS breaks things just for fun... In Access 2003, only the maximum allowable SQL string length could cause problems as you built a recordset. Now, when using string data in a WHERE clause, the 'identifier' maximum is 128 characters. So, …
A Case Study of using the Windows API to provide RS232 communications capability in Access without the use of Active-X controls.
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

584 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