Auto number - filling in the gaps

[freda42]

I have an autonumber field set up to provide unique reference numbers for each record. If a record is deleted for whatever reason, the number goes with it. Is there any way of getting the next new record to be one of the missing numbers, or is that just complicating matters?

Thanks!

 

[The_Doc_Man]

Pat and I agree on this, mostly.

If you want the autonumbers to be compacted or re-used, you are assigning a value or meaning to the number than an autonumber isn't meant to have. Actually, there is a value to an autonumber not being contiguous. When you find a gap, you don't know whether an INSERT failed or was cancelled, but you know one was tried. If you run across a sequence of a lot of gaps, you have perhaps identified trouble among your user community.