before update event triggered unexpectedly

[Ptero]

In order that I can build a change history and 'track back' to a record's
contents at any particular time I have created a form which allows the adding
of new records only.

The form is set to data entry, allows filters and edits and contains a
subform in the header section which lists all of the records in the table.
The detail part of the form contains the fields to be updated. (allow filters
and edits are required to allow records in the subform to be filtered and
edited, but all of the fields are set to enabled=true, locked=false)

When one of the records in the subform is double clicked, vba code populates
the new fields on the main form with the same information as the selected
record. (Ready to be modified)

If I then double click on another of the records in the subform the new
fields on the main form are repopulated with the new selection as expected.
(This caters for a wrong initial selection)

The problem is that as soon as I click on one of the new fields on the main
form to modify the data the main form before update event is triggered. The
before update event code is simply:

if msgbox("Update?",vbyesno)=vbno then me.undo

I only want the beforeupdate event to be triggered if I change the new field
contents and I don't understand why it is being triggered before I have
changed anything or tried to save the new record (either by clicking the save
button or moving off the main form). I presume it has something to do with
moving from the subform to the main form but don't see why.

Can someone please explain, or is there a better way of doing this?

Many thanks in advance

Ptero

 

[John W. Vinson]

I only want the beforeupdate event to be triggered if I change the new field
contents and I don't understand why it is being triggered before I have
changed anything or tried to save the new record (either by clicking the save
button or moving off the main form). I presume it has something to do with
moving from the subform to the main form but don't see why.

Can someone please explain, or is there a better way of doing this?

Normally a Subform is used for the "many" side of a one to many relationship,
with the mainform being the "one" side. As such, it's essetial that the
mainform record be written to disk before attempting to edit data on the
subform; therefore the mainform record is saved the moment you setfocus to the
subform (and vice versa, the subform record will be saved if you leave the
subform).

You might want to consider a different approach, if the only purpose of the
subform is to select a record. Could you instead use an unbound Combo Box or
Listbox rather than a subform? You could use its AfterUpdate event to navigate
to the selected record.

John W. Vinson [MVP]

 

[Ptero]

I did look at using a list box, but I need to be able to filter and sort the
list, although I guess I can do that with additional fields on the form
changing the recordsource for the listbox. Also the subform uses conditional
formatting which is not essential, but a nice to have.

The object of the exercise is to prevent the addition of unnecessary records
(i.e. where the record remains the same) to the table so I only want to add a
new record when either the record has changed from the original state (either
'null' or 'old') and the user says 'yes' to update.