Disable AutoNumber for just one entry....how?

[waldemar]

So I have this ID being an autonumber field going up to 25000 right now.
Now I need to add a table entry with a specific ID like 50000. How can I accomplish this? Once I convert the autonumber to a regular number and add the entry I am not allowed to turn it back to autonumber (but need that feature)...
Any ideas?

 

[waldemar]

From MSDN:
----------------------------------------------------------
ACC: Use Append Query to Set Initial Value of AutoNumber Field

Last reviewed: November 18, 1998
Article ID: Q94821

The information in this article applies to:

Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97


SUMMARY
Moderate: Requires basic macro, coding, and interoperability skills.

By using an append query, you can change the starting value of an AutoNumber field in a table (or Counter field in Microsoft Access 1.x and 2.0) to a number other than 1.

MORE INFORMATION
By design, Microsoft Access always numbers AutoNumber fields beginning with the number 1. You cannot edit an AutoNumber field or change its starting value.

However, you can force Microsoft Access to number an AutoNumber field with a number you choose by following these general steps:

Copy the design of the original table in which you want to set the starting AutoNumber value to a new table.
Change the AutoNumber field in the new table to a Number field with a FieldSize property of Long Integer.
Add a record to the new table, and set the Number field to a value that is one less than the starting number you want for your original table. For example, if you want the AutoNumber field to start at 100, type 99 in the Number field of the new table.
Use an append query to add this new record to your original table. This action forces Microsoft Access to number any new AutoNumber fields with your number plus 1.

NOTE: Do not compact the database before you add a new record to the original table. If you do, Microsoft Access will reset the AutoNumber field value to the number 1.
The following example uses the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0) to set a new starting value for an AutoNumber field in the Employees table:

 

[WildHare]

To do what YOU want, add the intervening records from 25000 to 49999. Then turn around and delete them. The next guy you add will be 50000.

Fun with Autonumbers...

Me? Ambivalent? Well, yes and no....
Another free Access forum:

http://www.forumco.com/jmhare

More Access stuff at

http://www.jmhare.com/wildhare2.htm

 

[waldemar]

Cool idea ;-)