Beginning number of auto number field

[Mark]

Working with Access 2007.

I have a new database and would like to know how to begin my auto number
field (which is also my record identifier) at a number other than one. I have
been tracking my numbers in another manner which have gotten up to 17,500
records and would like to begin this database number where the other left off.

Any thoughts?

 

[Keith Wilby]

Working with Access 2007.

I have a new database and would like to know how to begin my auto number
field (which is also my record identifier) at a number other than one. I
have
been tracking my numbers in another manner which have gotten up to 17,500
records and would like to begin this database number where the other left
off.

Any thoughts?

An AutoNumber field is for internal indexing purposes and it's value to the
user is irrelevant. If you want sequential numbering then you'd have to
roll your own. A popular method is using the DMax function in the control's
default value:

nz(DMax([MyField])+1,1)

or even

nz(DMax([MyField])+1,17501)

That's from memory so the sytax might be a bit skewiff.

Keith.
www.keithwilby.co.uk

 

[Roger Carlson]

Just to piggyback off of Keith's post, on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"AutonumberProblem.mdb" which illustrates how to do this. You can find it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Working with Access 2007.

I have a new database and would like to know how to begin my auto number
field (which is also my record identifier) at a number other than one. I
have
been tracking my numbers in another manner which have gotten up to 17,500
records and would like to begin this database number where the other left
off.

Any thoughts?

An AutoNumber field is for internal indexing purposes and it's value to
the user is irrelevant. If you want sequential numbering then you'd have
to roll your own. A popular method is using the DMax function in the
control's default value:

nz(DMax([MyField])+1,1)

or even

nz(DMax([MyField])+1,17501)

That's from memory so the sytax might be a bit skewiff.

Keith.
www.keithwilby.co.uk

 

[Clifford Bass]

Hi Mark,

Sure, just execute an append query that adds a row with the number
desired less one. Then delete the row.

INSERT INTO tblTableWithAutonumberField (RecordID, OtherField)
SELECT 17500 AS Expr1, "x" AS Expr2;

Where RecordID is the autonumber field and OtherField is a required
field.

Clifford Bass

 

[Keith Wilby]

Hi Mark,

Sure, just execute an append query that adds a row with the number
desired less one. Then delete the row.

INSERT INTO tblTableWithAutonumberField (RecordID, OtherField)
SELECT 17500 AS Expr1, "x" AS Expr2;

Where RecordID is the autonumber field and OtherField is a required
field.

But please note that you can't rely on AutoNumber to be sequential.

Keith.

 

[Keith Wilby]

Just to piggyback off of Keith's post, on my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"AutonumberProblem.mdb" which illustrates how to do this. You can find it
here: http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=395

Very nice site Roger