Make autonumber consecutive

[Islwyn]

I have an autonumber field in a table which is linked to fields in multiple other tables.

These existing numbers cannot be changed without having to change all the corresponding data in the other tables.

I have been testing the database by adding test records to check queries, new pieces of code etc. are working. However, this naturally creates new autonumbers, and after deletion of these test records the database 'remembers' the last false record and assigns new real records with a number which is not consecutive to the previous real record.

I had thought that compacting the database after testing and deleting the test records would reset the autonumber field so that new records would have consecutive numbers, but this does not happen.

Is there any way of resetting the seed for the autonumber to the last existing record after deletion of the test records (for example with code), without going through a long and protracted process of creating duplicate tables, breaking links between tables, writing queries to update all relevant fields in different tables etc. all of which run the risk of introducing errors in the data?

Thanks

 

[Galaxiom]

No.

However if the number matters to the user it should not be implimented as autonumber.

 

[John Big Booty]

As GalaxiomAtHome has alluded, the Auto Number is generally considered not fit for human consumption. If you require a consecutive number consider using the DMax() function plus one. Also search this forum on the subject of consecutive numbering.

 

[Islwyn]

I thought this might be the case but it was worth a try!

Thanks for your help

 

[gemma-the-husky]

if you do not want to use an autonumber because of this problem - the general alternative is to derive the vallue of the id field manually.

use dmax to find the highest number, and add 1 to it.

you will ALWAYS get problems if you delete within the table, as you will not be able to easily fill the gaps.