How can I keep the AutoNumber from skipping numbers?

[Kevin]

Ok, I'll have one column on AutoNumber, and so it goes 1, 2, 3, etc..

But when I delete a row, it is as if that AutoNumber is gone for good.

Like if I have auto numbers from rows 1-125, if I delete row 125, then 125
seems to be gone, and so I have 123, 124, 126, 127,..

So how can I get that number back so numbers aren't skipped?

 

[aaron.reese]

Yes, it will, that is the whole point of autonumbers. NEVER use the
autonumber as a key on another table because if you have to rebuild the
table you will be well and truly stuffed.

Build a table for counters and write a function that returns the next
number from the counter and a procedure to update the counter.

Obiron

 

[Roger Carlson]

You can't. Autonumbers are designed that way. If you must have a
sequential number, you'll have to code it yourself. On my website
(www.rogersaccesslibrary.com), is a small Access database sample called
"AutonumberProblem.mdb" which illustrates how to do this.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

 

[Rick Brandt]

Yes, it will, that is the whole point of autonumbers. NEVER use the
autonumber as a key on another table because if you have to rebuild
the table you will be well and truly stuffed.

I don't understand this comment. People use AutoNumbers to relate tables
all the time. That is what they are best at. What they are not good at is
producing unbroken seqences, but that is not an issue when relating tables
to each other.

What do you mean by "rebuilding a table" that would cause a problem with
AutoNumbers used to relate tables?