Access: Create a sequential number you control that is assigned every time a form is opened in Access 2003/XP/2000/97
Question: In 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 take a 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.