Beginning number of auto number field

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?
--
Best Regards

"Mark" wrote in message
...
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

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
he http://www.rogersaccesslibrary.com/f...ts.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/...UBED1=ACCESS-L


"Keith Wilby" wrote in message
...
"Mark" wrote in message
...
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

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

"Mark" wrote:

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?
--
Best Regards

"Clifford Bass" wrote in message
...
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.

"Roger Carlson" wrote in message
...
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
he http://www.rogersaccesslibrary.com/f...ts.asp?TID=395


Very nice site Roger :-)