How to re-number a counter after deletion?

[Guest]

hi,

I have a access table that has a counter field which has data type auto
number.
But in my datasheet view, say the below data:

Counter Product
01 AAA
02 BBB
03 CCC
04 DDD

Where i delete away the second record. It becomes:

Counter Product
01 AAA
03 CCC
04 DDD

but i want the counter to be re-numbered to :

Counter Product
01 AAA
02 CCC
03 DDD

Thanks a lot.

Tan

 

[Guest]

This is an age-old database issue. Auto-number fields do not reclaim empty
space upon record deletion. They are not there to provide a contiguous
sequence; they are just there to ensure that each field is unique. You can
create your own (additional) numbering field and force it to renumber all
greater numbers after each deletion, but consider this:

You now have 1,763,902 records and someone deletes record #2... Your code
now has to renumber everything from 3 - 1,763,902 back to 2 - 1,763,901.
Besides, what if PersonB has #3 open when PersonA deletes #2.

Also, if that number field is a foreign key in any relationships, you better
make sure you are enforcing referential integrity with cascade update.