MS Access 2003: Renumber invoices with VBA code
This MSAccess tutorial explains how to renumber invoice numbers in a table using VBA code in Access 2003 (with screenshots and step-by-step instructions).
Question: In Microsoft 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 .
Advertisements