Please, please, please help!!! I'm a new Access user...so please be patient with me. 
With the help of many research and resources available on the internet, I currently have a simple database that's functioning just fine with sequence number setup as such:
sequence number = ADM-11-0001
- ADM stands for the admin department -- it changes to different codes depending on the department enterining the form (i.e., mkt for marketing)
- 11 stands for the year
- 0001 is the number that changes based on each record entry (e.g., 1st entry is ADM-11-0001, 2nd entry is ADM-11-0002, and so forth and so on; if the entry is for marketing dept, it would be MKT-11-0001, MKT-11-0002, and etc.)
I have 3 different tables setup for this database.
- Events table - includes seq_number (i.e., ADM-11-0001) and loc_code (i.e., ADM)
- Codes table - includes code_desc (i.e., ADM11)and last_nbr_assigned (e.g., 1)
- Loc_Code table - includes Loc_Code (i..e, ADM)
Here's my problem...I need to change the current year to 2012--with Oct as the beginning of each fiscal year....another word, right now we are in Oct 2011 so it should be fiscal year 2012. The sequence number should be ADM-12-001 for fiscal year 2012. How do make the change? Below is what I currently have. Please advise. Please tell me exactly where I need to make the change and what changes to make in order for it to show fiscal 2012. Also I need to make sure that the database will make the correct update when we reach Oct 2012 so that it will read ADM-13-0001.
I'm learning to use Access as I go so I'm really clueless
but force to take on the task. I'm afraid to make any changes because I can't afford to start over again for xxxx # of time. Please, please, please help!!!! Any help is greatly appreciated! Thank you!!!
----------------------
Option Compare Database
Function NewSeqNumber(pLoc_Code) As String
Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim LInsert As String
Dim Lrs As DAO.Recordset
Dim LSeqNumber As String
Dim LYear As String
On Error GoTo Err_Execute
Set db = CurrentDb()
'Retrieve last 2 digits of current year
LYear = Mid(CStr(Year(Date)), 3, 2)
'Retrieve last number assigned for Loc_Code/year combination
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = LSQL & " where Code_Desc = '" & pLoc_Code & LYear & "'"
Set Lrs = db.OpenRecordset(LSQL)
'If no records were found, create a new Loc_Code/year combination 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 & "('" & pLoc_Code & LYear & "', 1)"
db.Execute LInsert, dbFailOnError
'New sequential number is formatted as "ADM-11-0001", for example
LSeqNumber = pLoc_Code & "-" & LYear & "-" & Format(1, "0000")
Else
'Determine new sequential number
'New sequential number is formatted as "ADM-11-0001", for example
LSeqNumber = pLoc_Code & "-" & LYear & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "0000")
'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 = '" & pLoc_Code & LYear & "'"
db.Execute LUpdate, dbFailOnError
End If
Lrs.Close
Set Lrs = Nothing
Set db = Nothing
NewSeqNumber = LSeqNumber
Exit Function
Err_Execute:
'An error occurred, return blank string
NewSeqNumber = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."
End Function
------------------------
With the help of many research and resources available on the internet, I currently have a simple database that's functioning just fine with sequence number setup as such:sequence number = ADM-11-0001
- ADM stands for the admin department -- it changes to different codes depending on the department enterining the form (i.e., mkt for marketing)
- 11 stands for the year
- 0001 is the number that changes based on each record entry (e.g., 1st entry is ADM-11-0001, 2nd entry is ADM-11-0002, and so forth and so on; if the entry is for marketing dept, it would be MKT-11-0001, MKT-11-0002, and etc.)
I have 3 different tables setup for this database.
- Events table - includes seq_number (i.e., ADM-11-0001) and loc_code (i.e., ADM)
- Codes table - includes code_desc (i.e., ADM11)and last_nbr_assigned (e.g., 1)
- Loc_Code table - includes Loc_Code (i..e, ADM)
Here's my problem...I need to change the current year to 2012--with Oct as the beginning of each fiscal year....another word, right now we are in Oct 2011 so it should be fiscal year 2012. The sequence number should be ADM-12-001 for fiscal year 2012. How do make the change? Below is what I currently have. Please advise. Please tell me exactly where I need to make the change and what changes to make in order for it to show fiscal 2012. Also I need to make sure that the database will make the correct update when we reach Oct 2012 so that it will read ADM-13-0001.
I'm learning to use Access as I go so I'm really clueless
but force to take on the task. I'm afraid to make any changes because I can't afford to start over again for xxxx # of time. Please, please, please help!!!! Any help is greatly appreciated! Thank you!!!----------------------
Option Compare Database
Function NewSeqNumber(pLoc_Code) As String
Dim db As Database
Dim LSQL As String
Dim LUpdate As String
Dim LInsert As String
Dim Lrs As DAO.Recordset
Dim LSeqNumber As String
Dim LYear As String
On Error GoTo Err_Execute
Set db = CurrentDb()
'Retrieve last 2 digits of current year
LYear = Mid(CStr(Year(Date)), 3, 2)
'Retrieve last number assigned for Loc_Code/year combination
LSQL = "Select Last_Nbr_Assigned from Codes"
LSQL = LSQL & " where Code_Desc = '" & pLoc_Code & LYear & "'"
Set Lrs = db.OpenRecordset(LSQL)
'If no records were found, create a new Loc_Code/year combination 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 & "('" & pLoc_Code & LYear & "', 1)"
db.Execute LInsert, dbFailOnError
'New sequential number is formatted as "ADM-11-0001", for example
LSeqNumber = pLoc_Code & "-" & LYear & "-" & Format(1, "0000")
Else
'Determine new sequential number
'New sequential number is formatted as "ADM-11-0001", for example
LSeqNumber = pLoc_Code & "-" & LYear & "-" & Format(Lrs("Last_Nbr_Assigned") + 1, "0000")
'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 = '" & pLoc_Code & LYear & "'"
db.Execute LUpdate, dbFailOnError
End If
Lrs.Close
Set Lrs = Nothing
Set db = Nothing
NewSeqNumber = LSeqNumber
Exit Function
Err_Execute:
'An error occurred, return blank string
NewSeqNumber = ""
MsgBox "An error occurred while trying to determine the next sequential number to assign."
End Function
------------------------
