---

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

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

Question: I have a database in Access 2003/XP/2000/97. Using VBA code, I want to generate a system assigned (AutoNumber) code for specific items falling under different commodity types in this format:

AGR-0001
AGR-0002
ITS-0001
and so on...

Where AGR and ITS are ComID values (The ComID is the primary key for the Commodity Type table), followed by the next number in the sequence for the given ComID.

Every time an item is entered for a certain commodity type, an item code is assigned to it formatted as: ComID + the item counter

However, the counter is just local to a certain Commodity type. A new counter applies to another commodity type as the sample data above shows....

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

The user can control the assignment of the next number through the Codes table. In this table, there is a record for AGR (which is Agricultural Supplies) as well as an entry for ITS (which is IT Supplies).

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

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

Function NewItemCode(pComID) As String

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

   On Error GoTo Err_Execute

   Set db = CurrentDb()

   'Retrieve last number assigned for Commodity Type
   LSQL = "Select Last_Nbr_Assigned from Codes"
   LSQL = LSQL & " where Code_Desc = '" & pComID & "'"

   Set Lrs = db.OpenRecordset(LSQL)

   'If no records were found, create a new Commodity Type 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 & "('" & pComID & "', 1)"

      db.Execute LInsert, dbFailOnError

      'New Item Code is formatted as "AGR-0001", for example
      LNewItemCode = pComID & "-" & Format(1, "0000")

   Else
      'Determine new ItemCode
      'New ItemCode is formatted as "AGR-0001", for example
      LNewItemCode = pComID & "-" & 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 = '" & pComID & "'"

      db.Execute LUpdate, dbFailOnError

   End If

   Lrs.Close
   Set Lrs = Nothing
   Set db = Nothing

   NewItemCode = LNewItemCode

   Exit Function

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