I am trying to change the current function to fit the needs of the years to come. I have table labeled [Codes] where the value of 'LRS' is stored. I'd like to have the option for other locations to use the database and assign their own unique ID [ABC] instead of the stored value below. Also I would like for the admin user to switch the year 'represented by the [13] and the value after [60001] with their values.
For example:
(id - year - value)
LRS-13-60000
ABC-13-70000
DEF-13-80000
In the end, I will create a admin page that allows the admin to select the station ID [ABC], year [13], and unique values [70000] for the start of the new year or a new location setup.
What this function does: It assigns a specific number that our headquarters assigns as a range for a purchase order in the beginning of the year. So for LRS, the range is 60000-69999. There will never be a case we reach more than 61000.
So could I replace the LRS with """?
For example:
(id - year - value)
LRS-13-60000
ABC-13-70000
DEF-13-80000
In the end, I will create a admin page that allows the admin to select the station ID [ABC], year [13], and unique values [70000] for the start of the new year or a new location setup.
What this function does: It assigns a specific number that our headquarters assigns as a range for a purchase order in the beginning of the year. So for LRS, the range is 60000-69999. There will never be a case we reach more than 61000.
So could I replace the LRS with """?
Code:
Function NewRequisitionNumber() As String
Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim LInsert As String
Dim rstsource As DAO.Recordset
Dim LNewRequisitionNumber As String
On Error GoTo Err_Execute
Set db = CurrentDb()
Set rstsource = db.OpenRecordset("Codes", dbOpenTable)
'Retrieve last number assigned for Requisition Number
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = LSQL & " where Code_Desc = 'LRS'"
Set rstsource = db.OpenRecordset(LSQL)
'If no records were found, create a new Requisition Number in the Codes table
'and set initial value to 1
If rstsource.EOF = True Then
LInsert = "Insert into Codes (Code_Desc, Last_Nbr_Assigned)"
LInsert = LInsert & " values "
LInsert = LInsert & "('LRS', 1)"
db.Execute LInsert, dbFailOnError
'New Requisition Number is formatted as "LRS-13-60001", for example
LNewRequisitionNumber = "LRS-13-60000"
Else
'Determine new Requisition Number
'New Requisition Number is formatted as "LRS-13-60001", for example
LNewRequisitionNumber = "LRS" & Format(rstsource("Last_Nbr_Assigned") + 1, "-13-60000")
'Increment counter in Codes table by 1
LUpdate = "Update Codes"
LUpdate = LUpdate & " set Last_Nbr_Assigned = " & rstsource("Last_Nbr_Assigned") + 1
LUpdate = LUpdate & " where Code_Desc = 'LRS'"
db.Execute LUpdate, dbFailOnError
End If
rstsource.Close
Set rstsource = Nothing
Set db = Nothing
NewRequisitionNumber = LNewRequisitionNumber
Exit Function
Err_Execute:
'An error occurred, return blank string
NewRequisitionNumber = ""
MsgBox "An error occurred while trying to determine the next Requisition Number to assign."
End Function