DoCmd.GoToRecor d , , acNewRec

[aaronyoung]

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, ReviewSearchRec ords, 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, ResultsSinceCha nge(memo), ActionToTake(4 choices based on a combo box), and ActionNotes(mem o). Depending on which ActionToTake is chosen, different things will occur, messages pop up, etc.

The problem is with ActionToTake="G ive 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.GoToRecor d , , 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.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If MsgBox("Save Changes?", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
MsgBox ("Changes not saved.  Click Undo Changes to return record to original settings, or save the record")
GoTo exitForm_BeforeUpdate
Else
If ActionToTake = "Give It More Time" Then
 
Dim promptWeeks As String
promptWeeks = InputBox("Enter number of additional weeks until next review", , "8")
 
If promptWeeks = "" Then
 
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")
GoTo exitForm_BeforeUpdate
End If
 
sStoreGroup = StoreGroup
sDate = DateReviewed
sWeeksToReview = WeeksToReview
sAccount = Account
sGLID = GLID
sRequestFrom = RequestFrom
sAccountToCopy = AccountToCopy
sOldDG = OldDG
sNewDG = NewDG
sLine1 = Line1
sUD1 = UD1
sLine2 = Line2
sUD2 = UD2
sLine3 = Line3
sUD3 = UD3
sLine4 = Line4
sUD4 = UD4
sLine5 = Line5
sUD5 = UD5
sNotes = Notes
 
 
DoCmd.GoToRecord , , acNewRec
 
StoreGroup = sStoreGroup
Date = sDate
WeeksToReview = promptWeeks
Account = sAccount
GLID = sGLID
RequestFrom = sRequestFrom
AccountToCopy = sAccountToCopy
OldDG = sOldDG
NewDG = sNewDG
Line1 = sLine1
UD1 = sUD1
Line2 = sLine2
UD2 = sUD2
Line3 = sLine3
UD3 = sUD3
Line4 = sLine4
UD4 = sUD4
Line5 = sLine5
UD5 = sUD5
Notes = "**Holdover from " & Date & " Review** " & sNotes
 
MsgBox "A New Record Has Been Added with a Date of " & Date & " so the the pricing can be reviewed in the future."
End If
 
 
If ActionToTake = "Change It Back" Then
MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
End If
If ActionToTake = "Modify It" Then
MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
End If
If ActionToTake = "Leave It As Is" Then
MsgBox "Record Updated and finalized."
End If
End If
 
exitForm_BeforeUpdate:
Exit Sub
 
End Sub
 

[Denburt]

And you said this has been working? Try placing this code in the after update event.

http://kbalertz.com/128195/Commands-...ate-Event.aspx

[aaronyoung]

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:

Private Sub Form_BeforeUpdate(Cancel As Integer)
 
 
If MsgBox("Save Changes?", vbYesNo + vbQuestion) = vbNo Then
Cancel = True
MsgBox ("Changes not saved.  Click Undo Changes to return record to original settings, or save the record")
GoTo exitForm_BeforeUpdate
 
End If
 
exitForm_BeforeUpdate:
Exit Sub
 
 
End Sub
 

And my AfterUpdate event is:

Private Sub Form_AfterUpdate()
 
If ActionToTake = "Give It More Time" Then
 
Dim promptWeeks As String
promptWeeks = InputBox("Enter number of additional weeks until next review", , "8")
 
If promptWeeks = "" Then
 
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")
GoTo exitForm_AfterUpdate
End If
 
sStoreGroup = StoreGroup
sDate = DateReviewed
sWeeksToReview = WeeksToReview
sAccount = Account
sGLID = GLID
sRequestFrom = RequestFrom
sAccountToCopy = AccountToCopy
sOldDG = OldDG
sNewDG = NewDG
sLine1 = Line1
sUD1 = UD1
sLine2 = Line2
sUD2 = UD2
sLine3 = Line3
sUD3 = UD3
sLine4 = Line4
sUD4 = UD4
sLine5 = Line5
sUD5 = UD5
sNotes = Notes
 
DoCmd.GoToRecord , , acNewRec
 
 
StoreGroup = sStoreGroup
Date = sDate
WeeksToReview = promptWeeks
Account = sAccount
GLID = sGLID
RequestFrom = sRequestFrom
AccountToCopy = sAccountToCopy
OldDG = sOldDG
NewDG = sNewDG
Line1 = sLine1
UD1 = sUD1
Line2 = sLine2
UD2 = sUD2
Line3 = sLine3
UD3 = sUD3
Line4 = sLine4
UD4 = sUD4
Line5 = sLine5
UD5 = sUD5
Notes = "**Holdover from " & Date & " Review** " & sNotes
 
MsgBox "A New Record Has Been Added with a Date of " & Date & " so the the pricing can be reviewed in the future."
 
End If
 
If ActionToTake = "Change It Back" Then
MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
End If
If ActionToTake = "Modify It" Then
MsgBox "Record updated, but not finalized.  Please use the Action Required form from the Switchboard after feedback from the field to finalize this record."
End If
If ActionToTake = "Leave It As Is" Then
MsgBox "Record Updated and finalized."
End If
 
exitForm_AfterUpdate:
Exit Sub
 
End Sub
 

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.