Next Record after update

[DCV0204]

Ok, I have tried to figure this out with no luck. I either get Runtime Error 2105 or 13.


I have a bound textbox "ReviewedBy" on a from with a query "InitialPrescriberReview" recordsource, I am trying to on AfterUpdate (ReviewedBy) ask a question as to whether to save changes or not.
If yes, requery the record set and go to the next record.
If no, remove what was entered into "ReviewedBy" and stay on the same record to make changes.

My code works unless I select "No" and then update the form enter the reviewedBy and it errors on the acnext.
Here is my code (I left some of the things I have tried as comments.)

Private Sub ReviewedBy_AfterUpdate()
Dim Msg, Style, Title
Msg = "Are you sure you have finished the review of this prescriber?" & vbNewLine & vbNewLine & " Yes, will remove this prescriber from the form" & vbNewLine & " No, will let you update additional information for the prescriber."
Style = vbYesNo + vbCritical + vbDefaultButton1
Title = "Critical"
Response = MsgBox(Msg, Style, Title)
If Response = vbYes Then 'User chose Yes.
MyString = "Yes"


Me.cmdnxt.SetFocus
Me.ReviewedBy.Enabled = False
Me.Form.Requery
Me.Form.SetFocus
DoCmd.GoToRecord , , acNext
DoCmd.GoToControl "iC"
'DoCmd.GoToRecord "InitialPrescriberReview", , acNext
'Me.Form.SetFocus
Me.Requery



'DoCmd.GoToRecord acActiveDataObject, , acNext


Else 'User chose No.
'Me.Refresh
Me.Undo
Me.ReviewedBy = Null
Me.Form.SetFocus
Me.Requery
'Me.iC.SetFocus
'DoCmd.GoToRecord acActiveDataObject, , acNext
'DoCmd.GoToRecord acActiveDataObject, , acPrevious
End If
End Sub

[ItsMe]

Maybe place your code in the BeforeUpdate event and use Cancel = True ? I would be careful employing Undo perhaps Cancel = True will suffice.

[Xipooo]

Since you are binding the form to the data, updates will happen unless you cancel them in the "Before Update" event as ItsMe suggested.

I also don't see any need to requery if you have multiple records in your form. You should just move on to the next record. A requery should only happen if your code is changing the data behind the form and in general this is a no no anyway.

Personally, I have found myself avoiding binding forms to queries more and more. They are a neat RAD feature of Access but it has some downsides. If you instead handle your CRUD through SQL queries and value setting from recordsets (which is what you do in VB.NET), you will have more flexibility in user output/input and record navigation.

It has been a valuable step in my learning process as I move away from Access development and into the more powerful languages like VB.NET, C#, PHP, and Javascript.

[June7]

Yes, use the BeforeUpdate event. Cancel = True will abort the record commitment but probably still want to Undo the control input.

[ItsMe]

... probably still want to Undo the control input.

Perhaps I am just being superstitious but I prefer to assign whatever the default value is back to the field via the control.

me.ReviewedBy = ""
or
ReviewedBy = 0
or null, etc