Update Or CancelUpdate without AddNew or Edit error

[gmazza via AccessMonster.com]

Hi there,
I keep getting this error after I update a record, then try moving to another
record.
When I debug, the error comes up where I am setting my modifier date field on
the form
to a certain value in the forms BeforeUpdate event. Is there somewhere else I
can set
this date to prevent this error?
I tried putting it in the AfterUpdate event but then I get this error in my
combo box after update:
You cancelled the previous operation.
With this code in it:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & str(Nz(Me![cboSelectMovie], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The error was on the last line.

I was suggested my database might be corrupt but I went through setting up a
new database with just this form in it and it still does the same thing.
Any help is appreciated!

 

[Dirk Goldgar]

Hi there,
I keep getting this error after I update a record, then try moving to
another
record.
When I debug, the error comes up where I am setting my modifier date field
on
the form
to a certain value in the forms BeforeUpdate event. Is there somewhere
else I
can set
this date to prevent this error?
I tried putting it in the AfterUpdate event but then I get this error in
my
combo box after update:
You cancelled the previous operation.
With this code in it:
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & str(Nz(Me![cboSelectMovie], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

The error was on the last line.

I was suggested my database might be corrupt but I went through setting up
a
new database with just this form in it and it still does the same thing.
Any help is appreciated!

Setting the value of a bound control in the form's BeforeUpdate event should
be okay. You can't do it in the form's AfterUpdate event, because then you
dirty the record immediately after you've saved it, preventing Access from
ever leaving the record. And if the record is being saved because you're
trying to move to a new record, you'd be in an infinite loop unless the
action was cancelled.

So forget about doing it in the AfterUpdate event. We need to figure out
why it isn't working in the BeforeUpdate event. If it isn't due to
corruption, my guess is that the problem isn't with what you're doing in the
BeforeUpdate event itself, but something you're doing elsewhere in the form.
Are you changing any other values in the AfterUpdate event? Is the combo
box bound or unbound (it should be unbound)? What other code do you have in
the form?

 

[gmazza via AccessMonster.com]

This is all the code I have for the form.
The combo box is unbound.
Actually, the Combo box AfterUpdate code has to be the problem because when I
take it
out, change a record, and move to the next record NOT using the combo box but
by using the record selector buttons at the bottom of the screen, I can
change multiple records without getting any error.
Your thoughts?

Private Sub cboSelectMovie_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & str(Nz(Me![cboSelectMovie], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_AfterUpdate()
cboSelectMovie.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

ModifierID = ap_GetUserName
ModifierDate = TimeAndDate

End Sub

Hi there,
I keep getting this error after I update a record, then try moving to

[quoted text clipped - 23 lines]

new database with just this form in it and it still does the same thing.
Any help is appreciated!

Setting the value of a bound control in the form's BeforeUpdate event should
be okay. You can't do it in the form's AfterUpdate event, because then you
dirty the record immediately after you've saved it, preventing Access from
ever leaving the record. And if the record is being saved because you're
trying to move to a new record, you'd be in an infinite loop unless the
action was cancelled.

So forget about doing it in the AfterUpdate event. We need to figure out
why it isn't working in the BeforeUpdate event. If it isn't due to
corruption, my guess is that the problem isn't with what you're doing in the
BeforeUpdate event itself, but something you're doing elsewhere in the form.
Are you changing any other values in the AfterUpdate event? Is the combo
box bound or unbound (it should be unbound)? What other code do you have in
the form?

 

[Dirk Goldgar]

This is all the code I have for the form.
The combo box is unbound.
Actually, the Combo box AfterUpdate code has to be the problem because
when I
take it
out, change a record, and move to the next record NOT using the combo box
but
by using the record selector buttons at the bottom of the screen, I can
change multiple records without getting any error.
Your thoughts?

Private Sub cboSelectMovie_AfterUpdate()
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[MovieID] = " & str(Nz(Me![cboSelectMovie], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Private Sub Form_AfterUpdate()
cboSelectMovie.Requery
End Sub

Private Sub Form_BeforeUpdate(Cancel As Integer)

ModifierID = ap_GetUserName
ModifierDate = TimeAndDate

End Sub

Hmm. I do see one thing that is wrong, but I don't know if that's to blame
for the error or not. The wizard-generated code for your combo box's
AfterUpdate event is wrong, if your combo box is not in an ADP (Access Data
Project). This is a known bug in the wizard. If you are working in an MDB
or ACCDB file, then the recordset's .EOF property is not what you should be
checking to see if the FindFirst method was successful. You need to test
the .NoMatch proeprty instead. Try this code instead:

'----- start of revised code -----
Private Sub cboSelectMovie_AfterUpdate()

If Not IsNull(Me!cboSelectMovie) Then

With Me.RecordsetClone
.FindFirst "MovieID = " & Me!cboSelectMovie
If Not .NoMatch Then Me.Bookmark = Bookmark
End With

End If

End Sub
'----- end of revised code -----

Now, that code assumes two things:

1. MovieID is a numeric field. If MovieID is a text field, change the
..FindFirst line to surround the selected value with quotes, like this:

.FindFirst "MovieID = '" & Me!cboSelectMovie & "'"

2. The Bound Column of cboSelectMovie is the column that contains MovieID.
If that's not the case, make it so.

 

[gmazza via AccessMonster.com]

Ok I copied your revised code over my other code.
I am working with an ACCDB file.

Its not updating with the correct record when I choose a movie title from the
combo box.
The record source of my combo box is:
SELECT Movie.MovieID, Movie.Title FROM Movie Order by Title Asc;
Bound Column is set to 1

I still want the user to choose the movie title from the combo box, yet its
still bound to the MovieID with this code correct?

This is all the code I have for the form.
The combo box is unbound.

[quoted text clipped - 24 lines]

End Sub

Hmm. I do see one thing that is wrong, but I don't know if that's to blame
for the error or not. The wizard-generated code for your combo box's
AfterUpdate event is wrong, if your combo box is not in an ADP (Access Data
Project). This is a known bug in the wizard. If you are working in an MDB
or ACCDB file, then the recordset's .EOF property is not what you should be
checking to see if the FindFirst method was successful. You need to test
the .NoMatch proeprty instead. Try this code instead:

'----- start of revised code -----
Private Sub cboSelectMovie_AfterUpdate()

If Not IsNull(Me!cboSelectMovie) Then

With Me.RecordsetClone
.FindFirst "MovieID = " & Me!cboSelectMovie
If Not .NoMatch Then Me.Bookmark = Bookmark
End With

End If

End Sub
'----- end of revised code -----

Now, that code assumes two things:

1. MovieID is a numeric field. If MovieID is a text field, change the
.FindFirst line to surround the selected value with quotes, like this:

.FindFirst "MovieID = '" & Me!cboSelectMovie & "'"

2. The Bound Column of cboSelectMovie is the column that contains MovieID.
If that's not the case, make it so.

 

[Dirk Goldgar]

Ok I copied your revised code over my other code.
I am working with an ACCDB file.

Its not updating with the correct record when I choose a movie title from
the
combo box.
The record source of my combo box is:
SELECT Movie.MovieID, Movie.Title FROM Movie Order by Title Asc;
Bound Column is set to 1

I still want the user to choose the movie title from the combo box, yet
its
still bound to the MovieID with this code correct?

Are you saying that you are both using this combo box for navigation *and*
and have it bound to the MovieID field in your form's recordset? You'd need
special code, which I don't think you have in place, to make that work.
Much easier is to use an unbound combo box for navigation, and some other
control -- if you need one at all -- to display the MovieID of the current
record.

Is MovieID an autonumber field?

 

[gmazza via AccessMonster.com]

No, sorry, its just bound to the MovieID, I just meant that when I navigate
to another record I can just as well use the combo box to choose another
movie title as that will take me to another record.
I don't display the movieid at all, and yes it is an autonumber field.

Ok I copied your revised code over my other code.
I am working with an ACCDB file.

[quoted text clipped - 9 lines]

its
still bound to the MovieID with this code correct?

Are you saying that you are both using this combo box for navigation *and*
and have it bound to the MovieID field in your form's recordset? You'd need
special code, which I don't think you have in place, to make that work.
Much easier is to use an unbound combo box for navigation, and some other
control -- if you need one at all -- to display the MovieID of the current
record.

Is MovieID an autonumber field?

 

[Dirk Goldgar]

No, sorry, its just bound to the MovieID, I just meant that when I
navigate
to another record I can just as well use the combo box to choose another
movie title as that will take me to another record.
I don't display the movieid at all, and yes it is an autonumber field.

I'm sorry, I'm not sure that I understand you. What are the following
properties of the combo box:

ControlSource
RowSource
RowSourceType
BoundColumn
ColumnCount

?

 

[gmazza via AccessMonster.com]

Sorry for this, here you go in the order you have:
no control source
SELECT Movie.MovieID, Movie.Title FROM Movie Order by Title Asc;
Table/Query
Bound Column: 1
Column Count: 2

 

[Dirk Goldgar]

Sorry for this, here you go in the order you have:
no control source
SELECT Movie.MovieID, Movie.Title FROM Movie Order by Title Asc;
Table/Query
Bound Column: 1
Column Count: 2

Thanks, that looks fine, and puts my mind at ease. You said the combo box
was "bound" to MovieID, which would mean that MovieID was its ControlSource,
but fortunately that was just a misnomer.

I've found a typo in the code I posted for you, and that's probably why that
code isn't working. I originally posted this:

If Not .NoMatch Then Me.Bookmark = Bookmark

But it should have been this:

If Not .NoMatch Then Me.Bookmark = .Bookmark

What was missing was the essential dot (.) before the second "Bookmark". So
please fix that, and try it again.

 

[gmazza via AccessMonster.com]

Thanks for your reply. I applied the change, adding the dot before the book
mark.
I am still getting the same error: Update or CancelUpdate without AddNew or
Edit and its still pointing to the ModifierID = ap_GetUserName in my
Form_BeforeUpdate procedure.

 

[Dirk Goldgar]

Thanks for your reply. I applied the change, adding the dot before the
book
mark.
I am still getting the same error: Update or CancelUpdate without AddNew
or
Edit and its still pointing to the ModifierID = ap_GetUserName in my
Form_BeforeUpdate procedure.

That makes me wonder if your ModifierID field may not be updatable. Is
ModifierID a text box on your form? If so, what is its ControlSource? If
not, then I assume it's a field in your form's recordsource. Is it a
calculated field? What type of field is it?

 

[gmazza via AccessMonster.com]

It is updatable. It is a textbox, with a ControlSource of the same name in
the table. The field is a Text field, and in the code it is calling a
function, ap_GetUserName, and here is the code of the function:

Public Function ap_GetUserName() As String
'Get the NT user name
Dim strUsername As String
Dim lngLength As Long
Dim lngResult As Long

'set up the buffer
strUsername = String$(255, 32)
lngLength = 255

'make the call
lngResult = wu_GetUserName(strUsername, lngLength)

'assign the value
ap_GetUserName = Mid$(strUsername, 1, Len(Trim$(strUsername)) - 1)

End Function

I should mention that this field is invisible if that matters.
Thanks Mr. Goldgar

Thanks for your reply. I applied the change, adding the dot before the
book

[quoted text clipped - 3 lines]

Edit and its still pointing to the ModifierID = ap_GetUserName in my
Form_BeforeUpdate procedure.

That makes me wonder if your ModifierID field may not be updatable. Is
ModifierID a text box on your form? If so, what is its ControlSource? If
not, then I assume it's a field in your form's recordsource. Is it a
calculated field? What type of field is it?

 

[Dirk Goldgar]

It is updatable. It is a textbox, with a ControlSource of the same name in
the table. The field is a Text field, and in the code it is calling a
function, ap_GetUserName, and here is the code of the function:

Public Function ap_GetUserName() As String
'Get the NT user name
Dim strUsername As String
Dim lngLength As Long
Dim lngResult As Long

'set up the buffer
strUsername = String$(255, 32)
lngLength = 255

'make the call
lngResult = wu_GetUserName(strUsername, lngLength)

'assign the value
ap_GetUserName = Mid$(strUsername, 1, Len(Trim$(strUsername)) - 1)

End Function

I should mention that this field is invisible if that matters.

That shouldn't matter, and I've used similar techniques all the time.

I'm assuming that you've properly declared the API function you're calling
as wu_GetUserName, and that you've tested the ap_GetUserName function.

This is perplexing, and I don't see the cause of your error. Would you be
interesting in sending me a copy of your database for investigation? If so,
please make special, cut-down copy, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB in
size (preferably much smaller). I'll have a look at it, time permitting.
You can send it to the address derived by removing NO SPAM and ".invalid"
from the reply address of this message. If that address isn't visible to
you, you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

 

[gmazza via AccessMonster.com]

Ok I have emailed you the db, the function has been declared properly.
At your convenience, let me know what you come up with.
Thanks!

It is updatable. It is a textbox, with a ControlSource of the same name in
the table. The field is a Text field, and in the code it is calling a

[quoted text clipped - 19 lines]

I should mention that this field is invisible if that matters.

That shouldn't matter, and I've used similar techniques all the time.

I'm assuming that you've properly declared the API function you're calling
as wu_GetUserName, and that you've tested the ap_GetUserName function.

This is perplexing, and I don't see the cause of your error. Would you be
interesting in sending me a copy of your database for investigation? If so,
please make special, cut-down copy, containing only the elements necessary
to demonstrate the problem, compacted and then zipped to less than 1MB in
size (preferably much smaller). I'll have a look at it, time permitting.
You can send it to the address derived by removing NO SPAM and ".invalid"
from the reply address of this message. If that address isn't visible to
you, you can get my address from my web site, which is listed in my sig. Do
*not* post my real address in the newsgroup -- I don't want to be buried in
spam and viruses.

 

[Dirk Goldgar]

Ok I have emailed you the db, the function has been declared properly.
At your convenience, let me know what you come up with.

I've found the problem and the solution. Apparently, there's something
about navigating to a new record via the RecordsetClone's Bookmark property
combined with dirtying the form in the form's BeforeUpdate event that Access
really doesn't like. I have never run into this behavior before, but I've
now reproduced it in another database, and in Access 2003 as well as 2007.

I've found two ways to get around this problem. The first, and most
universal, is just to force Access to save the current record before using
the Bookmark property to navigate to another one. Like this:

'----- start of revised code -----
Private Sub cboSelectMovie_AfterUpdate()

If Not IsNull(Me!cboSelectMovie) Then

' Force Access to save the current record.
If Me.Dirty Then Me.Dirty = False

With Me.RecordsetClone
.FindFirst "MovieID = " & Me!cboSelectMovie
If Not .NoMatch Then Me.Bookmark = .Bookmark
End With

End If

End Sub
'----- end of revised code -----

Adding that one line, "If Me.Dirty Then Me.Dirty = False", makes it work for
me.

An alternative approach can also work, if you are using Access 2000 or
later, and you're sure the record being sought exists in the form's
recordset, or you don't need to know if it doesn't. (I believe your case
meets those conditions.) In that case, you can navigate using the form's
recordset directly, not its clone, and the code can be simplified to this:

'----- start of alternative code -----
Private Sub cboSelectMovie_AfterUpdate()

If Not IsNull(Me!cboSelectMovie) Then

Me.Recordset.FindFirst "MovieID = " & Me!cboSelectMovie

End If

End Sub
'----- end of alternative code -----

When I tried that, I found that the error message was not raised, even
though I modified the form's bound controls in its BeforeUpdate event, even
without first forcing a record save.

 

[gmazza via AccessMonster.com]

You are correct, the 2nd case does meet my conditions and the error is now
gone. Great to know for future references. I thank you for your time, and I
can only hope that you never having this happen before has helped you in some
way as well!