Why do I loose ID# in my Access database?

[Guest]

I have a large (over 5000) Access database of clients, and frequently send
newsletters via Word mailmerge. I also run a linked access table which I use
to print filtered groups of clients.
When I remove clients on their request, I simply clear all the fields of
their record and use it for my next new client entry, therefor reusing the
old ID number.
My goal is to have the ID number equal its record number.

However I have already two ID numbers missing, and don't know why!

My highest ID number is now 2 higher than my highest record number.

How did I cause the AUTONUMBER to skip a number? Or do Access or Word ever
delete a record if it doesn't have any entry in its row - perhaps at time of
mailmerge or when filtering in the linked copy?

Can I somehow undo this and enter new records with the skipped ID numbers?
(sofar AUTONUMBER didn't allow me to do so)

Sue

 

[Rick Brandt]

I have a large (over 5000) Access database of clients, and frequently
send newsletters via Word mailmerge. I also run a linked access table
which I use to print filtered groups of clients.
When I remove clients on their request, I simply clear all the fields
of their record and use it for my next new client entry, therefor
reusing the old ID number.
My goal is to have the ID number equal its record number.

However I have already two ID numbers missing, and don't know why!

My highest ID number is now 2 higher than my highest record number.

How did I cause the AUTONUMBER to skip a number? Or do Access or Word
ever delete a record if it doesn't have any entry in its row -
perhaps at time of mailmerge or when filtering in the linked copy?

Can I somehow undo this and enter new records with the skipped ID
numbers? (sofar AUTONUMBER didn't allow me to do so)

Sue

Any time you care about the value in any way other than uniqueness then DON'T
use an AutoNumber. They are not designed to produce a gapless sequence. They
don't even try to do that.

 

[John W. Vinson]

I have a large (over 5000) Access database of clients, and frequently send
newsletters via Word mailmerge. I also run a linked access table which I use
to print filtered groups of clients.
When I remove clients on their request, I simply clear all the fields of
their record and use it for my next new client entry, therefor reusing the
old ID number.
My goal is to have the ID number equal its record number.

However I have already two ID numbers missing, and don't know why!

My highest ID number is now 2 higher than my highest record number.

How did I cause the AUTONUMBER to skip a number? Or do Access or Word ever
delete a record if it doesn't have any entry in its row - perhaps at time of
mailmerge or when filtering in the linked copy?

Can I somehow undo this and enter new records with the skipped ID numbers?
(sofar AUTONUMBER didn't allow me to do so)

Sue

An Autonumber has one purpose, and one purpose only: to provide a unique
meaningless key.

It is NOT guaranteed to be sequential or gapless. If you start to enter a new
record and hit the <Esc> key one tenth of a second later, an autonumber gets
assigned, used up, and deleted, leaving a gap when you later enter a record.

If you want a sequential, gapless number, you must use a Number (integer or
Long Integer) field, and assign its value yourself (probably using VBA code).
If you want to allow records to be deleted you'll need some (possibly a bit
complex) code to ensure that the gaps get filled.

My question is: *what's the point*?? If you want to count clients... use a
Totals query and count clients. If you want to sort clients (alphabetically,
or by date acquired, or by something else) use a Query and sort the clients.
It surely isn't important *to a client* that they are #1572 or #5031; I don't
see why it's important to you either, especially if you're reusing numbers as
clients leave!

John W. Vinson [MVP]