This is a table for invoice tracking for multiple different purposes from
commissions to what states we are doing business in, etc. etc. I liked this
feature because there are times that I need to manipulate invoice numbers to
show partial billings as well as for commission purposes.
If I understand Autonumber correctly I can't change the invoice number
after it has been assigned by Access. These invoices are not always
generated in a sequential order so it is important for us that we enter every
invoice number into Access so that we have a double check system to make sure
that all of our jobs are being billed since these billings are coming from
different areas of the country not just from one location.
When I enter these numbers into Access I am sometimes entering 10 - 200 at a
time, this is why this feature was so important for our purposes.
Autonumbers are indeed not suitable: they will have gaps (even hitting <ESC>
after the first keystroke of a new record permanently uses up that autonumber
value) and cannot be edited.
Instead, use a Form to enter the data (not a table datasheet). You can put
code in the form's BeforeInsert event to automatically assign a new
incremented invoice number:
Private Sub Form_BeforeInsert(Cancel as Integer)
Me!InvoiceNo = NZ(DMax("InvoiceNo", "[tablename]")) + 1
End Sub