3020: Update or CancelUpdate

[GBA]

the essence of my question is why does this statement in the form's
BeforeUpdate event:

me.field = Forms!MainForm.field

work if the form closes....but not work when the form changes record?

when I move to another record using a combobox in the header of the form it
throws the 3020 error.

I've made a work-around by putting a me.refresh into the OnEnter event of
the combo box inside an If Dirty statement......but I don't understand why I
am needing to do this....would welcome insight on this topic.....

 

[Allen Browne]

The problem is caused by the way the events are triggered, in combination
with what you are doing in the events.

The combo's AfterUpdate(?) event moves record in the main form. Before it
can move record, it must save any edits in the main form, which triggers
several events (for the form's events possibly some controls' events too),
and Access may need to reload the subform to reflect any changes (based on
the LinkMasterFields/LinkChildFields properties.)

This reload (or possibly the later one when the main form actually moves
record) triggers the subform's Form_BeforeUpdate event if the subform is
dirty. In this event, you are trying to assign a value to a subform field,
based on a value in the main form (which now has what record? Moved or not?)
This is the point where Access gets confused and spits the dummy.

By explicitly saving the main form record first, you trigger any saving that
needed to take place *before* any attempt is made to move record. IMHO,
explicitly saving like this is always good practice, and will save you other
grief as well.

Assuming the combo is unbound, you could put the explicit save in the
combo's AfterUpdate event, ahead of the code where you move record. That's
how I normally handle it, as in this example:
http://allenbrowne.com/ser-03.html

The final question is why you alter a subform's field in its
Form_BeforeUpdate like that? Would it make more sense to use
Form_BeforeInsert instead? That way, the value gets assigned as soon as the
user starts adding a record in the subform, rather than at the end of the
process. This would also allow you to check that the mainform field has a
suitable value (e.g. not null), and cancel the insert in the subform if the
main form record is not ready to accept a related record in the subform.

Also, avoid dirtying your forms unnecessarily (e.g. never assign a value to
a bound field in Form_Current), as this will avoid a whole string of events
getting fired unnecessarily.

HTH