---

MS Access 2003: Create a sequential number you control that is assigned every time a form is opened

This MSAccess tutorial explains how to create a sequential number that you control that is assigned every time that a form is opened in Access 2003 (with screenshots and step-by-step instructions).

Question: In Microsoft Access 2003/XP/2000/97, I want to create a form that when opened will assign a sequential number that I can control to a PODNumber field. I need the format of the sequential number to be:

POD00000001

where

POD = the type
00000001 = the sequential number

I want the form to allow me to enter some common values and select a BoxTrack value from a combo box. Then when I click on a button, I want some VBA code to run that will insert a record into the BoxesDispatched table using these values. Finally, I want a subform that will display all of the BoxesDispatched records including the one that I just created.

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 as described above.

Download version in Access 2000

Let's look at the example. We have a BoxesDispatched table that consists of the following fields: BoxTrack, JobID, DateReturned, TimeReturned, Dispatchedby, PODNumber (sequential number that is assigned).

When we open the Access database, a form called frmAddBoxesDispatched will automatically open that allows us to create records in the BoxesDispatched table. This form works as follows:

Step 1, when the form is opened, a sequential number will be assigned to the PODNumber field based on the POD00000001 format specified above.

Step 2, the user will enter the common values for each BoxesDispatched record.

Step 3, the user will select a BoxTrack value from the combo box. Next, the user will click on the "Create record in BoxesDispatched table" button which will create a record in the BoxesDispatched table based on the values entered on this form.

When the VBA code has completed, it will display the following message if everything was successful.

Then it will refresh the BoxesDispatched subform to display the records from the BoxesDispatched table including the record just added.

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

On the Form's load event, there is code that assigns the next POD Number.

Private Sub Form_Load()

   'Assign a new PODNumber
   PODNumber = NewPODNumber

End Sub

On the click event for the Create record in BoxesDispatched table button, there is code that makes sure that valid values are entered on the frmAddBoxesDispatched and calls the functions to create the new record.

Private Sub cmdCreate_Click()

   Dim LResponse As Integer

   'Must enter a PODNumber
   If IsNull(PODNumber) = True Or Len(PODNumber) = 0 Then
      LResponse = MsgBox("You must enter a valid POD Number.", vbInformation, "Validation Failed")
      PODNumber.SetFocus

   'Must enter a Job
   ElseIf IsNull(JobID) = True Or Len(JobID) = 0 Then
      LResponse = MsgBox("You must enter a valid Job.", vbInformation, "Validation Failed")
      JobID.SetFocus

   'Must enter a DateReturned
   ElseIf IsNull(DateReturned) = True Or Len(DateReturned) = 0 Then
      LResponse = MsgBox("You must enter a valid Date Returned.", vbInformation, "Validation Failed")
      DateReturned.SetFocus

   'Must enter a TimeReturned
   ElseIf IsNull(TimeReturned) = True Or Len(TimeReturned) = 0 Then
      LResponse = MsgBox("You must enter a valid Time Returned.", vbInformation, "Validation Failed")
      TimeReturned.SetFocus

   'Must enter a Dispatchedby
   ElseIf IsNull(Dispatchedby) = True Or Len(Dispatchedby) = 0 Then
      LResponse = MsgBox("You must enter a valid Dispatched by.", vbInformation, "Validation Failed")
      Dispatchedby.SetFocus

   'Must enter a BoxTrack
   ElseIf IsNull(BoxTrack) = True Or Len(BoxTrack) = 0 Then
      LResponse = MsgBox("You must enter a valid Box Track.", vbInformation, "Validation Failed")
      BoxTrack.SetFocus

   'Create records
   Else
      If CreateBoxesDispatched(Form_frmAddBoxesDispatched) = True Then

         'Clear and refresh BoxTrack combo box
         BoxTrack.Value = Null
         BoxTrack.Requery

         'Refresh subform to reflect new records just added
         frmBoxesDispatched.Requery

         MsgBox "Records were successfully created and should now be visible in this form."

      Else
         MsgBox "Failed."
      End If

   End If

End Sub

Then in Module1 in the Access database, there is a function called CreateBoxesDispatched that creates the new record.

Function CreateBoxesDispatched(pfrm As Object) As Boolean

   Dim db As Database
   Dim LInsert As String

   On Error GoTo Err_Execute

   Set db = CurrentDb()

   'Create new record
   LInsert = "Insert into BoxesDispatched (BoxTrack, JobID, DateReturned,"
   LInsert = LInsert & " TimeReturned, DispatchedBy, PODNumber) VALUES ("
   LInsert = LInsert & "'" & pfrm.BoxTrack & "'"
   LInsert = LInsert & ", '" & pfrm.JobID & "'"
   LInsert = LInsert & ", #" & pfrm.DateReturned & "#"
   LInsert = LInsert & ", #" & pfrm.TimeReturned & "#"
   LInsert = LInsert & ", " & pfrm.Dispatchedby
   LInsert = LInsert & ", '" & pfrm.PODNumber & "')"

   db.Execute LInsert, dbFailOnError

   Set db = Nothing

   CreateBoxesDispatched = True

   Exit Function

Err_Execute:
   'An error occurred
   CreateBoxesDispatched = False
   MsgBox "An error occurred while trying to add a new BoxesDispatched record."

End Function

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

Function NewPODNumber() As String

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

   On Error GoTo Err_Execute

   Set db = CurrentDb()

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

   Set Lrs = db.OpenRecordset(LSQL)

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

      db.Execute LInsert, dbFailOnError

      'New PODNumber is formatted as "POD00000001", for example
      LNewPODNumber = "POD00000001"

   Else
      'Determine new PODNumber
      'New PODNumber is formatted as "POD00000001", for example
      LNewPODNumber = "POD" & Format(Lrs("Last_Nbr_Assigned") + 1, "00000000")

      '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 = 'POD'"

      db.Execute LUpdate, dbFailOnError

   End If

   Lrs.Close
   Set Lrs = Nothing
   Set db = Nothing

   NewPODNumber = LNewPODNumber

   Exit Function

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