Auto number

[Guest]

Can I set the autonumber to start at a certain number or does it always have
to start with 1?
Thanks for your help.
J

 

[Guest]

No, you can't. You should not be using an Autonumber field like this.
Autonumber fields are useful only for PK/FK relationships and should never be
used as part of your data. They will never always be sequential. Gaps are
created any time you go to a new record, then undo or if you delete a record.

If you need a sequential numbering for your records, you should create a
different fields with the data type Long Integer. Then you can use the code
below to create the next sequential number when you add a new record:

MyNextNumber = Nz(DMax("[MySequentialField]","MyTableName"),0) + 1

 

[Steve Schapel]

Kinlye,

You can use an Append Query to put a record into the table, with a
number 1 less than your desired starting number in the Autonumber field.

For example, to start your Autonumber at 1000, open a blank query in
design view, and enter 999 in the Field row of the first column of the
query design grid. Make it an Append Query (select Append from the
Query menu), and nominate your table. In the Append To row of the grid,
enter the name of the Autonumber field. The SQL of the query will look
something like this....
INSERT INTO [YourTable] ( YourAutoNumberField )
SELECT 999 AS Expr1;

Run the query (click the toolbar button with the red [!] icon). Close
the query, open the table, and delete this record just added. After
that, the next record added will have 1000 in the autonumber field.

 

[Douglas J Steele]

Come now. There are lots of legitimate reasons to want an AutoNumber to
start at a particular value. For instance, you may have legacy pre-numbered
data that you need to use, but use an AutoNumber from now on.

Allen Browne shows one approach to setting the start value at
http://www.allenbrowne.com/ser-26.html

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

No, you can't. You should not be using an Autonumber field like this.
Autonumber fields are useful only for PK/FK relationships and should never be
used as part of your data. They will never always be sequential. Gaps are
created any time you go to a new record, then undo or if you delete a record.

If you need a sequential numbering for your records, you should create a
different fields with the data type Long Integer. Then you can use the code
below to create the next sequential number when you add a new record:

MyNextNumber = Nz(DMax("[MySequentialField]","MyTableName"),0) + 1

Can I set the autonumber to start at a certain number or does it always have
to start with 1?
Thanks for your help.
J

 

[Guest]

I know it can be done, but I always tell people it can't for a reason. It is
a really bad idea and misue of AutoNumber.

Kinlye,

You can use an Append Query to put a record into the table, with a
number 1 less than your desired starting number in the Autonumber field.

For example, to start your Autonumber at 1000, open a blank query in
design view, and enter 999 in the Field row of the first column of the
query design grid. Make it an Append Query (select Append from the
Query menu), and nominate your table. In the Append To row of the grid,
enter the name of the Autonumber field. The SQL of the query will look
something like this....
INSERT INTO [YourTable] ( YourAutoNumberField )
SELECT 999 AS Expr1;

Run the query (click the toolbar button with the red [!] icon). Close
the query, open the table, and delete this record just added. After
that, the next record added will have 1000 in the autonumber field.

--
Steve Schapel, Microsoft Access MVP

Can I set the autonumber to start at a certain number or does it always have
to start with 1?
Thanks for your help.
J

 

[Steve Schapel]

Sorry, Klatuu, we'll have to agree to disagree on that one.