Resetting automatic File Number at start of new year

[tdw]

In my Orders database (which someone else created and I have been improving), when I enter a new order it generates a file number (i.e. 2007-0001) using an autonumber REC_ID field. I'll show you the code that it uses.
The following occurs upon a button click:

If Me![REC_ID] <> 0 And Me![FILE_NO] = "" Then
    DoCmd.SetWarnings False
        If Me![REC_ID] - 10493 < 10 Then ' Numeric Value 10493 is last value of rec_id upon year change for file_id renum
        DoCmd.OpenQuery "FILE_NO_Update_New_000", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
        If Me![REC_ID] - 10493 < 100 Then
        DoCmd.OpenQuery "FILE_NO_Update_New_00", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
        If Me![REC_ID] - 10493 < 1000 Then
        DoCmd.OpenQuery "FILE_NO_Update_New_0", acNormal, acEdit
        GoTo Confirm_File_No_Creation
        End If
 
    DoCmd.OpenQuery "FILE_NO_Update_New", acNormal, acEdit
 

Here are the update queries:

[b](FILE_NO_Update_New_000)[B/]
 
UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-000" & [REC_ID]-"10493"
WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
 
[b](FILE_NO_Update_New_00)[B/]
 
UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-00" & [REC_ID]-"10493"
WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
 
[b](FILE_NO_Update_New_0)[B/]
 
UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-0" & [REC_ID]-"10493"
WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
 
 (FILE_NO_Update_New)
 
UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-" & [REC_ID]-"10493"
WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
 

It seems pretty complex to me, but I understand it. The problem is that at the beginning of every year, we reset our file number back to 0001 (i.e. the first order of 2006 was 2006-0001, first order of 2007 was 2007-0001, etc.). The only way we know to accomplish this is to go into the code for the form and all three queries to change the REC_ID number being used in the calculations (the 10493 number in these examples). To know what to change it to, we have to find the highest REC_ID (the last order entered the previous year), which could be in either of two tables (one table of open orders: SC_OPEN, and another table of closed, or archived, orders: SC_ARCH). To do that, we have to manually open each table and look for ourselves. This can be complicated and very easy to badly screw up for non-programmers to try to accomplish, and it's not reasonable to expect that a programmer will always be available first thing in the morning on the first work day of the year to make these changes.

It would seem to me that there is simpler way, maybe even an automated way, to have our file number reset to 0001 at the beginning of each year. Any thoughts? Any suggestions? Should the whole process be reworked?

[NeoPa]

Not off the top of my head no, but it might be interesting to know which line it appears on? Otherwise I can wait to see where you get to.

[tdw]

Not off the top of my head no, but it might be interesting to know which line it appears on? Otherwise I can wait to see where you get to.

It appears that it is occurring on the first DCount line. I threw in a message box between every step so I could see where it was giving me the error message "you canceled the previous operation". The last message box I get before the error is "Step 2: Dim variables as integers accomplished"

Private Sub Create_Order_File_Structure_Click()
On Error GoTo Err_Create_Order_File_Structure_Click
 
'   Yes No Box
    Dim Msg, Style, Title, Response
    Msg = "Are you sure you want to Create this new Open Order?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Create Order"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
 
    RunCommand acCmdSaveRecord
 
MsgBox ("Step 1: save record accomplished")
 
Dim stHighest As Integer
Dim stHighOpen As Integer
Dim stHighArch As Integer
 
MsgBox ("Step 2: Dim variables as integers accomplished")
 
    '   Check to see if this is the first order of the year
    If DCount("[FILE_NO]", "SC_OPEN", "Left([FILE_NO], 4) = Year(DATE)") < 1 Then
 
MsgBox ("Step 3a: first order of year in open? found true")
 
        If DCount("[FILE_NO]", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)") < 1 Then
            Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
 
MsgBox ("Step 3b: first order of year in arch? found true. accomplished")
 
        End If
    Else
 
MsgBox ("Step 3c: not found to be first order of year")
 
        '   Find the highest file number for the current year from both tables
        stHighOpen = DLast("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Year(DATE)")
 
MsgBox ("Step 4a: find highest number in open accomplished")
 
        stHighArch = DLast("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)")
 
MsgBox ("Step 4b: find highest number in arch accomplished")
 
        '   Pick the one that is highest
            If stHighOpen < stHighArch Then
                stHighest = stHighArch
 
MsgBox ("Step 4c: found open to be lower than arch")
 
            Else
                stHighest = stHighOpen
 
MsgBox ("Step 4d: found open to be higher than arch")
 
            End If
 
        '   Create new file number
        Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
 
MsgBox ("Step 5: add 1 to highest number accomplished")
 
    End If
 
Confirm_File_No_Creation:
    DoCmd.SetWarnings True
    MsgBox ("The File Number Has Been Created For The Order")
 

[NeoPa]

Sorry to take so long.
I couldn't see for looking :
Left([FILE_NO], 4) is alpha and Year(DATE) is numeric.
Try instead :

If DCount("[FILE_NO]", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE, 'yyyy')") < 1 Then

[tdw]

Sorry to take so long.
I couldn't see for looking :
Left([FILE_NO], 4) is alpha and Year(DATE) is numeric.
Try instead :

If DCount("[FILE_NO]", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE, 'yyyy')") < 1 Then

I made the changes but I still get the same message "you canceled the previous operation". The message appears at the same place as before. I've tried looking through this forum, and the microsoft site, but haven't found anything even close to resembling this problem.

[tdw]

I made the changes but I still get the same message "you canceled the previous operation". The message appears at the same place as before. I've tried looking through this forum, and the microsoft site, but haven't found anything even close to resembling this problem.

By the way, the form that runs this code does not use either the SC_OPEN nor the SC_ARCH tables as it's data source. Could this be a cause of the problem? If so, I'll feel really stupid for not mentioning it before!

[NeoPa]

By the way, the form that runs this code does not use either the SC_OPEN nor the SC_ARCH tables as it's data source. Could this be a cause of the problem? If so, I'll feel really stupid for not mentioning it before!

No. They should be entirely independant of each other.
I'm not sure what your problem is if it's not that I'm afraid.
I may get another chance to look later, but life's very busy ATM I'm afraid.

[tdw]

life's very busy ATM I'm afraid.

Same here! :-) I'll have to wait till later (or tomorrow) to play with Access anyway.

[NeoPa]

Same here! :-) I'll have to wait till later (or tomorrow) to play with Access anyway.

That's fine then. We both agree to give the other plenty of time to respond :D

[tdw]

I made the changes but I still get the same message "you canceled the previous operation". The message appears at the same place as before. I've tried looking through this forum, and the microsoft site, but haven't found anything even close to resembling this problem.

Ok, I finally have some more time to devote to this. Just waking this sleeping thread back up. :-)

[NeoPa]

Ok, I finally have some more time to devote to this. Just waking this sleeping thread back up. :-)

I've got a couple of potentially sticky days ahead. I had a quick look through, but nothing jumped out as the problem. If I don't think of anything by Thursday bump it up again for me please. I'll try to look at it more deeply before then if I get a chance though.