Error in Form BeforeUpdate Event

[James Martin]

I have code in my form's BeforeUpdate event that changes the value of one of
the fields on the underlying record.

However, sometimes (but only sometimes) when it gets to the code that
assigns a value to this field, I get the following error:

Run-time error '-2147352567 (800200009)':
Update or CancelUpdate without AddNew or Edit.

When I go to Debug and try to assign a simple value directly, I get the same
error.

It is this one field that isn't accessed anywhere else in the code. It ought
to be straight-forward and most of the time it updates without a problem.

Any suggestions as to the cause of this mysterious error would be greatly
appreciated!

Thanks

James

 

[Allen Browne]

This message can indicate that something else is going on that Access is
unable to complete.

If you have an event procedure running that requires the implicit save of
the record (such as moving record via RecordsetClone, applying a filter,
changing sort order, reassigning recordsource, closing the form, quitting
Access, ...) and the save fails for some reason (required field missing,
duplicte index, validation rule not met, cancelled update, ...), Access can
get confused about what is wrong. You can avoid that situation by explicitly
forcing the record save before you execute anything that requires the
implicit save. My preferred approach is:
If Me.Dirty Then Me.Dirty = False
since that works even if the form does not have focus, and generates a
trappable error if it fails.

If that is not the issue, it is possible that the code is corrupting,
particularly if you are editing the code while the form is open (i.e.
without switching to design view first.) A Decompile will probably fix this
problem, and modifying code only in design view will probably prevent it
recurring.

There are other possible causes, particularly where an event queue triggers
other events and some event in the queue is unable to complete. For example,
if you have any Form_Timer event running, or ...

 

[James Martin]

Hi Allen,

Thanks for the input. Where would you put the If Me.Dirty Then
Me.Dirty=False?

James

 

[Allen Browne]

Place the line before anything you do that could cause Access to have to
save the record.

For examle, if you are using a combo to navigate to a different record, it
would need to save the record before moving, so here's an example:
http://allenbrowne.com/ser-03.html

 

[James Martin]

Thanks Allen, you've solved me problem!

James

Place the line before anything you do that could cause Access to have to
save the record.

For examle, if you are using a combo to navigate to a different record, it
would need to save the record before moving, so here's an example:
http://allenbrowne.com/ser-03.html