Invoice number

[Sparky]

I want to automatically enter an invoice number into an invoiceref
field when I enter the invoice date into invoicedate field. My problem
is how do I take the previous invoice number and increment by 1?

Thanks.

 

[Allen Browne]

Use the BeforeUpdate event procedure of your form to lookup the maximum
number used so far in the table, and add one.

This event is the last possible moment before the record is saved. Leaving
it to the last moment reduces the chance that 2 users will be given the same
number.

Example, assuming a field named InvoiceNum, in a table named Table1:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[InvoiceNum]) Then
Me.[InvoiceNum] = Nz(DMax("InvoiceNum", "Table1"),0) + 1
End If
End Sub

 

[Brian Bastl]

Sparky,

Nz(DMax("YourInvoiceNumField","YourInvoicesTable"),0) + 1

Also, you might consider using it instead in the form's BeforeUpdate event
procedure, especially if you are in a multi-user environment.

Brian

 

[Brian Bastl]

Sorry Allen,

Your post didn't show up in my reader until after I sent my own post.

Brian

Use the BeforeUpdate event procedure of your form to lookup the maximum
number used so far in the table, and add one.

This event is the last possible moment before the record is saved. Leaving
it to the last moment reduces the chance that 2 users will be given the same
number.

Example, assuming a field named InvoiceNum, in a table named Table1:

Private Sub Form_BeforeUpdate(Cancel As Integer)
If IsNull(Me.[InvoiceNum]) Then
Me.[InvoiceNum] = Nz(DMax("InvoiceNum", "Table1"),0) + 1
End If
End Sub

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

I want to automatically enter an invoice number into an invoiceref
field when I enter the invoice date into invoicedate field. My problem
is how do I take the previous invoice number and increment by 1?

Thanks.

 

[Sparky]

Thanks to all. Works a treat!

Sparky,

Nz(DMax("YourInvoiceNumField","YourInvoicesTable"),0) + 1

Also, you might consider using it instead in the form's BeforeUpdate event
procedure, especially if you are in a multi-user environment.

Brian