Access2000 AutoNumber questions?

[Guest]

I have one Purchase Order tables and the primary key is autonumber as
PurchaseOrderID. And I found that if one record in that table is deleted(for
example, PurchaseOrderID=2000), then the next assigned autonumber won't pick
up the deleted number(2000) and it will use 2001, which will make my PO ID
inconsistent(I mean PO number is not continuous by +1).

I already tried "compacting database" but it does not work. Is that the way
autonumber works? Any solution or comments?

Thanks in advance!

 

[Jeff Conrad]

Yes, that is the way AutoNumber works.
It is not meant for human consumption so to speak.
This violates Rule #7 of the Access Ten Commandments:

http://www.mvps.org/access/tencommandments.htm
http://members.rogers.com/douglas.j.steele/AccessTenCommandments.html

An AutoNumber field has one, and only one, purpose:
"To uniquely identify a record"
That's it.

AutoNumber will have gaps with record deletions (as you already
noticed), can become a random number, and even go negative!
How would your Accounting people like to see a negative Purchase
Order Number? Not good.

If you care at all (even a little bit) what the actual value of
the AutoNumber field is, then it is being used incorrectly.
You will need to develop a different method to come up
with a Purchase Order Number that is sequential.

 

[Lynn Trapp]

Jessica,
In addition to Jeff's excellent comments, I would ask you, "Why do your
Purchase Order numbers NEED to be sequential?" A Purchase Order is merely a
reference number for a supplier to use when invoicing for something ordered
from them. As long as it is a unique value in the system it should suffice.

 

[Guest]

hi,
I hate access autonumber. too squirrelly.
there are 2 way to set up your own autonumber
write a query which select the max of the PO number + 1.
use this as the PO numbers text box control record source
the other is to create your own "autonumber" table.
both ways can be set up so that numbers are not skipped if
you decide not to complete the PO id delete or cancel.
of cource deleteing previous PO would present a problem
which is why at here, deleting is forbidden. worst than a
four letter word. instead we set the complete flag to true.
I have used both from time to time. I NEVER use access
autonumber.