Function Update

steve21nj

Registered User.
Local time
Yesterday, 23:15
Joined
Sep 11, 2012
Messages
260
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 """?
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
 
I have been holding off on doing this because of other projects inside the database but will work on this next week. I also need to figure out a way to separate the data further based on year.

Our new business year starts October 1st. With the New Year come new Requisitions Numbers, new budgets, and new OCCs. One thing I am concerned with is how to create the relationship with the Budget to Requisition to Year. Would I need to start a [year] table and link that with the [OCC] table that also links with the
Code:
 table that produces the new requisition number?[/FONT][/SIZE]

[SIZE=3][FONT=Arial]Otherwise when October 2013 comes around, the data will show negative values as more purchases are completed. And I’d like to be able to still view 2012 data to compare.[/FONT][/SIZE]
 
Any thoughts?
 

Attachments

  • codeOCC.PNG
    codeOCC.PNG
    11.6 KB · Views: 104
  • tblOCCcode.PNG
    tblOCCcode.PNG
    40 KB · Views: 94
I went ahead and started to change my coding for the RequisitionNumber. I am currently getting an error saying the value cannot be a zero-length string. I've attached my code, i'm sure it is in need of help!


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
 

Attachments

  • reqError.PNG
    reqError.PNG
    14.9 KB · Views: 105
  • codes.PNG
    codes.PNG
    23.9 KB · Views: 99

Users who are viewing this thread

Back
Top Bottom