By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
425,854 Members | 869 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 425,854 IT Pros & Developers. It's quick & easy.

Resetting automatic File Number at start of new year

tdw
100+
P: 206
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:
Expand|Select|Wrap|Line Numbers
  1. If Me![REC_ID] <> 0 And Me![FILE_NO] = "" Then
  2.     DoCmd.SetWarnings False
  3.         If Me![REC_ID] - 10493 < 10 Then ' Numeric Value 10493 is last value of rec_id upon year change for file_id renum
  4.         DoCmd.OpenQuery "FILE_NO_Update_New_000", acNormal, acEdit
  5.         GoTo Confirm_File_No_Creation
  6.         End If
  7.  
  8.         If Me![REC_ID] - 10493 < 100 Then
  9.         DoCmd.OpenQuery "FILE_NO_Update_New_00", acNormal, acEdit
  10.         GoTo Confirm_File_No_Creation
  11.         End If
  12.  
  13.         If Me![REC_ID] - 10493 < 1000 Then
  14.         DoCmd.OpenQuery "FILE_NO_Update_New_0", acNormal, acEdit
  15.         GoTo Confirm_File_No_Creation
  16.         End If
  17.  
  18.     DoCmd.OpenQuery "FILE_NO_Update_New", acNormal, acEdit
  19.  
Here are the update queries:
Expand|Select|Wrap|Line Numbers
  1. [b](FILE_NO_Update_New_000)[B/]
  2.  
  3. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-000" & [REC_ID]-"10493"
  4. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  5.  
  6. [b](FILE_NO_Update_New_00)[B/]
  7.  
  8. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-00" & [REC_ID]-"10493"
  9. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  10.  
  11. [b](FILE_NO_Update_New_0)[B/]
  12.  
  13. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-0" & [REC_ID]-"10493"
  14. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  15.  
  16. (FILE_NO_Update_New)
  17.  
  18. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-" & [REC_ID]-"10493"
  19. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  20.  
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?
Apr 24 '07 #1
Share this Question
Share on Google+
38 Replies


ADezii
Expert 5K+
P: 8,623
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:
Expand|Select|Wrap|Line Numbers
  1. If Me![REC_ID] <> 0 And Me![FILE_NO] = "" Then
  2.     DoCmd.SetWarnings False
  3.         If Me![REC_ID] - 10493 < 10 Then ' Numeric Value 10493 is last value of rec_id upon year change for file_id renum
  4.         DoCmd.OpenQuery "FILE_NO_Update_New_000", acNormal, acEdit
  5.         GoTo Confirm_File_No_Creation
  6.         End If
  7.  
  8.         If Me![REC_ID] - 10493 < 100 Then
  9.         DoCmd.OpenQuery "FILE_NO_Update_New_00", acNormal, acEdit
  10.         GoTo Confirm_File_No_Creation
  11.         End If
  12.  
  13.         If Me![REC_ID] - 10493 < 1000 Then
  14.         DoCmd.OpenQuery "FILE_NO_Update_New_0", acNormal, acEdit
  15.         GoTo Confirm_File_No_Creation
  16.         End If
  17.  
  18.     DoCmd.OpenQuery "FILE_NO_Update_New", acNormal, acEdit
  19.  
Here are the update queries:
Expand|Select|Wrap|Line Numbers
  1. [b](FILE_NO_Update_New_000)[B/]
  2.  
  3. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-000" & [REC_ID]-"10493"
  4. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  5.  
  6. [b](FILE_NO_Update_New_00)[B/]
  7.  
  8. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-00" & [REC_ID]-"10493"
  9. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  10.  
  11. [b](FILE_NO_Update_New_0)[B/]
  12.  
  13. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-0" & [REC_ID]-"10493"
  14. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  15.  
  16. (FILE_NO_Update_New)
  17.  
  18. UPDATE SC_NEW SET SC_NEW.FILE_NO = Year(Date()) & "-" & [REC_ID]-"10493"
  19. WHERE ((([Forms]![Add New Orders]![REC_ID])=[SC_NEW]![REC_ID]));
  20.  
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fFindHighestREC_ID() As Long
  2. Dim lngID_1 As Long, lngID_2 As Long
  3.  
  4. ID_1 = DLast("[REC_ID]", "SC_OPEN")
  5. ID_2 = DLast("[REC_ID]", "SC_ARCH")
  6.  
  7. If ID_1 > ID_2 Then
  8.   fFindHighestREC_ID = ID_1
  9. ElseIf ID_1 < ID_2 Then
  10.   fFindHighestREC_ID = ID_2
  11. Else    'both equal, assign to either one
  12.   fFindHighestREC_ID = ID_1
  13. End If
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. If Year(Now()) = DLookup("[Current Year]", "tblCurrentYear") Then
  2.    'do nothing, the year is the same
  3. Else      'a New Year
  4.    'run an UPDATE Query to Reset [FILE_NO] to Year(Now()) & "-0001"
  5. End If
Apr 25 '07 #2

tdw
100+
P: 206
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:
Expand|Select|Wrap|Line Numbers
  1. Public Function fFindHighestREC_ID() As Long
  2. Dim lngID_1 As Long, lngID_2 As Long
  3.  
  4. ID_1 = DLast("[REC_ID]", "SC_OPEN")
  5. ID_2 = DLast("[REC_ID]", "SC_ARCH")
  6.  
  7. If ID_1 > ID_2 Then
  8.   fFindHighestREC_ID = ID_1
  9. ElseIf ID_1 < ID_2 Then
  10.   fFindHighestREC_ID = ID_2
  11. Else    'both equal, assign to either one
  12.   fFindHighestREC_ID = ID_1
  13. End If
  14. 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:
Expand|Select|Wrap|Line Numbers
  1. If Year(Now()) = DLookup("[Current Year]", "tblCurrentYear") Then
  2.    'do nothing, the year is the same
  3. Else      'a New Year
  4.    'run an UPDATE Query to Reset [FILE_NO] to Year(Now()) & "-0001"
  5. 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?
Apr 25 '07 #3

tdw
100+
P: 206
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.
May 1 '07 #4

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 1 '07 #5

tdw
100+
P: 206
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. :-)
May 2 '07 #6

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 2 '07 #7

tdw
100+
P: 206
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?
Expand|Select|Wrap|Line Numbers
  1. Dim stHighest As Integer
  2. Dim stHighOpen As Integer
  3. Dim stHighArch As Integer
  4.  
  5.     '   Check to see if this is the first order of the year
  6.     If DCount([FILE_NO], "SC_OPEN", Left([FILE_NO], 4) = Year(DATE)) = 0 Then
  7.         If DCount([FILE_NO], "SC_ARCH", Left([FILE_NO], 4) = Year(DATE)) = 0 Then
  8.             Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
  9.         End If
  10.     Else
  11.         '   Find the highest file number for the current year from both tables
  12.         stHighOpen = DMax(Right([FILE_NO], 4), "SC_OPEN", Left([FILE_NO], 4) = Year(DATE))
  13.         stHighArch = DMax(Right([FILE_NO], 4), "SC_ARCH", Left([FILE_NO], 4) = Year(DATE))
  14.         '   Pick the one that is highest
  15.             If stHighOpen < stHighArch Then
  16.                 stHighest = stHighArch
  17.             Else
  18.                 stHighest = stHighOpen
  19.             End If
  20.  
  21.         '   Create new file number
  22.         Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
  23.     End If
  24.  
May 19 '07 #8

NeoPa
Expert Mod 15k+
P: 31,418
Look at line #7
Expand|Select|Wrap|Line Numbers
  1. 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 (")) :
Expand|Select|Wrap|Line Numbers
  1. 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.
May 19 '07 #9

tdw
100+
P: 206
tdw
Look at line #7
Expand|Select|Wrap|Line Numbers
  1. 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 (")) :
Expand|Select|Wrap|Line Numbers
  1. 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!
May 20 '07 #10

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 20 '07 #11

tdw
100+
P: 206
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"
Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_Order_File_Structure_Click()
  2. On Error GoTo Err_Create_Order_File_Structure_Click
  3.  
  4. '   Yes No Box
  5.     Dim Msg, Style, Title, Response
  6.     Msg = "Are you sure you want to Create this new Open Order?"    ' Define message.
  7.     Style = vbYesNo   ' Define buttons.
  8.     Title = "Create Order"    ' Define title.
  9.     Response = MsgBox(Msg, Style, Title)
  10. If Response = vbYes Then    ' User chose Yes
  11.  
  12.     RunCommand acCmdSaveRecord
  13.  
  14. MsgBox ("Step 1: save record accomplished")
  15.  
  16. Dim stHighest As Integer
  17. Dim stHighOpen As Integer
  18. Dim stHighArch As Integer
  19.  
  20. MsgBox ("Step 2: Dim variables as integers accomplished")
  21.  
  22.     '   Check to see if this is the first order of the year
  23.     If DCount("[FILE_NO]", "SC_OPEN", "Left([FILE_NO], 4) = Year(DATE)") < 1 Then
  24.  
  25. MsgBox ("Step 3a: first order of year in open? found true")
  26.  
  27.         If DCount("[FILE_NO]", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)") < 1 Then
  28.             Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
  29.  
  30. MsgBox ("Step 3b: first order of year in arch? found true. accomplished")
  31.  
  32.         End If
  33.     Else
  34.  
  35. MsgBox ("Step 3c: not found to be first order of year")
  36.  
  37.         '   Find the highest file number for the current year from both tables
  38.         stHighOpen = DLast("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Year(DATE)")
  39.  
  40. MsgBox ("Step 4a: find highest number in open accomplished")
  41.  
  42.         stHighArch = DLast("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Year(DATE)")
  43.  
  44. MsgBox ("Step 4b: find highest number in arch accomplished")
  45.  
  46.         '   Pick the one that is highest
  47.             If stHighOpen < stHighArch Then
  48.                 stHighest = stHighArch
  49.  
  50. MsgBox ("Step 4c: found open to be lower than arch")
  51.  
  52.             Else
  53.                 stHighest = stHighOpen
  54.  
  55. MsgBox ("Step 4d: found open to be higher than arch")
  56.  
  57.             End If
  58.  
  59.         '   Create new file number
  60.         Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
  61.  
  62. MsgBox ("Step 5: add 1 to highest number accomplished")
  63.  
  64.     End If
  65.  
  66. Confirm_File_No_Creation:
  67.     DoCmd.SetWarnings True
  68.     MsgBox ("The File Number Has Been Created For The Order")
  69.  
May 20 '07 #12

NeoPa
Expert Mod 15k+
P: 31,418
Sorry to take so long.
I couldn't see for looking :
Left([FILE_NO], 4) is alpha and Year(DATE) is numeric.
Try instead :
Expand|Select|Wrap|Line Numbers
  1. If DCount("[FILE_NO]", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE, 'yyyy')") < 1 Then
May 21 '07 #13

tdw
100+
P: 206
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 :
Expand|Select|Wrap|Line Numbers
  1. 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.
May 21 '07 #14

tdw
100+
P: 206
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!
May 21 '07 #15

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 22 '07 #16

tdw
100+
P: 206
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.
May 22 '07 #17

NeoPa
Expert Mod 15k+
P: 31,418
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
May 22 '07 #18

tdw
100+
P: 206
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. :-)
May 29 '07 #19

NeoPa
Expert Mod 15k+
P: 31,418
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.
May 30 '07 #20

tdw
100+
P: 206
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!
May 30 '07 #21

NeoPa
Expert Mod 15k+
P: 31,418
Ok, no problem, and no rush. Thank you very much!
Try (don't hold breath) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Jun 1 '07 #22

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 1 '07 #23

tdw
100+
P: 206
tdw
Try (don't hold breath) :
Expand|Select|Wrap|Line Numbers
  1. 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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub Create_Order_File_Structure_Click()
  2. On Error GoTo Err_Create_Order_File_Structure_Click
  3.  
  4. '   Yes No Box
  5.     Dim Msg, Style, Title, Response
  6.     Msg = "Are you sure you want to Create this new Open Order?"    ' Define message.
  7.     Style = vbYesNo   ' Define buttons.
  8.     Title = "Create Order"    ' Define title.
  9.     Response = MsgBox(Msg, Style, Title)
  10. If Response = vbYes Then    ' User chose Yes
  11.  
  12.     RunCommand acCmdSaveRecord
  13.  
  14. MsgBox ("Step 1: save record accomplished")
  15.  
  16. Dim stHighest As Integer
  17. Dim stHighOpen As Integer
  18. Dim stHighArch As Integer
  19.  
  20. MsgBox ("Step 2: Dim variables as integers accomplished")
  21.  
  22.     '   Check to see if this is the first order of the year
  23.  
  24.     If DCount("*", "SC_OPEN", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then
  25. MsgBox ("Step 3a: first order of year in open? found true")
  26.  
  27.         If DCount("*", "SC_ARCH", "Left([FILE_NO],4)=Format(Date(),'yyyy')") = 0 Then
  28.             Me.FILE_NO = Year(DATE) & "-0001"   '   Reset file number to 1
  29.  
  30. MsgBox ("Step 3b: first order of year in arch? found true. accomplished")
  31.  
  32.         End If
  33.     Else
  34.  
  35. MsgBox ("Step 3c: not found to be first order of year")
  36.  
  37.         '   Find the highest file number for the current year from both tables
  38.         stHighOpen = DLast("Right([FILE_NO], 4)", "SC_OPEN", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')")
  39.  
  40. MsgBox ("Step 4a: find highest number in open accomplished")
  41.  
  42.         stHighArch = DLast("Right([FILE_NO], 4)", "SC_ARCH", "Left([FILE_NO], 4) = Format(DATE(), 'yyyy')")
  43.  
  44. MsgBox ("Step 4b: find highest number in arch accomplished")
  45.  
  46.         '   Pick the one that is highest
  47.             If stHighOpen < stHighArch Then
  48.                 stHighest = stHighArch
  49.  
  50. MsgBox ("Step 4c: found open to be lower than arch")
  51.  
  52.             Else
  53.                 stHighest = stHighOpen
  54.  
  55. MsgBox ("Step 4d: found open to be higher than arch")
  56.  
  57.             End If
  58.  
  59.         '   Create new file number
  60.         If stHighest > 1 And stHighest < 10 Then
  61.             Me.FILE_NO = Year(DATE) & "-000" & stHighest + 1
  62.         End If
  63.  
  64.         If stHighest > 9 And stHighest < 100 Then
  65.             Me.FILE_NO = Year(DATE) & "-00" & stHighest + 1
  66.         End If
  67.  
  68.         If stHighest > 99 And stHighest < 1000 Then
  69.             Me.FILE_NO = Year(DATE) & "-0" & stHighest + 1
  70.         End If
  71.  
  72.         If stHighest > 999 Then
  73.             Me.FILE_NO = Year(DATE) & "-" & stHighest + 1
  74.         End If
  75.  
  76. MsgBox ("Step 5: add 1 to highest number accomplished")
  77.  
  78.     End If
  79.  
  80. Confirm_File_No_Creation:
  81.     DoCmd.SetWarnings True
  82.     MsgBox ("The File Number Has Been Created For The Order")
  83.  
  84.     '   Create Folder on O: drive
  85.     stFileNumber = [FILE_NO]
  86.     stPathName = "O:\" & [FILE_NO]
  87.     stFileName = [FILE_NO] & ".rtf"
  88.     stPFName = stPathName & "\" & stFileName
  89.  
  90.     If Dir$(stPathName, vbDirectory) <> "" Then  ' Does Path Already Exist?
  91.         MsgBox ("The directory " & stPathName & " Already exits")
  92.         GoTo Exit_Create_Order_File_Structure_Click
  93.     Else
  94.         MkDir Path:=(stPathName)   '  Create Directory Structure
  95.     End If
  96.  
  97.  
  98. '   Create Report File
  99.     DoCmd.OutputTo acOutputReport, "File Creation", acFormatRTF, stPFName
  100.     MsgBox ("The " & stPathName & " folder and the file " & stFileName & " have been created")
  101.  
  102. Else    ' User chose No.
  103.     GoTo Exit_Create_Order_File_Structure_Click
  104. End If
  105.  
  106. '   Query Name variable definitions
  107.     Dim stQName As String
  108.     Dim stQName1 As String
  109.     stQName = "APPEND NEW RECORD TO OPEN" 'Named Database Query
  110.     stQName1 = "Delete From New"  'Named Database Query
  111.  
  112.     DoCmd.SetWarnings True
  113.     DoCmd.OpenQuery stQName, acNormal, acEdit   'Run Query
  114.     'DoCmd.SetWarnings True
  115.  
  116. '   Yes No Box
  117.     Msg = "Do you want to Print " & stFileNumber & "?"    ' Define message.
  118.     Style = vbYesNo   ' Define buttons.
  119.     Title = "Print New Order"    ' Define title.
  120.     Response = MsgBox(Msg, Style, Title)
  121. If Response = vbYes Then    ' User chose Yes
  122.     DoCmd.RunMacro ("Print New Order") '   Print New Order
  123. End If
  124.  
  125.     DoCmd.SetWarnings True
  126.     DoCmd.OpenQuery stQName1, acNormal, acEdit  'Run Query
  127.     DoCmd.Requery   'Refresh Form
  128.     'DoCmd.SetWarnings True
  129. Exit_Create_Order_File_Structure_Click:
  130.     Exit Sub
  131.  
  132. Err_Create_Order_File_Structure_Click:
  133.     MsgBox Err.Description
  134.     Resume Exit_Create_Order_File_Structure_Click
  135. End Sub
  136.  
Jun 1 '07 #24

NeoPa
Expert Mod 15k+
P: 31,418
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?
Jun 2 '07 #25

tdw
100+
P: 206
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.....
Jun 4 '07 #26

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. 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).
Jun 5 '07 #27

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 5 '07 #28

tdw
100+
P: 206
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!) :-)
Jun 7 '07 #29

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jun 7 '07 #30

tdw
100+
P: 206
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
Expand|Select|Wrap|Line Numbers
  1. Val(Nz(Mid(DMax("[FILE_NO]","[SC_OPEN]","[FILE_NO] Like Format(Date(),'yyyy') & '*'"),6),"0"))+1
  2.  
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! :-)
Jun 22 '07 #31

tdw
100+
P: 206
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.
Jun 22 '07 #32

NeoPa
Expert Mod 15k+
P: 31,418
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourSub()
  2.   Dim lngOpen As Long, lngMax As Long
  3.  
  4.   lngOpen = Val(Nz(Mid(DMax("[FILE_NO]", _
  5.                             "[SC_OPEN]", _
  6.                             "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
  7.                    "0")) + 1
  8.   lngMax = Val(Nz(Mid(DMax("[FILE_NO]", _
  9.                            "[SC_ARCH]", _
  10.                            "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
  11.                   "0")) + 1
  12.   If lngOpen > lngMax Then lngMax = lngOpen
  13. 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.
Jun 22 '07 #33

tdw
100+
P: 206
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 :
Expand|Select|Wrap|Line Numbers
  1. Private Sub YourSub()
  2.   Dim lngOpen As Long, lngMax As Long
  3.  
  4.   lngOpen = Val(Nz(Mid(DMax("[FILE_NO]", _
  5.                             "[SC_OPEN]", _
  6.                             "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
  7.                    "0")) + 1
  8.   lngMax = Val(Nz(Mid(DMax("[FILE_NO]", _
  9.                            "[SC_ARCH]", _
  10.                            "[FILE_NO] Like Format(Date(), 'yyyy\*')"), 6), _
  11.                   "0")) + 1
  12.   If lngOpen > lngMax Then lngMax = lngOpen
  13. 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.
Jun 23 '07 #34

NeoPa
Expert Mod 15k+
P: 31,418
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 :)
Jun 23 '07 #35

tdw
100+
P: 206
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.
Jun 26 '07 #36

NeoPa
Expert Mod 15k+
P: 31,418
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).
Jun 28 '07 #37

tdw
100+
P: 206
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. :-)
Jul 2 '07 #38

NeoPa
Expert Mod 15k+
P: 31,418
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.
Jul 2 '07 #39

Post your reply

Sign in to post your reply or Sign up for a free account.