Auto-Counter Reset

[Earl Anderson]

KB Article Q140908 provided the following function to create an Auto
Incrementing Counter:

Function Next_Custom_Counter ()
On Error GoTo Next_Custom_Counter_Err

Dim MyDB As Database
Dim MyTable As Recordset
Dim NextCounter As Long

'================================================= ================
'Open table and get the current value of "Next Available Number,"
'increment the value by 1, and save the value back into the table
'================================================= ================

Set MyDB = CurrentDb
Set MyTable = MyDB.OpenRecordset("Counter Table")

MyTable.Edit
NextCounter = MyTable("Next Available Counter")

'================================================= ================
'The next line can be changed to conform to your custom counter
'preferences. This example increments the value by 1 each time.
'================================================= ================

MyTable("Next Available Counter") = NextCounter + 1
MyTable.Update

MsgBox "Next available counter value is " & Str$(NextCounter)
Next_Custom_Counter = NextCounter

Exit Function

'================================================= ===============
'The following error routine should be replaced with a custom
'error routine. This example only resumes execution when an error
'occurs. If a record locking error occurs this is fine; however,
'any non-record locking error will result in an infinite loop.
'================================================= ===============

Next_Custom_Counter_Err:
Msgbox "Error " & Err & ": " & Error$
If Err <> 0 Then Resume
End

End Function

In a caseload tracking system, I use an alpha-numeric PK comprised of a
2-character region designator + 2-digit year designator +
4-digit counter (i.e. NY03-0549). My question is how can this function be
modified to be reset its counter to '1' (One) 'at will' or at a designated
date/time (i.a. 1/1/2004 0001hrs). This function doesn't increment my PK
directly, but merely advises the user as to the next sequential number
available. As the beginning of 2004 is fast approaching, I would like to
set the counter back to "NY04-0001" for the first case of the new year. I
realize that the same result can be accomplished by recreating a new counter
table-module-form on New Year's Eve, every year, however I feel that would
be 'bush and klunky' solution.
Thanks for any advise...
Earl Anderson