Increment field by one without using Autonumber

[PBL]

Hi Guys,

Have read somewhere that the statement below will help to increment a numeric field by 1 without using Autonumber.

[Invoice Number] = Nz(Dmax("Invoice Number", "Invoice Transaction Header")) + 1

This was to be applied Before Update under the event of Invoice Number.

Would appreciate it if someone can help to offer a solution.

Thank you and Best regards
PBL

 

[pr2-eugin]

Hello PBL, Welcome to AWF..

Well you have got the idea right, but needs a bit more detail.. You need to set the field "InvoiceNumber" default to 0 in the Table design.. Then you have to use the code in the Form Current..

Private Sub From_Current()
    If Me.NewRecord Then
        Me.[InvoiceNumber]= Nz(Dmax("InvoiceNumber", "InvoiceTransactionHeader"), 0) + 1
    End If
End Sub

You might want to Check out DMax()+1 method by RainLover, for more information..

Also on a side note, you might want to rethink your Naming Conventions.. Having spaces in Table/Field names is not recommended..

 

[gemma-the-husky]

a slight disagreement/observation with the above.

if you get the next number at the START of editing a record, then two users doing the same action will both get the same record number - as they both read the same "current highest value". there is another issue expanded on below also.

in my opinion, the only place to assign the invoice number is immediately before the record is saved. Therefore the assignment ought to be placed in the form's before update event

an alternative approach to using dmax, especially useful for things like invoice numbers, is to store the next invoice number in a separate table - and read and incrmeent this when you need a new invoice number.

the other thing to be aware of, though, is that once you obtain an invoice number, then if you cancel the invoice, the invoice number may be "lost", and you may end up with gaps in the invoice sequence - which is another reason for obtaining the invoice number at the time the record is saved, rather than when it is first created.

 

[pr2-eugin]

if you get the next number at the START of editing a record, then two users doing the same action will both get the same record number - as they both read the same "current highest value".

Wow, that really is a good observation Dave.. Thank you, no wonder I have not got a problem with this so far.. right now only one of my team member inputs data.. Soon we are planning to let two other people input data... Just in time you have saved me.. I need to change the code, to revert it to the BeforeUpdate.. Since I do not use it for Invoice number, just a sequence, I think I might stick with this.. Thanks again Dave..

 

[gemma-the-husky]

I just tested the current event, actually, to make sure I was correct, and 2 users do get the same value.

There might be a problem if users need to know the invoice number allocated (in order to record the value, perhaps) so you may need to show the number allocated in a msgbox, or similar.

 

[pr2-eugin]

Yes, I just tested the same.. I have used a message box to display a message showing the New ID.. It works perfect.. Thanks Dave..