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.
Advertisements