I've created a database for making invoices and similar documents. As we know these documents must be numerated progressively and univocally without any missing number from one invoice (or other document) and the following.
For this reason I added an auto-number field to the table that is the number of the invoice or other document.
As we know MS Access creates a further new record just in the moment something is inserted / edited into the current/new record. So there is a new record no mater what, just right after the user has been started editing the former new record.
So if the db user starts editing a new invoice (or any other doc) just for doing exercise or practice without the will to save and print it but just exiting from the record with the Esc key when he makes mistakes or meet problems that cannot solve yet, a documento number is lost and a hole in numbering remains that cannot be recovered in next new record no mater what.
How do I set MS Access to create a new record not when the previous former new record is just edited but when it is saved by the user? In this way missing numbering in auto-number fields will be prevented. In other words how do I populate an auto-number field only when the record is "officially saved" instead of in the moment I start editing the new record?
autonumbers are for internal use only (that means you the develper of the application). For example, when you load a word document, the OS issues a memory segment location - you as a user of word don't care.
The same goes for autonumbers. They are for developers to create relationships between tables. they CAN NOT be used for things like invoice numbers etc.
Even if you move to a new record, start typing, the record is NOT yet saved. However, a NEW auto number will have been issued. If the user hits ESC key, or edit->un to NOT save the record, then the auto number will have not been saved - but now you have a gap in auto numbers. So if a user decides to not save a record, but has started typing in that record, the auto number is issued (and used up). However, if you don't save that record, and the user decides to not add (save) the record, then you will find a gap.
As a result, it is not practical to attempt to use auto numbers this way. You will find the same for Oracle, SQL server, and the vast majority of database systems. The simple and bottom line is such numbers are for you the developer to create and setup relationships. They have ZERO to do with things like invoice numbers etc.
Auto numbers are for relationships. The fact that you want to relate customers to say invoices has ZERO and NOTHING to do with the fact that you have, or have not some invoice number. Why should a whole application break down and your relationships stop working because you don't have some silly invoice number? A invoice number has ZERO ZERO to do with some internal auto number. You might make invoice numbers required, but then again I worked with some systems in that the invoice information is entered, and until such time the invoice is approved, or actually sent out, the invoice number is blank until such time it is approved or sent out. Regardless of some business rules, and even if a invoice number is required, it has nothing to do with auto numbers and relationships you setup. Such relationships will work with, or without a invoice number.
The answer is you cannot and should not use auto numbers in Access for external meaning. There is simply no practical way to control the next number, and to control missing gaps. Worse, it is HIGH unlikely that such invoice numbers will start at 1. And many companies adopt a system in which each invoice sent out to a given customer does NOT revel for example how many invoices were issued since the last one (this can give away valuable information to your customers).
If you need some incrementing invoice number? Then YOU the developer have to build and setup a system based on your requirements.
So, I would think that this requirement is something more then just a number starting at 1, then 2, then 3 etc. And you VERY much want the ability in your application to set the next or starting invoice number.
There are as many ways to do this as there are flavors of ice cream. However, a typical and common approach is to setup a small table.
So create a table called tblNextInvoice.
It will look like this: tblNextInvoice ID: autonumber PK - you want one of these for all tables. NextInvoice: long integer. - this is your next invoice number table.
This table will have ONE row. In the above table, simply enter into NextInvoice the next invoice number you want invoice numbers to start at.
Now, in the before update event of your form:
if isnull(me!InvoiceNumber) = True then
' about to save record - set the invoice number
me!InvoiceNumber = GetNextInvoiceNum()
End if
And you need to create a public function (placed in a standard code module - not the forms code module. It can look like this:
Public Function NextInvoice() as long
Dim myRST As Dao.Recordset
set myRST = Currentdb.OpenRecordSet("tblNextInvoice")
NextInovice = myRST!NextInvoiceNum
myRST.Edit
myRST!NextInvoiceNum = myRST!NextInoiceNum + 1
myRST.Update
myRST.Close
End Function
So, if you want to ensure no gaps, be able to set the the starting, and you want control over creating a invoice number? Then you as a developer must setup, create, and design your invoice number system. Because the system is custom, then you can include say 01152020-00012 types of format (So this sample number has the month, date + a sequence number. You might even has a design and development meeting with your accounting people as to what kind of format and even government regulations are required for your invoice numbering system. So that custom get next invoice does not have to be limited to JUST a number - you can include some string values, and change that function (and invoicenumber) to a text type column as opposed to just a number type of column.
For example, I have some software running for volunteer groups, and some church groups - because of their non-profit status, there are now in place some government regulations as to the format of the invoice(s) issued. And this is especially for the case of receipts numbers issued.
When receipts are issued, then not only must the receipt show the new receipt number, but ALSO show that the receipt issued is a replacement for receipt XXXX has to be clearly marked (this is to prevent someone asking for a 2nd receipt of a donation, and submitting it two times for a double tax deduction. So, now you not even allowed to issue a receipt with the same number two times to a donor! (they must show a new number, and ALSO the number it is replacing).
So, regardless of these issues, in near all business systems, YOU the developer will need complete control over how such numbers re to be issued - and you can't leave this issue to chance, nor can you leave this issue to auto numbers either. This types of numbers are for business rules and business operations - they have nothing to do with using auto numbers to setup some relationships in a database - and relationships are 100% separate concepts from that of business things like invoice numbers, or receipt numbers etc.