I have specific numbers that are generated based on: [location],[year],[number] which is represented [LRS-13-60000]. Other locations may have [ABC-13-70000]. For each new purchase order, +1 is added to the number. Right now I am attempting to have my table [Codes] store the values of location, year, and number, as well as last number assigned to generate this number.
Any ideas?
Any ideas?
Code:
Option Compare Database
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 Code_Desc from Codes"
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = "Select StartingRange from Codes"
LSQL = "Select YearValue from Codes"
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 (Last_Nbr_Assigned)"
LInsert = LInsert & " values "
LInsert = LInsert & "(1)"
db.Execute LInsert, dbFailOnError
'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange"
Else
'Determine new Requisition Number
'New Requisition Number is formatted as "LRS-12-60001", if year is 2012 for example
LNewRequisitionNumber = "Code_Desc" & -"YearValue" & -"StartingRange" & -Format(rstsource("Last_Nbr_Assigned") + 1)
'Increment counter in Codes table by 1
LUpdate = "Update Codes"
LUpdate = LUpdate & " set Code_Desc = " & rstsource("Code_Desc")
LUpdate = LUpdate & " set Last_Nbr_Assigned = " & rstsource("Last_Nbr_Assigned") + 1
LUpdate = LUpdate & " set StartingRange = " & rstsource("StartingRange")
LUpdate = LUpdate & " set YearValue = " & rstsource("YearValue")
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