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?

[tdw]

My pleasure.
Even if changing the function around fixes your error, I'd still use the code I posted in post #27 as it does the whole thing in one line for you. It even handles the first new record of a year.

I finally have a little time to get back to working on this. You suggest using the code

Val(Nz(Mid(DMax("[FILE_NO]","[SC_OPEN]","[FILE_NO] Like Format(Date(),'yyyy') & '*'"),6),"0"))+1
 

I'm trying to understand what this line does and does not do so that I know what parts of my code it should replace. If I am understanding correctly, it will look for the highest existing file number with the format 'yyyy' & '*' (and this will properly generate the 2007-0123 format?) located in my SC_OPEN table, and add 1 to it. You say it will also recognize if there is no existing file number with the current year as the beginning of the file number, and start at zero?
I believe that I see where all of the above works in the code. Now, since the highest existing file number may actually be in the SC_ARCH table, I suppose I should copy the same line of code, changing the table to SC_ARCH? In which case I would still have to tell it to determine which of the two tables has the highest file number, prior to running the code above, so that it knows which line (referencing which table) to use and which to ignore?

Hit me on the head if I'm way off on my understanding of this! :-)

[tdw]

I suspect (Not sure as I don't really want to do a reverse engineering job on your 130+ lines of code) that the reason you had this problem is that you were using DLast() rather than DMax(). Simply changing that MAY resolve the issues with your current code.

You are correct, that did resolve it. However I am interested in your other suggestion. See below for more questions about it.

[NeoPa]

I hadn't appreciated that you wanted to handle the max of both tables (begs the question why store the data in separate tables anyway?). Assuming that two tables are how you're doing this though, you will need some logic as per below :

Private Sub YourSub()
  Dim lngOpen As Long, lngMax As Long
 
  lngOpen = Val(Nz(Mid(DMax("[FILE_NO]", _
                            "[SC_OPEN]", _
                            "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
                   "0")) + 1
  lngMax = Val(Nz(Mid(DMax("[FILE_NO]", _
                           "[SC_ARCH]", _
                           "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
                  "0")) + 1
  If lngOpen > lngMax Then lngMax = lngOpen
End Sub

Your code wouldn't necessarily need to be in a self-contained procedure as this example is. This is just an illustration of the concept for handling a situation like yours.

[tdw]

I hadn't appreciated that you wanted to handle the max of both tables (begs the question why store the data in separate tables anyway?). Assuming that two tables are how you're doing this though, you will need some logic as per below :

Private Sub YourSub()
  Dim lngOpen As Long, lngMax As Long
 
  lngOpen = Val(Nz(Mid(DMax("[FILE_NO]", _
                            "[SC_OPEN]", _
                            "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
                   "0")) + 1
  lngMax = Val(Nz(Mid(DMax("[FILE_NO]", _
                           "[SC_ARCH]", _
                           "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
                  "0")) + 1
  If lngOpen > lngMax Then lngMax = lngOpen
End Sub

Your code wouldn't necessarily need to be in a self-contained procedure as this example is. This is just an illustration of the concept for handling a situation like yours.

Great, thanks! I appreciate it, and I will experiment with that even though it's working the other way. Always good to find shorter ways to do things!
The reason for two tables, though I'm not sure it's neccessary (I didn't write the original version of this database, just have been improving it and injecting it with steroids), is because once we complete a survey order, we archive it in the second table and delete it from the Open orders table. That way we can still search for data from past orders, but don't have it mixed in with the current ones.

[NeoPa]

I'm glad that helps.
As far as the tables go, setting a flag and filtering the data would be another option. I can't say how appropriate that would be for you, as I'm not there and don't know your exact situation. I'll happily leave that to you :)

[tdw]

I'm glad that helps.
As far as the tables go, setting a flag and filtering the data would be another option. I can't say how appropriate that would be for you, as I'm not there and don't know your exact situation. I'll happily leave that to you :)

You know, I though of that too. However, if there are a whole lot of archived orders, would it slow down the form for viewing open orders, since it's opening the whole recordset before filtering it? I have a "edit order" button in my "View Open Orders" form that will open another form that lets the user edit that record. It filters the Edit form so that only the record currently displayed on the View form shows up for editing, yet it takes a while to load that Edit form. I have assumed that it is because it is really opening the entire table before filtering it down to the one record.
I guess that's really the only reason that I can see for having a separate table for the Archived orders.

[NeoPa]

Whether or not the whole table is opened is generally down to your index design.
Simple rule of thumb :
If it can (by using one of your indexes) determine which records match your filter criteria, it will.
If you were to add an index on this flag field (not unique), then filtering should work fairly quickly.
At the end of the day, what works for you depends heavily on your requirements, but this could be considered. Obviously any change to the design or structure of a database is likely to involve the designer in some level of work (overhead).

[tdw]

Whether or not the whole table is opened is generally down to your index design.
Simple rule of thumb :
If it can (by using one of your indexes) determine which records match your filter criteria, it will.
If you were to add an index on this flag field (not unique), then filtering should work fairly quickly.
At the end of the day, what works for you depends heavily on your requirements, but this could be considered. Obviously any change to the design or structure of a database is likely to involve the designer in some level of work (overhead).

Ok, thanks. It would probably work pretty good to have a checkbox or other yes/no field in each record for closing out the order, but I better leave it as is for now and consider combining tables sometime in the future if we encounter anything that suggests the current setup has any problems relating to there being separate tables. While appending the data back into a single table would be easy, there would be the issue of "closing" individually a few thousand archived orders. :-)

[NeoPa]

Sounds like a sensible compromise :)
Why take on extra work now, just to save you some potential work at a later date? It may never happen.