Access: Renumber invoices with VBA code in Access 2003/XP/2000/97

Question:  In Access 2003/XP/2000/97, I have a database which contains a large number of records. Each week, I need to renumber the INVOICE NUMBER field in a table named DATA with a new batch of numbers.

Answer:  You can renumber your invoice numbers using a custom function.

Open your Access database, click on the Modules tab and create a new Module. Paste in the following code:

Function UpdateInvoiceNumbers() As Boolean

   Dim db As Database
   Dim LSQL As String
   Dim Lrs As DAO.Recordset
   Dim LUpdate As String
   Dim LInvoiceNbr As Long

   On Error GoTo Err_Execute

   'Query user for the starting invoice number
   LInvoiceNbr = InputBox("Please enter starting invoice number.", "Renumber invoices")

   Set db = CurrentDb()

   'Retrieve each record
   LSQL = "select [INVOICE NUMBER] from DATA"
   LSQL = LSQL & " where [INVOICE NUMBER] < " & LInvoiceNbr
   LSQL = LSQL & " order by [INVOICE NUMBER]"

   Set Lrs = db.OpenRecordset(LSQL)

   Do Until Lrs.EOF
      'Renumber invoice number
      LUpdate = "update DATA"
      LUpdate = LUpdate & " set [INVOICE NUMBER] = " & LInvoiceNbr
      LUpdate = LUpdate & " where [INVOICE NUMBER] = " & Lrs("INVOICE NUMBER")

      db.Execute LUpdate, dbFailOnError

      'Increment invoice number
      LInvoiceNbr = LInvoiceNbr + 1
      Lrs.MoveNext
   Loop

   Set Lrs = Nothing
   Set db = Nothing

   MsgBox "Renumbering the invoice numbers has successfully completed."

   UpdateInvoiceNumbers = True

   On Error GoTo 0

   Exit Function

Err_Execute:
   MsgBox "Updating the invoice numbers failed."
   UpdateInvoiceNumbers = False
End Function

You can then call the function from a Form (ie: button click event, etc) or a Macro and it will renumber the invoice numbers.

Please note that the function assumes that you are always assigning higher invoice numbers than the previous.

If after trying this example, you receive a "not defined" error on the "Dim db as Database" declaration, you will need to follow some .