Reset Auto Number

[aussie]

Hi,
I have set up a database and tested the forms etc to make sure they are working.

When I did this, the Invoice Number (Auto Numbers - Indexed) went up to 6, because I entered 6 invoices.

How do I reset the AutoNumber back to 0:banghead:

 

[jdraw]

Autonumbers are meant to be unique. Autonumbers are not necessarily sequential. Autonumbers are meant for system use.

Why does it matter what the value is?

More info at
http://allenbrowne.com/ser-40.html
http://allenbrowne.com/ser-26.html

Good luck with your project

 

[RainLover]

Delete it and save, then add it back in.

AutoNumber is not good for Invoice Numbers.There is no guarantee that it will be sequential.

Try Using DMax plus 1

 

[aussie]

Hi Rain,
Can you describe what you mean by DMax plus 1?
Thanks

 

[RainLover]

Search Access Help for "DMax"

It finds the highest value.

You can then add to the result, so if DMax = 15 plus 1 = 16 etc.

You can also search for the Max Number of a Type. If you had

Cat 12
Cat 13
Dog 89
Dog 90

DMax would give for Cat 14 and Dog 91

Attached is a sample.

 

[ypma]

There is another way and that is to created a Data Definiton query . example :-
Alter Table tablename Alter Column RecordId Counter (1,1)

 

[spikepl]

@ypma

There is a good reason why Rain advised OP not to use Autonumber for Invoice Number. One can reset an Autonumber by numerous means, but that is not what OP should do here.

 

[ypma]

There is another way and that is to created a Data Definiton query . example :-
Alter Table tablename Alter Column RecordId Counter (1,1)

 

[ypma]

@Spikepl. I Accept you valid comments however, Aussie asked how he could reset the counter to zero and surly if that what he requested my reply gave him an extra option to consider, and in no way was it intended to counter or undermine the professional advise given..

 

[keyel1971]

To reset Autonumber, you need to delete the current records from the table, and then compact and repair the database.

 

[jdraw]

to reset autonumber see
http://www.accessforums.net/access/auto-number-13-numbers-25062.html

 

[gemma-the-husky]

after all of this - if you are trying to use autonumbers to maintain an intact sequence - you cannot.

you need a different mechanism to using autonumbers.

 

[RainLover]

@Spikepl. I Accept you valid comments however, Aussie asked how he could reset the counter to zero and surly if that what he requested my reply gave him an extra option to consider, and in no way was it intended to counter or undermine the professional advise given..

Do we or do we not answer the question as asked.

That is the question.

I personally like to set a person in the right direction.

I do get annoyed when a person answers a question that will lead to further problems.

AWF is for learning. So sometimes we have to train the trainer and not offend.

 

[spikepl]

Many questions asked here are asked by not very experienced users. Some questions are Access-equivalents to asking instructions for how to remove the protective cover to allow the user to plug the fingers into the wall outlet.

In such instances I believe that it is better to provide an alternative solution rather than a step-by-step instruction for how to do that.

 

[RainLover]

I hate it when I have to teach someone to use a Mouse.

Worse still is when they don't know a Mouse exists.

 

[krazykaigh]

@RainLover

Search Access Help for "DMax"

It finds the highest value.

You can then add to the result, so if DMax = 15 plus 1 = 16 etc.

You can also search for the Max Number of a Type. If you had

Cat 12
Cat 13
Dog 89
Dog 90

DMax would give for Cat 14 and Dog 91

Attached is a sample.

Based on this information, if Cat 13 was deleted and DMax was applied would it come back with Cat 13, again?

 

[RainLover]

Based on this information, if Cat 13 was deleted and DMax was applied would it come back with Cat 13, again?

Yes it would.

But if 12 was deleted it would leave a Gap.