If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Beginning number of auto number field
"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 |
#3
|
|||
|
|||
Beginning number of auto number field
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 |
#4
|
|||
|
|||
Beginning number of auto number field
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 |
#5
|
|||
|
|||
Beginning number of auto number field
"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. |
#6
|
|||
|
|||
Beginning number of auto number field
"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 :-) |
Thread Tools | |
Display Modes | |
|
|