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

DoCmd.GoToRecord , , acNewRec

P: 5
I have been using this forum to answer many questions regarding an Access database I am building (my first attempt at something like this - teaching myself as I go), and I've always been able to find solutions. My problem now has been addressed in other posts, but none of the solutions given are working for me.

The database will be used for tracking changes that I make to a customer's pricing, with forms based on queries that will allow me to review these changes at a determined interval, decide if the change is paying off, and then either Leave it, Change it Back, Modify It, or Give it More Time (review it again in the future).

The issue I am having now just popped up - it was working as of 2 days ago, but something changed and now it's not.

I have a form that calls a query, ReviewSearchRecords, that shows records that are due to be reviewed this week, based on information put into the original record. On this form, the user inputs DateReviewed, ResultsSinceChange(memo), ActionToTake(4 choices based on a combo box), and ActionNotes(memo). Depending on which ActionToTake is chosen, different things will occur, messages pop up, etc.

The problem is with ActionToTake="Give It More Time". I have set it up so that when this selected and the record is saved, a new record will be inserted into the table with much of the same data as the original, with some data altered per user input via an InputBox, etc.

As I mentioned, this was working fine until recently. Now, I am receiving a RunTime 2105 You Can't Go To Specified Record, and the debugger takes me to the DoCmd.GoToRecord , , acNewRec line.

To rule things out based on what I've read in other posts - the form is set to allow additions. I had most of the fields that were displayed set to Locked=Yes on the form because I didn't want these to be changed via this form. To be sure, I set all these to No, but still received same error.


I am running Access 2000. Any help would be appreciated. I am posting my entire BeforeUpdate code, even though some of it may not be needed.

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2. If MsgBox("Save Changes?", vbYesNo + vbQuestion) = vbNo Then
  3. Cancel = True
  4. MsgBox ("Changes not saved.  Click Undo Changes to return record to original settings, or save the record")
  5. GoTo exitForm_BeforeUpdate
  6. Else
  7. If ActionToTake = "Give It More Time" Then
  8.  
  9. Dim promptWeeks As String
  10. promptWeeks = InputBox("Enter number of additional weeks until next review", , "8")
  11.  
  12. If promptWeeks = "" Then
  13.  
  14. MsgBox ("If you want to have this record come up for review again in the future, you must enter the number of weeks until the next review")
  15. GoTo exitForm_BeforeUpdate
  16. End If
  17.  
  18. sStoreGroup = StoreGroup
  19. sDate = DateReviewed
  20. sWeeksToReview = WeeksToReview
  21. sAccount = Account
  22. sGLID = GLID
  23. sRequestFrom = RequestFrom
  24. sAccountToCopy = AccountToCopy
  25. sOldDG = OldDG
  26. sNewDG = NewDG
  27. sLine1 = Line1
  28. sUD1 = UD1
  29. sLine2 = Line2
  30. sUD2 = UD2
  31. sLine3 = Line3
  32. sUD3 = UD3
  33. sLine4 = Line4
  34. sUD4 = UD4
  35. sLine5 = Line5
  36. sUD5 = UD5
  37. sNotes = Notes
  38.  
  39.  
  40. DoCmd.GoToRecord , , acNewRec
  41.  
  42. StoreGroup = sStoreGroup
  43. Date = sDate
  44. WeeksToReview = promptWeeks
  45. Account = sAccount
  46. GLID = sGLID
  47. RequestFrom = sRequestFrom
  48. AccountToCopy = sAccountToCopy
  49. OldDG = sOldDG
  50. NewDG = sNewDG
  51. Line1 = sLine1
  52. UD1 = sUD1
  53. Line2 = sLine2
  54. UD2 = sUD2
  55. Line3 = sLine3
  56. UD3 = sUD3
  57. Line4 = sLine4
  58. UD4 = sUD4
  59. Line5 = sLine5
  60. UD5 = sUD5
  61. Notes = "**Holdover from " & Date & " Review** " & sNotes
  62.  
  63. MsgBox "A New Record Has Been Added with a Date of " & Date & " so the the pricing can be reviewed in the future."
  64. End If
  65.  
  66.  
  67. If ActionToTake = "Change It Back" Then
  68. MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
  69. End If
  70. If ActionToTake = "Modify It" Then
  71. MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
  72. End If
  73. If ActionToTake = "Leave It As Is" Then
  74. MsgBox "Record Updated and finalized."
  75. End If
  76. End If
  77.  
  78. exitForm_BeforeUpdate:
  79. Exit Sub
  80.  
  81. End Sub
  82.  
Feb 1 '08 #1
Share this Question
Share on Google+
2 Replies


Denburt
Expert 100+
P: 1,356
And you said this has been working? Try placing this code in the after update event.

http://kbalertz.com/128195/Commands-...ate-Event.aspx
Feb 1 '08 #2

P: 5
Hi - Thanks for the quick reply.

Yes, it was working at one point, but after reading the link you posted, I guess I'm not sure how.

At one point, I believe I had this loaded into an OnClick event, but decided on the BeforeUpdate so that if the form was dirty, it would force the user to either save or undo before moving to the next record (using the navigation buttons) and just confirming the desire to save the changes if the button was clicked. I am almost positive that I switched this to a BeforeUpdate event some time ago, and that I have had it working (i.e. adding the new record properly) since I did this, but perhaps I am losing my mind (an entirely likely possibility).

To follow up, moving this to the AfterUpdate event made the Add Record part work, however it made the first part of my code useless, i.e. the confirmation that you want to save. To fix this, I split the code up and now have, as a BeforeUpdate event:

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_BeforeUpdate(Cancel As Integer)
  2.  
  3.  
  4. If MsgBox("Save Changes?", vbYesNo + vbQuestion) = vbNo Then
  5. Cancel = True
  6. MsgBox ("Changes not saved.  Click Undo Changes to return record to original settings, or save the record")
  7. GoTo exitForm_BeforeUpdate
  8.  
  9. End If
  10.  
  11. exitForm_BeforeUpdate:
  12. Exit Sub
  13.  
  14.  
  15. End Sub
  16.  
And my AfterUpdate event is:
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.  
  3. If ActionToTake = "Give It More Time" Then
  4.  
  5. Dim promptWeeks As String
  6. promptWeeks = InputBox("Enter number of additional weeks until next review", , "8")
  7.  
  8. If promptWeeks = "" Then
  9.  
  10. MsgBox ("If you want to have this record come up for review again in the future, you must enter the number of weeks until the next review")
  11. GoTo exitForm_AfterUpdate
  12. End If
  13.  
  14. sStoreGroup = StoreGroup
  15. sDate = DateReviewed
  16. sWeeksToReview = WeeksToReview
  17. sAccount = Account
  18. sGLID = GLID
  19. sRequestFrom = RequestFrom
  20. sAccountToCopy = AccountToCopy
  21. sOldDG = OldDG
  22. sNewDG = NewDG
  23. sLine1 = Line1
  24. sUD1 = UD1
  25. sLine2 = Line2
  26. sUD2 = UD2
  27. sLine3 = Line3
  28. sUD3 = UD3
  29. sLine4 = Line4
  30. sUD4 = UD4
  31. sLine5 = Line5
  32. sUD5 = UD5
  33. sNotes = Notes
  34.  
  35. DoCmd.GoToRecord , , acNewRec
  36.  
  37.  
  38. StoreGroup = sStoreGroup
  39. Date = sDate
  40. WeeksToReview = promptWeeks
  41. Account = sAccount
  42. GLID = sGLID
  43. RequestFrom = sRequestFrom
  44. AccountToCopy = sAccountToCopy
  45. OldDG = sOldDG
  46. NewDG = sNewDG
  47. Line1 = sLine1
  48. UD1 = sUD1
  49. Line2 = sLine2
  50. UD2 = sUD2
  51. Line3 = sLine3
  52. UD3 = sUD3
  53. Line4 = sLine4
  54. UD4 = sUD4
  55. Line5 = sLine5
  56. UD5 = sUD5
  57. Notes = "**Holdover from " & Date & " Review** " & sNotes
  58.  
  59. MsgBox "A New Record Has Been Added with a Date of " & Date & " so the the pricing can be reviewed in the future."
  60.  
  61. End If
  62.  
  63. If ActionToTake = "Change It Back" Then
  64. MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
  65. End If
  66. If ActionToTake = "Modify It" Then
  67. MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
  68. End If
  69. If ActionToTake = "Leave It As Is" Then
  70. MsgBox "Record Updated and finalized."
  71. End If
  72.  
  73. exitForm_AfterUpdate:
  74. Exit Sub
  75.  
  76. End Sub
  77.  
This creates another problem, because once the new record is entered, I can't save the new record because of a 2115 runtime error, which, from the quick searching I have done on it, is because I am trying to save the new record when the BeforeUpdate event has already been triggered (due to saving the original record). I'll try to devise a solution to this and if I can't come up with anything will post a fresh question. This question, regarding the 2105 error, has been answered - thanks for your help.
Feb 1 '08 #3

Post your reply

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