Add Autonumber when appending records

Posted on 2014-09-29
Last Modified: 2015-07-11
I created an Apend Query and I have a field called Invoice Number, I want to add in this field a unique sequential number based on the records I am adding similar to the Autonumber in the Table. How can I accomplish this?
Question by:joeserrone
LVL 24

Accepted Solution

chaau earned 500 total points
ID: 40351344
There is no ROW_NUMBER() window function in Access. You can use a Domain Aggregate DCount() function to simulate it, like this:
DCount('ID', 'Table1', 'ID <= ' & [ID]) AS rn

Open in new window

If you could provide your existing query with the table structure I could help you write the actual syntax
LVL 49

Expert Comment

by:Gustav Brock
ID: 40351817
It is quite straight-forward to do with DAO.
Change your append query to a normal select query.
Public Sub CopyRecords()
  Dim rstSource   As DAO.Recordset
  Dim rstInsert   As DAO.Recordset
  Dim fld         As DAO.Field
  Dim strSQLSource      As String
  Dim strSQLInsert      As String
  Dim lngLoop     As Long
  Dim lngCount    As Long
  Dim lngInvoice As Long

  strSQLSource = "SELECT * FROM qryInvoiceNew"  ' New invoices.
  strSQLInsert = "SELECT TOP 1 * FROM tblInvoice Order By InvoiceNo Desc"  ' Existing invoices.

  Set rstInsert = CurrentDb.OpenRecordset(strSQLInsert)
  lngInvoice = rstInsert!InvoiceNo    ' Last used Invoice Number.

  Set rstSource = CurrentDb.OpenRecordset(strSQLSource)
  With rstSource
    lngCount = .RecordCount
    For lngLoop = 1 To lngCount
      With rstInsert
          lngInvoice = lngInvoice + 1
          For Each fld In rstSource.Fields
            With fld
              If .Attributes And dbAutoIncrField Then
                ' Skip Autonumber or GUID field.
              ElseIf .Name = "InvoiceNo" Then
                ' Insert next Invoice Number.
                rstInsert.Fields(.Name).Value = lngInvoice
                ' Copy field content.
                rstInsert.Fields(.Name).Value = .Value
              End If
            End With
      End With
  End With
  Set rstInsert = Nothing
  Set rstSource = Nothing
End Sub

Open in new window

Of course, for this loop to work, field names must match.

LVL 24

Expert Comment

ID: 40361683

the safest method is to use an AutoID for that purpose. Guessing that you do not only want sequential numbers for this append query but always it is the cleanest (and multi-user usable) solution to create a counter table for that purpose.

If you have an invoice table with an AutoID and an invoice number then the normal thing which could happen is that a user starts to enter data into a new invoice form but after typing the first thing Access would increment the AutoID. If the user decides to cancel the input the ID is lost. If he starts a new one the next AutoID is used instead - no sequential invoice number.

So if you create an additional table consisting of an AutoID field and an additional ID_Invoice field you can achieve what you want for both, the append query and the invoice form. In case of the invoice form it is simple: Use the "Before_Update" event of the form which will be triggered right before the current record will be saved. Here you can get the AutoID of the invoice table which Access already has filled in. Then execute an INSERT query which inserts this ID into the ID_Invoice field so this will create a unique sequential invoice AutoID in the counter table. As this has also the ID_Invoice (which is also unique but not sequential) you can read this line out to save that into the invoice field, all that in the "Before_Update" event.
As this will be executed only right before saving the user cannot cancel this and your invoice will always be sequential.

Now back to the append query.
The following code can be used to insert the sequential next invoice number into the invoice table using the counter table:
Public Sub InsertInvoice()
    Dim db As DAO.Database
    Dim lngCount As Long
    Dim strAppendID As String

    strAppendID = GetRandomIdentifier
    Set db = CurrentDb
    ' This should be your INSERT query, this is a simplified example using a customer field with a fix value only
    db.Execute "INSERT INTO tblInvoice (Customer,AppendID) VALUES ('X','" & strAppendID & "')"
    ' This creates the desired unique sequential invoice no. in the table tblInvoiceNo
    db.Execute "INSERT INTO tblInvoiceNo (AppendID) VALUES ('" & strAppendID & "')"
    ' Now update the invoice table with that no
    db.Execute "UPDATE tblInvoice INNER JOIN tblInvoiceNo AS INo ON tblInvoice.AppendID = INo.AppendID " & _
               "   SET tblInvoice.InvoiceNo = INo.InvoiceNo " & _
               " WHERE tblInvoice.AppendID = '" & strAppendID & "'"
    ' Optional: Update the invoice ID back to the invoice no table for later possible use
    db.Execute "UPDATE tblInvoiceNo INNER JOIN tblInvoice AS I ON tblInvoiceNo.AppendID = I.AppendID " & _
               "   SET tblInvoiceNo.ID_Invoice = I.ID_Invoice" & _
               " WHERE I.AppendID = '" & strAppendID & "'"
    Set db = Nothing
End Sub

Open in new window

Additionally you need a function to create a unique ID which is not used by another ID:
Public Function GetRandomIdentifier() As String
    Dim i As Byte
    Dim strOut As String
    For i = 1 To 100
        strOut = strOut & Replace(Chr((Rnd() * 126) + 32), "'", "x")
    GetRandomIdentifier = strOut
End Function

Open in new window

(You can of course better use a function to create a real GUID instead but this should only show the way and 100 random characters should also be unique enough.)

What it does: It gets a unique 100 character random string as "GUID" and use that to insert it together with the new row into the invoice table (= your append query plus the new AppendID field which must be added to the invoice table).
Then it inserts a new row into the InvoiceNo table which consists of the AutoID "InvoiceNo", the long integer "ID_Invoice" and the 100 character string field "AppendID". This also gets the same AppendID.

Now an UPDATE command can be used to JOIN the two tables using the AppendID and then the InvoiceNo can be inserted into the invoice table and optionally in a second UPDATE back into the InvoiceNo table to have a clear reference.

This of course works only if your append query does not use an INSERT...SELECT command because adding more than one row at the same time does not execute any VBA more than once so it would not be possible to create different "GUIDs" for each row. In this case only a recordset-loop can help here.

This method is absolutely safe for multi-user environments because both tables using the same AppendID to "find" each other later and the AutoID makes sure that no ID is used more than once. Moreover it uses pure SQL to manipulate the data.


LVL 49

Expert Comment

by:Gustav Brock
ID: 40361975
Christian, aside from overcomplication the scenario, I think you miss that Joe just wants a sequential number series for the new invoices. This cannot be random or an autonumber as it has to start from the number of the last invoice previously added. Most accountants don't accept "holes" in the series of invoice numbers.

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

LVL 24

Expert Comment

ID: 40362078
Hi Gustav,

the random number is not the invoice number, it is a JOIN criteria, if you read exactly. As I said above you can also use a GUID which fulfills the same purpose.

The invoice number can (and should) of course be an autonumber field as this is the best sort of sequential number which is guaranteed without any programming.
Maybe you didn't really read what I wrote above, this method is an absolute safe method to have a sequential number without any holes - and it is really not complicate. (I'm fully aware of the problem of sequential invoice numbers, thanks for the "clarification"... that's exactly why I wrote this recommendation.)

If there is an existing start number then you can add as many entries into the counter table to come to the wanted number and then delete them (as easiest method). The next number is the wanted, the rest is as I wrote above.

Using anything like "last number + 1" (which I often see as recommendation for sequential numbers) is NOT multi-user safe as the number you got could be used by another user in the same moment you try to insert that. Especially your method shows the wrong way: You get the last number first and then do a lot "long" running VBA before inserting the number - which is enough time for any other user trying to do the same at the same time to write the number in the mean time.
("Long" means of course milliseconds but enough for other users. By the way: Using CurrentDb more than once in a procedure is really not the well-known way to use that, I'm sure you know that...)

In example: User1 executes your code, gets number 5 as last invoice. User1 is still executing that while getting the invoice list to copy. It assumes now, 6 is the next number to use. Your code now starts writing the next invoice using number 6. Now User2 starts the same and gets 6 as last number (while the code of User1 is still running). The code assumes 7 as new invoice. The code of User1 writes 7 in this moment and User2 is getting the list of invoices to copy. User2 writes 7 and bang!, the code throws an error (if the Invoice number has a unique index, otherwise you now have simply invoice number 7 twice). The code of both users goes on if there was no error and you have a list of double invoice numbers.

Working with a database means: Working with SQL. VBA and procedural code should only be used if it is really not possible to solve a problem with SQL alone. That's not only true for Access but for any database. I wonder why you recommend such code as I'm sure that you are a database professional.


LVL 49

Expert Comment

by:Gustav Brock
ID: 40362109
Christian, I know all that. However, Joe doesn't indicate that this is a heavy duty multiuser application creating thousands and thousands of invoices, rather a typical small business app where a few people write invoices and never at exactly the same second, and somewhere he has a batch routine that copies newly created invoices to the list of confirmed invoices.

Further, the autonumber is no guarantee for an unbroken sequential series of numbers; if a new record for some reason cannot be saved, this number will be lefted unused. I'm sure you are aware of this.

For a bullet-proof allocation of sequential numbers for large scale operations, you will need a reservation/confirmation method wrapped in transactions. That would be overkill here.

Finally, I don't know why state this:

> VBA and procedural code should only be used if it is really not possible to solve a problem with SQL alone.

It cannot be cut Black/White like that. As always: What to use and when depends on the circumstances.

LVL 24

Expert Comment

ID: 40362122
Hi Gustav,

I would say that it should always be the case that a database application should be prepared for multi-user environment because you never know when and if more than one user will access that. It doesn't need large scale, it is simply enough when two users do the same at the same time on two workstations. That can even be the case in a very small database.

you will need a reservation/confirmation method wrapped in transactions. That would be overkill here
Sure, that would be the best idea and I would not say that's an overkill, but explaining a transaction is a longer thing. I personally would even never use an Access database as backend, always SQL Server (or other database servers) where any single command is automatically a transaction and where getting a new number can be made in a stored procedure with an easy OUTPUT clause in the INSERT command... we can make it more safe, no doubt, but I do not want to write a book each time I answer a question...:-)
But if I have a decision between a safe and potentially unsafe method in the given frame of Access I would always use and recommend the more safe method, especially if the safer method is really not more complicated than a procedural code...;-)

What to use and when depends on the circumstances
Yep, that's why I've said that there are circumstances when procedural code is the only way to solve some things, but in any other case SQL should always be preferred as it is more safe and performant than VBA ever will be. But that's a discussion I don't want to make here as that has nothing to do with the problem here.


LVL 75
ID: 40362413
@ Christian:
"The invoice number can (and should) of course be an autonumber field as this is the best sort of sequential number which is guaranteed without any programming."

Of course as Gustav points out above, that is certainly *not* true for a myriad of reasons, none the least of which is if a user starts a record (new AN created) then backs out or whatever - that AN is lost - although - this behavior has varied over the different releases of Access - but is currently in effect. There were a couple of releases of Access where - if a new record was started, then cancelled - that AN would become the AN for the next new record.  And there are similar analogies concerning what Compact & Repair does with 'reusing' ANs.

LVL 24

Expert Comment

ID: 40362468
Hi mx,

does anyone of you really read what I wrote instead of picking some words and answering that?
I know that what you wrote and I also have written that by myself as NOT to use the AutoID field of the invoice table itself, exactly because a user could start a record and cancel that.

That's why I the invoice number should come from the extra table which only has the meaning of getting a sequential number - here's no choice of cancellation by the user as it's not the user who insert a new record. The method is bullet proof and I use that in an enterprise project since years WITHOUT ANY DOUBLE OR MISSING NUMBER.

Please read my solution post above completely before you answer, thanks!


Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

895 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now