How do I reuse an autonumber

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I am doing a form with a Purchase Order Number. That number is an
autonumber. The problem I am having is that it does not go back to a number
if the record is deleted or not used. Is this possible and if so how do I
get it to do that.

Thanks,
Annemarie
 
Autonumber should never be used for a sequence number such as would be used
on a purchase order or invoice. Autonumber if for the database's purposes
only. If you delete a record, or if you go to a new record and don't use it,
the autonumber is discarded and the next one is used for a new record.
That's the way autonumber works, and it can't be changed. Actually, I think
it is possible to reassign the numbers in some cases, but the process is
difficult and complex, and not to be used day-to-day but rather as a one-time
repair.
However, it is possible to construct an incrementing number such as 05-001,
05-002, etc. (or whatever you need). If you post the number sequence you
need then somebody here will surely have a way of achieving it.
 
Annemarie,

Well, it is possible. But really, if you care what the number is, an
Autonumber data type is not a good choice. It is better to change it to
a Number data type, and use code, or the Default Value property, to
assign the next sequential number to new Purchase Orders. I assume you
do not mean that you want to re-use a Purchase Order Number if a
previous purchase order is cancelled or deleted or whatever? I assume
you are just talking about a situation where the entry of a new Purchase
Order is aborted, resulting in the Autonumber being "used up"?
 
Bruce:
Thanks for the insight. I am trying to do a sequential number starting with
the number 4535 and adding one every time I put in a new PO. The problem is
that I also need it to be able to go back and use a number that was deleted
or not used. Any help is greatly appreciated. I am not very familiar with
MS Access Forms.
 
Steve:
Thank you so much. I will be sure to change that. I appreciate all of
your help.
 
Annemarie,

The easiest way for the next sequential number, is to go to the Default
Value property of the purchase order number textbox on the form design,
and set it to the equivalent of...
DMax("[PO Number]","NameOfYourTable")+1

This will also allow you to manually change a newly entered PO Number to
the value of a previously deleted or unused PO Number. So you should be
able to do whatever you need. However, I must say that re-using numbers
like this is an "unusual" business practice.
 
Back
Top