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

[Charles McCaffery]

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.

[Scott McDaniel]

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.

[Charles McCaffery]

Many thanks. I will try that.

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

[Prakash Wadhwani]

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.

[Wayne Gillespie]

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

[Prakash Wadhwani]

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