Auto Number PK, change starting number? (1 Viewer)

[Ms Kathy]

I have a table with an auto number PK. This table will contain orders. I'd like to use the PK from this table as the Invoice number on the invoice. I'd like to have it start at a number other than "1" just because it looks better on an invoice. I don't know how to do this. I looked at the table design to see if there were options available to me there but couldn't find anything. Is it possible? (I do not know how to use code.) Thank you!

 

[pr2-eugin]

AutoNumbers are not for human coinsumption. Refer to UtterAccess Wiki for more information : http://www.utteraccess.com/wiki/index.php/Autonumbers

If you want something like an incremental reference, you need to make use of a technique DMax + 1, by RainLover of this Forum.

 

[spikepl]

Autonumbers are not useful as invoice numbers. Accounting rules most often require sequential invoice numbers, but if you use autonumbers you'll invariably run into holes in the sequence, due to mistakes, cancellations etc and the impossibility of reusing an autonumber. Go with pr2 on this.

 

[Ms Kathy]

Thank you both. I will not use the PK Auto Number as an invoice number. I will now reevaulate the creation of invoice numbers. Thanks again.

 

[Pat Hartman]

The most flexible way to create sequential numbers is to use table that serves the next number. You could start the counter with some six digit number and increment from there. Use DAO to read the counter value. Add 1 to it and update it.

 

[Mihail]

The most flexible way to create sequential numbers is to use table that serves the next number. You could start the counter with some six digit number and increment from there. Use DAO to read the counter value. Add 1 to it and update it.

Hi Pat.
Just now I am facing to create sequential numbers.
My approach is (pseudocode)
NextNuber = DMax(ExistentNumbers)+1
Based on your skills I'm sure that you have a better method but I can't understand, from your post, "how to".
Are you so kind to explain in more details or (the best for me) to upload a simple example ?

Thank you !

 

[Pat Hartman]

The DMax() method is fine. An alternative is to create a table to record the latest value. You would then use DAO (or ADO if you prefer) to read the record and update it. It's a little more work but it is a little easier to reset the "next" value.

 

[Cronk]

To go one step further, in a busy multi-user environment, I've locked the lookup table with the next sequential number, to avoid any risk of "collision" with two users getting the same number.

If data entry of the new record is aborted, the lookup table can be checked that no other numbers have been issued, and if not, the next sequential number reset.