How to make invoice numbers automatically advance one number at a time.
Q: Peter Lessey writes: I would like the invoice numbers to advance automatically one number at a time. Also to start from a preset number. Is there a way to do this?
A: You can always use an AutoNumber field, but there are problems with that approach (you may have already tried it). There is a Help article on starting autonumbering with a specific number, but the technique is complex, and doesn’t always work, and then you can have skipped numbers, which is a problem in accounting. For gapless numbering, I use DMax to get the highest number used, and add 1 to it, in code running from a form’s Before_Insert event, such as the following:
Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo ErrorHandler
Dim lngID As Long
lngID = DMax(“[EmployeeID]”, “tblEmployees”) + 1
Me![EmployeeID] = lngID
ErrorHandlerExit:
Exit Sub
ErrorHandler:
MsgBox “Error No: ” & Err.Number & “; Description: ” & Err.Description
Resume ErrorHandlerExit
End Sub
This will not prevent all gaps – if you delete an invoice, you will have a gap in numbering. But at least you won’t get gaps caused by accidentally creating a new record, then deleting it, as can happen with an AutoNumber field.
