Can this be done?!!

[DaniBoy]

Hello,
I am creating an application for my boss, its a database that keeps track of workorders by fiscal year. Its there any way that I can create a code where at the end of the fiscal year it saves all the information for that year and strat from scratch again but with the new fical year on the fical Year Field? and to be able to go back and find a workorder from the previous years?
Thanks
Daniel

 

[Jack Cowley]

I assume that one of your fields is a date field. That is all you need. No need to start from scratch each year, just keep going. Use queries to find data for any fiscal year that you want to look at....

 

[DaniBoy]

Thanks Jack, But what about the workorder number? They want me to make the work order number start from 0 every year.

 

[Travis]

Then in this case the Workorder field cannot be a unique field (This rules out using AutoNumber). What you need to do is to create a function that returns the last used Workorder number for the current Fiscal Year.

Public Function GetLast(tbl As String, fld As String) As Variant
'tbl=Name of the table
'fld=Name of the fld
'This is the DAO Method
Dim db As database
Dim rst As DAO.Recordset
Dim str As String

str = "Select Top 1 [" & fld & "] From [" & tbl & "] " & IIf(Criteria <> "", "Where " & Criteria, "") & " ORDER BY [" & fld & "]"
Set db = CurrentDb
On Error Resume Next
Set rst = CurrentDb.OpenRecordset(str)
rst.MoveFirst
If Err.Number <> 0 Then
'There are no records
GetLast = 0
Else
GetLast = rst.Fields(fld) + 1
End If

End Function