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?

[ADezii]

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?

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).

__1. This process can be autuomated via a Public Function which returns the highest REC_ID in either 1 of 2 Tables and can be used virtually anywhere in the Database:

Public Function fFindHighestREC_ID() As Long
Dim lngID_1 As Long, lngID_2 As Long
 
ID_1 = DLast("[REC_ID]", "SC_OPEN")
ID_2 = DLast("[REC_ID]", "SC_ARCH")
 
If ID_1 > ID_2 Then
  fFindHighestREC_ID = ID_1
ElseIf ID_1 < ID_2 Then
  fFindHighestREC_ID = ID_2
Else    'both equal, assign to either one
  fFindHighestREC_ID = ID_1
End If
End Function

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

__2. Automate the resetting of FILE_NO

1. Create a Table called tblCurrentYear.
2. Create a single Field in this Table called [Current Year] - INTEGER
3. Enter the Current Year into the [Current Year] Field.
4. Run the following code before any processing begins:

If Year(Now()) = DLookup("[Current Year]", "tblCurrentYear") Then
   'do nothing, the year is the same
Else      'a New Year
   'run an UPDATE Query to Reset [FILE_NO] to Year(Now()) & "-0001"
End If

[tdw]

__1. This process can be autuomated via a Public Function which returns the highest REC_ID in either 1 of 2 Tables and can be used virtually anywhere in the Database:

Public Function fFindHighestREC_ID() As Long
Dim lngID_1 As Long, lngID_2 As Long
 
ID_1 = DLast("[REC_ID]", "SC_OPEN")
ID_2 = DLast("[REC_ID]", "SC_ARCH")
 
If ID_1 > ID_2 Then
  fFindHighestREC_ID = ID_1
ElseIf ID_1 < ID_2 Then
  fFindHighestREC_ID = ID_2
Else    'both equal, assign to either one
  fFindHighestREC_ID = ID_1
End If
End Function

__2. Automate the resetting of FILE_NO

1. Create a Table called tblCurrentYear.
2. Create a single Field in this Table called [Current Year] - INTEGER
3. Enter the Current Year into the [Current Year] Field.
4. Run the following code before any processing begins:

If Year(Now()) = DLookup("[Current Year]", "tblCurrentYear") Then
   'do nothing, the year is the same
Else      'a New Year
   'run an UPDATE Query to Reset [FILE_NO] to Year(Now()) & "-0001"
End If

I believe that I understand what both of those do. But currently the File Number is generated by a number within the code that is the highest REC_ID minus one. For example, if the highest REC_ID at the end of 2007 is, let's say, 45673, then we have to take that number, subtract 45672, so the end result is 1. Then it uses that 1 as the new file number. Might I add a second field to tblCurrentYear called HighREC_ID, and have the queries reference that field each time a new File Number is generated?
Then could I have the single record in tblCurrentYear update to the new year and new highest REC_ID at the beginning of each year?

Also, is there a better way to have the file number add the appropriate number of zeros to the beginning of the number (2007-0001, versus 2007-1; 2007-0234, versus 2007-234) than by using the four queries in a row as is currently happening?

[tdw]

I believe that I understand what both of those do. But currently the File Number is generated by a number within the code that is the highest REC_ID minus one. For example, if the highest REC_ID at the end of 2007 is, let's say, 45673, then we have to take that number, subtract 45672, so the end result is 1. Then it uses that 1 as the new file number. Might I add a second field to tblCurrentYear called HighREC_ID, and have the queries reference that field each time a new File Number is generated?
Then could I have the single record in tblCurrentYear update to the new year and new highest REC_ID at the beginning of each year?

Also, is there a better way to have the file number add the appropriate number of zeros to the beginning of the number (2007-0001, versus 2007-1; 2007-0234, versus 2007-234) than by using the four queries in a row as is currently happening?

Was my question confusing? Sorry.

[NeoPa]

Was my question confusing? Sorry.

Your existing keys can be in either of two tables?

Conceptually you need to :

• Get the current highest number from all the records that match the current year.
• If there is none then you need to handle converting that result to 0. Nz() can work well in this scenario.
• Add 1 to the result of your previous search.
• Format this result if necessary (If you need to have n digits for instance).
• Append this to your year and this should be your new key.

[tdw]

Your existing keys can be in either of two tables?

Conceptually you need to :

• Get the current highest number from all the records that match the current year.
• If there is none then you need to handle converting that result to 0. Nz() can work well in this scenario.
• Add 1 to the result of your previous search.
• Format this result if necessary (If you need to have n digits for instance).
• Append this to your year and this should be your new key.

Thank you for the response. Please correct me if I am not understanding this correctly:
Are you saying that the above process should completely replace the current process for generating our file numbers? Meaning: get rid of all those existing queries, etc? And would that mean that I no longer even need the Rec_ID autonumber field? So, instead of using any kind of autonumber field whatsoever, I just let the file number creation process check for the highest existing value in the FILE_NO field for current year and add 1 each time a new order is created, thus when it's a new year it knows (because I tell it so) to start over at 0 (and then add 1).
I just want to be clear on this because this would be a total rewrite, and because it seems so much simpler than what currently exists I want to be sure that there isn't some reason that the guy who designed this database chose not to do it this way in the first place!

I am not familiar with Nz(), but I will find what I can learn on my own before asking you to explain it. :-)

[NeoPa]

I'm very pleased to receive such an intelligent response tdw. I can't say that you should necessarily scrap all your old stuff as I don't know your database in detail. Certainly this is the way I'd approach a similar situation.

Thank you for the response. Please correct me if I am not understanding this correctly:
Are you saying that the above process should completely replace the current process for generating our file numbers? Meaning: get rid of all those existing queries, etc?

I think so. It may be possible that some of the queries etc provide some extra functionality, but as described I can't see what you'd need them for if you use the approach I laid out.

And would that mean that I no longer even need the Rec_ID autonumber field? So, instead of using any kind of autonumber field whatsoever, I just let the file number creation process check for the highest existing value in the FILE_NO field for current year and add 1 each time a new order is created, thus when it's a new year it knows (because I tell it so) to start over at 0 (and then add 1).

Yes.
Except that this should automatically detect that there are no records for a new year - therefore, your calculated result (Converted from null to 0 using Nz()) will still be appropriate to the situation. You shouldn't need to 'tell it so' explicitly.

I just want to be clear on this because this would be a total rewrite, and because it seems so much simpler than what currently exists I want to be sure that there isn't some reason that the guy who designed this database chose not to do it this way in the first place!

Not everyone is able to think the concept through, so many people try to implement it in the best way that they know how at the time.

I am not familiar with Nz(), but I will find what I can learn on my own before asking you to explain it. :-)

Excellent, but I'm happy to help further should you need.

[tdw]

I'm back! Ok here's what I've come up with but it's not working. First of all I don't know how to make the function suggested near the beginning of this thread work within a private sub. I'm sure it's easy and I'm just dumb, but oh well. So I tried the following, and am getting the message "you entered an invalid argument in a domain aggregate function". I'm guessing that it doesn't like the way I tried to narrow down the criteria for the DCount, but I'm not sure. Any ideas?

Dim stHighest As Integer
Dim stHighOpen As Integer
Dim stHighArch As Integer
 
    '   Check to see if this is the first order of the year
    If DCount([FILE_NO], "SC_OPEN", Left([FILE_NO], 4) = Year(DATE)) = 0 Then
        If DCount([FILE_NO], "SC_ARCH", Left([FILE_NO], 4) = Year(DATE)) = 0 Then
            Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
        End If
    Else
        '   Find the highest file number for the current year from both tables
        stHighOpen = DMax(Right([FILE_NO], 4), "SC_OPEN", Left([FILE_NO], 4) = Year(DATE))
        stHighArch = DMax(Right([FILE_NO], 4), "SC_ARCH", Left([FILE_NO], 4) = Year(DATE))
        '   Pick the one that is highest
            If stHighOpen < stHighArch Then
                stHighest = stHighArch
            Else
                stHighest = stHighOpen
            End If
 
        '   Create new file number
        Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
    End If
 

[NeoPa]

Look at line #7

If DCount([FILE_NO], "SC_ARCH", Left([FILE_NO], 4) = Year(DATE)) = 0 Then

The first and third parameters should be a strings (enclosed in quotes (")) :

If DCount("[FILE_NO]", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)) = 0 Then

Please check the rest of your code to make sure that it matches this rule.

[tdw]

Look at line #7

If DCount([FILE_NO], "SC_ARCH", Left([FILE_NO], 4) = Year(DATE)) = 0 Then

The first and third parameters should be a strings (enclosed in quotes (")) :

If DCount("[FILE_NO]", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)) = 0 Then

Please check the rest of your code to make sure that it matches this rule.

I had a sneaky suspicion that it was something simple like that. Now I'm getting a new error "you canceled the last operation". I'll work on that one tomorrow though. If you know right off the top of your head what is wrong now please let me know, but otherwise I will try to figure this one out before asking the forum.

Thanks for the help!