Consecutive numbering

[Frankythekid]

Hello guys;

I'm a relative new user of Access 2000. I created a simple program that will help me organize my Purchase Orders. But the problem that I have is that in the field called [PO Number] i want it to display a new form.
I.E.
if the last PO I did was 44343, after i add this PO Number, the next form has to be 44344. I'm currently using this equation on the control source:

=Max([PO Number])+1

The issue with this is that the new number will show up, but it will not record it to the table.
How can I solve this problem, without using Autonumber, because if I choose Autonumber, lets say that the number is 10002, if I used 10002 and made a mistake, I cannot go back to using 10002, it will show 10003.

Thanks in advance.

 

[usermj]

May not the good solution, but I did similar thing before described as the following:

I still use the automatic number,

I use a variable to store the value (lastvalue+1) separately without creating new record into the table. After confirmation with other information, then I create the record and save them all into the table. So if any mistake, it will not affect the table original sequence at all.

 

[WayneRyan]

Franky,

Use the BeforeInsert event of the form, to put:

Me.[PO Number] = DMax("[PO Number]", "YourTable") + 1

Wayne

Hope you don't mind Wayne. At first I just removed the extra =. Then I realized you need a DMax() in this situation. I have no Idea if that is the correct syntax for the PO Number. I NEVER use spaces so I don't have to work out the syntax anomolies they cause.

 

[Frankythekid]

Tahnks For The Help Wayne It Worked Perfectly.

 

[RvVelzen]

Just a quick reply to acknowledge that WayneRyans 2004 solution has helped me tremendously, too.

I just wish I had implemented this earlier: I have already added hundreds of record id numbers manually. But the next hundreds will be a LOT easier!

 

[missinglinq]

Frankthekid, you need to get your terms straight or you're going to confuse people at times! You're not creating a new Form when you add another PO Number, you're creating a new Record. A Form contains many Records.

Also note: Placing this code in the BeforeInsert event is fine, if this is a single-user database, but can lead to problems if this is a multi-user database.

BeforeInsert fires the instant a single character is typed in a new record, even before the new record is actually created. If, for example, the last PO Number was 10002 and UserA started a new record, it would be assigned PO Number 10003. If, while UserA was still entering data on his PO, UserB started to enter a new Purchase Order, his would also be assigned PO Number 10003! Not good!

In a multi-user environment you need to place the code in the Form_BeforeUpdate event. This means that the PO Number will only be assigned at the last second, and greatly reduces the chance of a duplicate. Actually, in years of using this type code placed in the BeforeUpdate event, and in talking to other people who use it this way, I've never known of a duplicate being created.

Also note, an AutoNumber should never be used for this kind of thing! They're desogned for one purpose and one purpose only, to provide a unique identifier for records! They should never even be seen by the end users. When using Autonumbers, there will be gaps in the numbering - any record that's deleted will leave a gap; hitting <Esc> after starting a record will leave a gap; adding records using an Append query may leave a gap, often a huge one; replicating the database will make your invoice numbers random.