?
Solved

Renumbering an Autonumber field

Posted on 2002-05-08
7
Medium Priority
?
700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 58
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
Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

 

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

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

It’s been over a month into 2017, and there is already a sophisticated Gmail phishing email making it rounds. New techniques and tactics, have given hackers a way to authentically impersonate your contacts.How it Works The attack works by targeti…
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
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.
Suggested Courses

770 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