Subform Validation

[Guest]

I have a form containing a subform and I want to validate some data on the
subform when the main form is updated. When the BeforeUpdate event fires on
the main form, it checks (in VBA code) two fields on the subform and if the
user must change one of them it does a DoCmd.CancelEvent. This stops the
record from being updated, but it doesn't allow the user to the the field on
the subform because when they move to the subform to do it, the BeforeUpdate
event fires again which in turn displays the same error message and cancels
the update. Does anyone know how to get around this or is it enough
information to go on.

Thanks in advance,

 

[Marshall Barton]

I have a form containing a subform and I want to validate some data on the
subform when the main form is updated. When the BeforeUpdate event fires on
the main form, it checks (in VBA code) two fields on the subform and if the
user must change one of them it does a DoCmd.CancelEvent. This stops the
record from being updated, but it doesn't allow the user to the the field on
the subform because when they move to the subform to do it, the BeforeUpdate
event fires again which in turn displays the same error message and cancels
the update. Does anyone know how to get around this or is it enough
information to go on.

I don't think your idea can go anywhere. The subform data
was saved when the focus was moved to the main form. Not
only that, but the main form data was saved when the focus
shifted to the subform.

What the means is that the subform data validation should be
done in the subform's Before Update event where you can
prevent the inconsistent data from being saved. Of course,
some impatient user (or one that does not have the correct
data available) can just terminated the Access task wreaking
all kinds of havoc.

You can also use a MsgBox in the mainform's validation
instead of trying to shift the focus. The message can warn
the user that the values need to be changed. This can also
be done in the main form's Current event so the message is
repeated whenever to user navigates to the problematic
record in the main form. It could also be done in the
subform control's Enter event and the focus set to the
offending control. With all those nags, the user will
probably be sufficiently motivated to actually fix the data
issue.