Change the starting value of an Number field

[eyalco]

I have an invoice database. The invoice number is handled by dmax and is ok.
I need to allow the user to to have the opportunity to start the invoice number from other number then 1, unless he approves starting from 1. This is a choice he gets only when he starts using the project. from then, number is handled by dmax again.
How do I do that please?
Thanks

 

[Kiwiman]

Howzit

One way you can do it...

Put in the On Current Event of your Invoice form

Private Sub Form_Current()

Dim intVal As Integer
Dim intInv As Integer


 If Me.NewRecord Then
    
    intVal = DCount("[yourfield]", "yourtable")
        If intVal = 0 Then
            intInv = InputBox("What Invoice Number do you want as your first?", "Invoice Number",1) ' Activate input box setting 1 as defualt
            Me.yourcontrol.DefaultValue = intInv
        Else
            On Error Resume Next 'It should never occur, just to be sure...
            Me.yourcontrol.DefaultValue = Nz(DMax("[yourfield]", "yourtable"), 0) + 1
        End If
End If

End Sub

 

[missinglinq]

If you're working in a single-user environment, using this in the Form_Current event its' fine. If, however, you're in a multi-user environment it can result in duplicate numbers. Form_Current fires when you move to the new record. UserA starts a new record and the record is assigned a number. Whiles UserA is entering the rest of the data for his new record, UserB starts a new record. It's also assigned a number, the same number as UserA's record was assigned!

To avoid this, the code should fire at the last second before the record is saved. I use the Form_BeforeUpdate event for this, having it the last code there, not counting any error handing code.