By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,483 Members | 3,229 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,483 IT Pros & Developers. It's quick & easy.

HOW DO I RESENT AUTO-NUMBERING TO 1 (ONE)?

P: n/a
I have written a database with auto-numbering and now wish to remove alkl of
my test data and set the auto-numbering back to one. How do I do this
please?

Charles McCaffery.
Nov 12 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
Delete the data, the Compact the database.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Charles McCaffery" <ch**************@tiscali.co.uk> wrote in message
news:3f**********@mk-nntp-2.news.uk.tiscali.com...
I have written a database with auto-numbering and now wish to remove alkl of my test data and set the auto-numbering back to one. How do I do this
please?

Charles McCaffery.

Nov 12 '05 #2

P: n/a


Many thanks. I will try that.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Nov 12 '05 #3

P: n/a
wat if i'd like the autonumbering to start (begin) from a a particular
number. Is there any way to do it ?

Best Rgds,
Prakash


"Scott McDaniel" <scott@NOSPAM__thedatabaseplace.net> wrote in message news:<7P********************@comcast.com>...
Delete the data, the Compact the database.

--
Scott McDaniel
CS Computer Software
www.thedatabaseplace.net

"Charles McCaffery" <ch**************@tiscali.co.uk> wrote in message
news:3f**********@mk-nntp-2.news.uk.tiscali.com...
I have written a database with auto-numbering and now wish to remove alkl

of
my test data and set the auto-numbering back to one. How do I do this
please?

Charles McCaffery.

Nov 12 '05 #4

P: n/a
On 13 Oct 2003 13:06:51 -0700, si****@omantel.net.om (Prakash Wadhwani) wrote:
wat if i'd like the autonumbering to start (begin) from a a particular
number. Is there any way to do it ?

Best Rgds,
Prakash


Clear the data of any existing records.
Append a single record to the table with the a value one less than your start number. You must use an append query to do this.
Compact the database.
Delete the temporary record.
The first record entered will have an autonumber value equal to your start value.

That said it is very bad practice to use an autonumber to control sequential numbering as you will quickly develope gaps in the numbering sequence.
eg If a use starts a new record but presses escape and does not the complete the record, the autonumber assigned to the new record will be discarded
and NOTreused. Therefore the next new record created will NOT be in sequence.

A far better solution is to use a Long Integer field and use Nz(DMax("MyID","tblMyTable"))+1 to determine the next available sequential number.
If you want to set a specific starting number using this method, set the last argument of the Nz function to one less than your starting number. eg to
start at 1000 use Nz(DMax("MyID","tblMyTable"),999)+1
If the new record is the first record entered in the table, 1000 will be assigned, From then on the value will increment by 1.
Wayne Gillespie
Gosford NSW Australia
Nov 12 '05 #5

P: n/a
I've never used auto-numbering before but was planning on using it in
one of my teeny-weeny apps next month. Thx for the warning ... I'll
certainly use your DMax alternative. I also prefer having FULL control
on the incremental processs.

Thx & Best Rgds
Prakash.

Wayne Gillespie <be*****@NObestfitsoftwareSPAM.com.au> wrote in message news:<uu********************************@4ax.com>. ..
On 13 Oct 2003 13:06:51 -0700, si****@omantel.net.om (Prakash Wadhwani) wrote:
wat if i'd like the autonumbering to start (begin) from a a particular
number. Is there any way to do it ?

Best Rgds,
Prakash


Clear the data of any existing records.
Append a single record to the table with the a value one less than your start number. You must use an append query to do this.
Compact the database.
Delete the temporary record.
The first record entered will have an autonumber value equal to your start value.

That said it is very bad practice to use an autonumber to control sequential numbering as you will quickly develope gaps in the numbering sequence.
eg If a use starts a new record but presses escape and does not the complete the record, the autonumber assigned to the new record will be discarded
and NOTreused. Therefore the next new record created will NOT be in sequence.

A far better solution is to use a Long Integer field and use Nz(DMax("MyID","tblMyTable"))+1 to determine the next available sequential number.
If you want to set a specific starting number using this method, set the last argument of the Nz function to one less than your starting number. eg to
start at 1000 use Nz(DMax("MyID","tblMyTable"),999)+1
If the new record is the first record entered in the table, 1000 will be assigned, From then on the value will increment by 1.
Wayne Gillespie
Gosford NSW Australia

Nov 12 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.