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!

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

[tdw]

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.

Ok, no problem, and no rush. Thank you very much!

[NeoPa]

Ok, no problem, and no rush. Thank you very much!

Try (don't hold breath) :

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

If problem persists, trace through the code and let me know if problem persists then.

[NeoPa]

If you don't know how to trace through code, then I'm putting together a tutorial (Debugging in VBA) on that in the Access Articles section. It's not finished yet, but there are some helpful pointers in there already, which might kick-start your usage of it nevertheless.

[tdw]

Try (don't hold breath) :

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

If problem persists, trace through the code and let me know if problem persists then.

It definitely liked that better. Now the problem is that it is always picking 2007-1200 as the next appropriate file number, when it should be higher. I'll post the code here again. I intentionally changed some lines where it had said to set warnings to false, to say true instead so I could see what it was doing.

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("*", "SC_OPEN", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then
MsgBox ("Step 3a: first order of year in open? found true")
 
        If DCount("*", "SC_ARCH", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 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) = Format(DATE(), 'yyyy')")
 
MsgBox ("Step 4a: find highest number in open accomplished")
 
        stHighArch = DLast("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')")
 
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
        If stHighest > 1 And stHighest < 10 Then
            Me.FILE_NO = Year(DATE) & "-000" & stHighest + 1
        End If
 
        If stHighest > 9 And stHighest < 100 Then
            Me.FILE_NO = Year(DATE) & "-00" & stHighest + 1
        End If
 
        If stHighest > 99 And stHighest < 1000 Then
            Me.FILE_NO = Year(DATE) & "-0" & stHighest + 1
        End If
 
        If stHighest > 999 Then
            Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
        End If
 
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")
 
    '   Create Folder on O: drive
    stFileNumber = [FILE_NO]
    stPathName = "O:\" & [FILE_NO]
    stFileName = [FILE_NO] & ".rtf"
    stPFName = stPathName & "\" & stFileName
 
    If Dir$(stPathName, vbDirectory) <> "" Then  ' Does Path Already Exist?
        MsgBox ("The directory " & stPathName & " Already exits")
        GoTo Exit_Create_Order_File_Structure_Click
    Else
        MkDir Path:=(stPathName)   '  Create Directory Structure
    End If
 
 
'   Create Report File
    DoCmd.OutputTo acOutputReport, "File Creation", acFormatRTF, stPFName
    MsgBox ("The " & stPathName & " folder and the file " & stFileName & " have been created")
 
Else    ' User chose No.
    GoTo Exit_Create_Order_File_Structure_Click
End If
 
'   Query Name variable definitions
    Dim stQName As String
    Dim stQName1 As String
    stQName = "APPEND NEW RECORD TO OPEN" 'Named Database Query
    stQName1 = "Delete From New"  'Named Database Query
 
    DoCmd.SetWarnings True
    DoCmd.OpenQuery stQName, acNormal, acEdit   'Run Query
    'DoCmd.SetWarnings True
 
'   Yes No Box
    Msg = "Do you want to Print " & stFileNumber & "?"    ' Define message.
    Style = vbYesNo   ' Define buttons.
    Title = "Print New Order"    ' Define title.
    Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then    ' User chose Yes
    DoCmd.RunMacro ("Print New Order") '   Print New Order
End If
 
    DoCmd.SetWarnings True
    DoCmd.OpenQuery stQName1, acNormal, acEdit  'Run Query
    DoCmd.Requery   'Refresh Form
    'DoCmd.SetWarnings True
Exit_Create_Order_File_Structure_Click:
    Exit Sub
 
Err_Create_Order_File_Structure_Click:
    MsgBox Err.Description
    Resume Exit_Create_Order_File_Structure_Click
End Sub
 

[NeoPa]

It's probably a good idea to post the latest code again here.
Am I right in thinking now that the code runs without any errors ocurring anywhere, but the result it gives is incorrect?

[tdw]

It's probably a good idea to post the latest code again here.

I did below.

Am I right in thinking now that the code runs without any errors ocurring anywhere, but the result it gives is incorrect?

Yes, you are correct. It goes through everything with no errors, but the result is always 2007-1200 , rather than what it should be. Even when it just used that number the last time around, it picks it again, and again.....

[NeoPa]

It's probably a good idea to post the latest code again here.

I did below.

I was confirming it was a good idea rather than requesting it again ;)

Am I right in thinking now that the code runs without any errors ocurring anywhere, but the result it gives is incorrect?

Yes, you are correct. It goes through everything with no errors, but the result is always 2007-1200 , rather than what it should be. Even when it just used that number the last time around, it picks it again, and again.....

Try :

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

This provides the next number rather than the whole key string as I'm not absolutely sure what your format is (fixed variable length etc).

[NeoPa]

Yes, you are correct. It goes through everything with no errors, but the result is always 2007-1200 , rather than what it should be. Even when it just used that number the last time around, it picks it again, and again.....

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.

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

Thanks for your suggestions, I will get around to trying them soon (this week became busier than anticipated!) :-)

[NeoPa]

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.

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