next invoice number

[Dave]

Hi

I have a form that will lead onto an invoice. I want to pick the last
invoice number created and add 1 to it and suggest this as the next number.
What is the easiest way to do this and also how would i do the first number
as it will find a null

Thanks

 

[Guest]

Dave

The simplest way would be to make the invoice number an AutoNumber. Access
will increment it for you. The user will not have to type anything in that
field.

That said...If you do not want to use an AutoNUnmber, you can use the DMax()
domain function to find the highest number.

Put this in your form's Current event:


Private Sub Form_Current()
Dim varMaxNum As Variant

varMaxNum = DMax("CustNum", "Orders_noAutoNum")
If Me.NewRecord Then
If IsNull(varMaxNum) Then
Me.CustNum = 1
Else
Me.CustNum = varMaxNum + 1
End If
End If

End Sub

DMax has 3 parts: the field, the table and optional crieria. If no number is
found, it returns a Null. If it is null, the invoice number should be 1. If
it is not null we add 1 to the highest number (MAX number).