Error 3020 for VB: Update of CancelUpdate without AddNew or Edit?

[Nomij]

I am trying to create a form that pulls up a record from a drop down list. If
the data is modified, a msg box confirms the change, " Yes, No, Cancel".
Everything works except "cancel". When you come back to the form, I get Error
3020 as named above. I've included the code below. Does anyone have a
solustion to this? (Access 2003) Thanks for any help!

Private Sub Combo53_AfterUpdate()
'Find the record that matches the control.
On Error GoTo Err_CommandExit

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Me.Bookmark = Me.Bookmark
Exit Sub


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm changes with user
Dim intAnswer As Integer
intAnswer = MsgBox("The Customer Info has been modified. Do you want to
save your changes?", vbYesNoCancel)
Select Case intAnswer
Case vbYes
Cancel = no
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub

 

[Maverick]

My gut reaction: Why do you want YesNoCancel as opposed to YesNo? Either they
want to save the changes or they don't. Cancel isn't really a valid option.
What is the code supposed to do when cancelled versus saying no to saving? It
seems that cancel leaves your code in limbo with nothing to do.

 

[Nomij]

Thanks, but Cancel is appropriate when you want to continue with the
updating, but not lose what you've already changed, but not save and move to
another record, (ie - cancel record change). I've seen it in many
applications, and used it in many applications, and would like it in my
application.

My gut reaction: Why do you want YesNoCancel as opposed to YesNo? Either they
want to save the changes or they don't. Cancel isn't really a valid option.
What is the code supposed to do when cancelled versus saying no to saving? It
seems that cancel leaves your code in limbo with nothing to do.

I am trying to create a form that pulls up a record from a drop down list. If
the data is modified, a msg box confirms the change, " Yes, No, Cancel".
Everything works except "cancel". When you come back to the form, I get Error
3020 as named above. I've included the code below. Does anyone have a
solustion to this? (Access 2003) Thanks for any help!

Private Sub Combo53_AfterUpdate()
'Find the record that matches the control.
On Error GoTo Err_CommandExit

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Me.Bookmark = Me.Bookmark
Exit Sub


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm changes with user
Dim intAnswer As Integer
intAnswer = MsgBox("The Customer Info has been modified. Do you want to
save your changes?", vbYesNoCancel)
Select Case intAnswer
Case vbYes
Cancel = no
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub

 

[Nomij]

That was *exactly* where the original problem occoured! I tried a few
different solutions, and the error sub was all I could come up with! (I am a
bit of a n00b, but I am enjoying learninig access while creating something
for work). Thank you for your code, it was a huge help!

I am trying to create a form that pulls up a record from a drop down list.
If
the data is modified, a msg box confirms the change, " Yes, No, Cancel".
Everything works except "cancel". When you come back to the form, I get
Error
3020 as named above. I've included the code below. Does anyone have a
solustion to this? (Access 2003) Thanks for any help!

Private Sub Combo53_AfterUpdate()
'Find the record that matches the control.
On Error GoTo Err_CommandExit

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Me.Bookmark = Me.Bookmark
Exit Sub


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm changes with user
Dim intAnswer As Integer
intAnswer = MsgBox("The Customer Info has been modified. Do you want
to
save your changes?", vbYesNoCancel)
Select Case intAnswer
Case vbYes
Cancel = no
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub

Does this happen when you've modified a record and then use the combo box to
try to navigate to a new record? I can see how your code in
Combo53_AfterUpdate() could make that happen, because your error-handling is
incorrect. Also, the code that tests for the no-match condition, though
it's what the wizard writes, is flat-out wrong for a Jet database.

Try this:

'----- start of suggested code -----
Private Sub Combo53_AfterUpdate()

'Find the record that matches the control.
On Error GoTo Err_CommandExit

' Save the current record if it's Dirty.
If Me.Dirty Then Me.Dirty = False

With Me.Recordset.Clone
.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With


Exit_Point:
Exit Sub

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Resume Exit_Point

End Sub

'----- end of suggested code -----



--
Dirk Goldgar, MS Access MVP
www.datagnostics.com

(please reply to the newsgroup)

 

[Dirk Goldgar]

I am trying to create a form that pulls up a record from a drop down list.
If
the data is modified, a msg box confirms the change, " Yes, No, Cancel".
Everything works except "cancel". When you come back to the form, I get
Error
3020 as named above. I've included the code below. Does anyone have a
solustion to this? (Access 2003) Thanks for any help!

Private Sub Combo53_AfterUpdate()
'Find the record that matches the control.
On Error GoTo Err_CommandExit

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Me.Bookmark = Me.Bookmark
Exit Sub


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm changes with user
Dim intAnswer As Integer
intAnswer = MsgBox("The Customer Info has been modified. Do you want
to
save your changes?", vbYesNoCancel)
Select Case intAnswer
Case vbYes
Cancel = no
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub

Does this happen when you've modified a record and then use the combo box to
try to navigate to a new record? I can see how your code in
Combo53_AfterUpdate() could make that happen, because your error-handling is
incorrect. Also, the code that tests for the no-match condition, though
it's what the wizard writes, is flat-out wrong for a Jet database.

Try this:

'----- start of suggested code -----
Private Sub Combo53_AfterUpdate()

'Find the record that matches the control.
On Error GoTo Err_CommandExit

' Save the current record if it's Dirty.
If Me.Dirty Then Me.Dirty = False

With Me.Recordset.Clone
.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With


Exit_Point:
Exit Sub

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Resume Exit_Point

End Sub

'----- end of suggested code -----

 

[Chris O''Neill]

By the way, it will make it easier for you (and anyone else looking at your
work) if you'd name the controls, variables, and other XXXX using something
logical. For instance, if Combo53 is bound to a text field containing the
Customer's name, naming it "cboCustmerName" tells you that it's a combo box
containing the Customer Name. Combo53 doesn't tell you anything.

For more naming conventions, look here:

http://www.mvps.org/access/general/gen0012.htm

While that doesn't resolve your current issue, it will hopefully make things
a bit easier for you in the future.

Regards, Chris

 

[Chris O''Neill]

LOL! I butchered that reply, but I hope you get the idea.

Chris

By the way, it will make it easier for you (and anyone else looking at your
work) if you'd name the controls, variables, and other XXXX using something
logical. For instance, if Combo53 is bound to a text field containing the
Customer's name, naming it "cboCustmerName" tells you that it's a combo box
containing the Customer Name. Combo53 doesn't tell you anything.

For more naming conventions, look here:

http://www.mvps.org/access/general/gen0012.htm

While that doesn't resolve your current issue, it will hopefully make things
a bit easier for you in the future.

Regards, Chris

I am trying to create a form that pulls up a record from a drop down list. If
the data is modified, a msg box confirms the change, " Yes, No, Cancel".
Everything works except "cancel". When you come back to the form, I get Error
3020 as named above. I've included the code below. Does anyone have a
solustion to this? (Access 2003) Thanks for any help!

Private Sub Combo53_AfterUpdate()
'Find the record that matches the control.
On Error GoTo Err_CommandExit

Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Customer Name] = '" & Me![Combo53] & "'"

If Not rs.EOF Then Me.Bookmark = rs.Bookmark

Err_CommandExit:
Me![Combo53] = Me.Customer_Name
Me.Bookmark = Me.Bookmark
Exit Sub


End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)
'Confirm changes with user
Dim intAnswer As Integer
intAnswer = MsgBox("The Customer Info has been modified. Do you want to
save your changes?", vbYesNoCancel)
Select Case intAnswer
Case vbYes
Cancel = no
Case vbNo
Me.Undo
Case vbCancel
Cancel = True
End Select
End Sub