---

MS Access 2003: Create a sequential number that you can control using a format such as A05-0001

This MSAccess tutorial explains how to create a sequential number that you control using a format such as A05-0001 in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I have tried to create a sequential number in a format such as the following:

A05-0001

where

A = the item_type
05 = the last two digits of the year (system date)
0001 = the sequential number

The year would need to change automatically based on system time and the sequential number would need to reset back to one with the change in years.

Is this possible?

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

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 Item Type, and the form will automatically assign a sequential number to the Sequential # field.

The user can control the assignment of the next number through the Codes table. In this table, there are two records for A (which is Accident) - one for 2004 as denoted by A04 and one for 2005 as denoted by A05. There is also an entry for F (which is Fire) for 2005 as denoted by F05.

If the Codes table does not contain an entry for an Item Type selected in the combo box, it will create an entry in the Codes table for the new Item Type and assign the Last_Nbr_Assigned field to 1.

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

Function NewSeqNumber(pItem_Type) As String

   Dim db As Database
   Dim LSQL As String
   Dim LUpdate As String
   Dim LInsert As String
   Dim Lrs As DAO.Recordset
   Dim LSeqNumber As String
   Dim LYear As String

   On Error GoTo Err_Execute

   Set db = CurrentDb()

   'Retrieve last 2 digits of current year
   LYear = Mid(CStr(Year(Date)), 3, 2)

   'Retrieve last number assigned for item_type/year combination
   LSQL = "Select Last_Nbr_Assigned from Codes"
   LSQL = LSQL & " where Code_Desc = '" & pItem_Type & LYear & "'"

   Set Lrs = db.OpenRecordset(LSQL)

   'If no records were found, create a new item_type/year combination in
   'the Codes table and set initial value to 1
   If Lrs.EOF = True Then

      LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
      LInsert = LInsert & " values "
      LInsert = LInsert & "('" & pItem_Type & LYear & "', 1)"

      db.Execute LInsert, dbFailOnError

      'New sequential number is formatted as "A05-0001", for example
      LSeqNumber = pItem_Type & LYear & "-" & Format(1, "0000")

   Else
      'Determine new sequential number
      'New sequential number is formatted as "A05-0001", for example
      LSeqNumber = pItem_Type & LYear & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "0000")

      'Increment counter in Codes table by 1
      LUpdate = "Update Codes"
      LUpdate = LUpdate & " set Last_Nbr_Assigned = " & Lrs("Last_Nbr_Assigned") + 1
      LUpdate = LUpdate & " where Code_Desc = '" & pItem_Type & LYear & "'"

      db.Execute LUpdate, dbFailOnError

   End If

   Lrs.Close

   Set Lrs = Nothing
   Set db = Nothing
   NewSeqNumber = LSeqNumber

   Exit Function

Err_Execute:
   'An error occurred, return blank string
   NewSeqNumber = ""
   MsgBox "An error occurred while trying to determine the next sequential 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.