Auto Number

H

Hank

Is there a way to start an auto number (and primary key) at 1001. If not is
there a way I can use another field in a TABLE to ADD 1000 to the primary
key?
Thanks
 
S

Steve Huff

I couldn't remember off the top of my head how to do this but I found the
solution at:

http://www.tech-recipes.com/microsoft_office_tips518.html

here is what it says:

"Create your table with an AutoNumber type field, but don't enter any
records. Then create another table with only a single Long Integer Number
type field. This field must have same name as the AutoNumber field in the
first table. Enter one record in the second table that is a number one less
than the required start of the AutoNumber for the first table.

Now create an append query to append the record in the second table to the
first table and run the query. You can now delete the second table and begin
entering your data into the first table."

I tried this and it works.

Keep in mind though that a lot of people use AutoNumbers for the wrong
thing. If you delete a record you are going to loose that number unless you
go through the trouble of moving all the data to another table and
re-numbering again. "Generally speaking" autonumbers should be used as
Random Numbers when you don't care what the number is as long as it's
unique.

--Steve Huff
 
A

Alex Ivanov

Run an insert query that inserts a value one less than the beginning value
you want, for example
insert into table1(recid) values(1000)
then delete the newly inserted record.
If this does not work, delete all records from the table, compact the
database and try again or simply create a new table.
 
S

Steve Schapel

Hank

In order to use the method that Alex suggested, using the query design
interface, is...
1. Make a new query. Do not add any tables to the top panel of the
query design window.
2. Type 1000 into the Field row of the first column of the grid.
3. Make it an Append Query (select Append from the Query menu), and
nominate your table as the table to append to.
4. In the Append To row in the first column of the grid, enter your
Autonumber field.
5. Run the query (click the toolbar button with the red ! icon).
6. Close and don't save the query.
7. Open the table datasheet and delete the newlay appended record.
 
Top