---

MS Access 2003: Create a sequential number that you can control instead of using an AutoNumber field

This MSAccess tutorial explains how to create a sequential number that you control instead of using an autonumber field in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I want to create a sequential number that I can control instead of using an AutoNumber field. How can I do this?

Answer: We've created a sample Access database that you can download that demonstrates how to create a sequential number that you can control.

Download version in Access 2000

Let's look at the example. Below, we have a form that allows the user to enter a record. The user will enter the Date, Description, and Value, and the form will automatically assign a sequential number to the Journal # field.

The user can control the assignment of the next number through the Codes table. In this table, there is a record for Journal Number that displays the last number assigned. The user can modify this value accordingly to start at whatever number is appropriate.

Then in Module1 in the Access database, there is a function called NewJournalNbr that returns the next number in the sequence and increments the Last_Nbr_Assigned field by 1.

Function NewJournalNbr() As Long

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

   On Error GoTo Err_Execute

   Set db = CurrentDb()

   'Retrieve last number assigned for Journal Number
   LSQL = "Select Last_Nbr_Assigned from Codes"
   LSQL = LSQL & " where Code_Desc = 'Journal Number'"

   Set Lrs = db.OpenRecordset(LSQL)

   'If no records were found, return an error
   If Lrs.EOF = True Then
      LNewJournalNbr = 0
      MsgBox "There was no entry found in the Codes table for Journal Number."

   Else
      'Determine new Journal Number
      LNewJournalNbr = Lrs("Last_Nbr_Assigned") + 1

      'Increment Journal Number in Codes table by 1
      LUpdate = "Update Codes"
      LUpdate = LUpdate & " set Last_Nbr_Assigned = " & LNewJournalNbr
      LUpdate = LUpdate & " where Code_Desc = 'Journal Number'"

      db.Execute LUpdate, dbFailOnError

   End If

   Lrs.Close
   Set Lrs = Nothing
   Set db = Nothing

   NewJournalNbr = LNewJournalNbr

   Exit Function

Err_Execute:
   'An error occurred, return 0
   NewJournalNbr = 0
   MsgBox "An error occurred while trying to determine the next Journal Number to assign."

End Function

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