[AccessD] Dire need for help

Sorry, I posted this on another thread first...AAAARRG....I'm just so
frustrated at the moment.

OK...I need help here...BADLY! I've been like the typical guy, who
won't stop and ask for directions...I've been working on this one single
issue, since yesterday, and I cannot find the problem.

I wrote this program, many moons ago, for our Social Services Fraud
Unit. They told me, at that time, that they would never exceed, or even
get to 1000 records in a year. There largest amount was around 700. The
reason this was an issue was their control numbers, which were in the
form of 06-001, where this number would be the first case of the year
2006. It then rolls over for the first case of the following year, to
07-001. Despite their claim, I made the control number in the form of,
06-0001, so that it could handle up to 9,999 records. When they saw
this, they demanded that I change it. They did not want the extra digit
in their number. 

Well, guess what. Two years ago, they went over the 1000 record mark
and called me to make the change. Actually that year they thought it was
a fluke or something, so we just made do somehow...I don't even recall.
Last year it was the same issue, so I fixed it...or thought I did. It
must have worked because it went up to record 06-1000 this year, before
halting, whereas last year it stopped at 05-999.

Now we are at my current problem, and I just can't figure it out. It
looks like it should work and it isn't breaking at 999, but rather 1000,
and this confuses me. The code is as follows:

Function NewControlNum() As String
Dim strSQL As String
Dim strNewID As String
Dim rst As Recordset
strSQL = "SELECT nz(Max(Mid([tblMain]![txtControlNum],4)),0)+1 AS
lngMaxID," & _
         "Right(Year(Now()),2)" & _
         "& '-' & [lngMaxID] AS lngNewID " & _
         "FROM tblMain " & _
         "WHERE
(((Left([tblMain]![txtControlNum],2))=Right(Year(Now()),2)));"
Set rst = CodeDb.OpenRecordset(strSQL)
With rst
   strNewID = rst!lngNewID
   rst.Close
   Set rst = Nothing
End With
NewControlNum = strNewID

Rem
***************************************************************************

Select Case Len(NewControlNum)
    Case 4
        NewControlNum = Left(NewControlNum, 3) & "00" &
Right(NewControlNum, 1)
    Case 5
        NewControlNum = Left(NewControlNum, 3) & "0" &
Right(NewControlNum, 2)
End Select


End Function

--------------------------------------------------------------------------------------

BTW...this is an A97 program. 

It works perfectly fine, filling in the next control number, when you
hit upon a new record, until it gets to 06-1000, and then it just wants
to keep putting in that same number.

As a test, I went into the table and added a record 06-1101, just to
see what the result would be. It too returned 06-1000 as the next
number. I deleted the last 5 records so that we were at 06-996, and it
let me re-enter them, putting in the proper numbers, as I went (06-997
thru 06-1000).

I just know it is something simple that I am missing.

John W. Clark