G
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
to start with 1?
Thanks for your help.
J
Klatuu said: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
kinlye said: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 said: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