Using BeforeUpdate event with subforms?

Question

I am trying to verify a form is filled out completely before saving, and if not, I ask the user whether they wish to continue.  I am using the BeforeUpdate event, but I don't think it's the right method. The problem occurs when the user goes to entering data into a subform. The BeforeUpdate event fires (though I don't want it to in this case).

So, basically, I have

Private Sub Form_BeforeUpdate(Cancel as Integer)

If Not formComplete() Then

     response = MsgBox("This form has missing fields.  Do you wish to continue?", vbYesNoCancel)

     If response <> vbYes then

         Cancel = True

         Exit Sub

     End if

End If

End Sub

...where "formComplete" is my function which determines whether all the required fields are entered.  

As I say though, I would like so skip this procedure if the user is simply entering data in a subform (they can't actually have a complete form until they do so).  Suggestions?

Thanks in advance,

Answer 1

Is there a way to (and would it make sense to) determine that all that has happened is that a subform has taken (or has) the focus, and if so to just bail out of the BeforeUpdate event (or skip the form completion check)?

That would be too late in the day to 'bail out'.  In any event 'bailing out' would mean allowing the parent form's current record to be saved by not setting the return value of the Cancel argument to True, which is what you are trying to prevent in the first place.  Once you've done that it won't execute again, so there would be no validation whatsoever.

It would be possible with a little more work to extend the code in my demo form's module to do all the validation, and therefore allow you to avoid the use of the BeforeUpdate event procedure completely.  You'd need to extend the criteria for the DLookup function call so that in addition to testing for a row in families with no match in FamilyMembers by means of the subquery, it also tests for Nulls in any of the mandatory columns in Families.  This would allow you to move to the subform without validating the parent form's controls, but while it would work in practice in the way you envisage, I would not be happy with such a solution as it would mean that the mandatory columns in Families would each have to allow Null in the table definition, prejudicing the integrity of the table.

My demo does not do any validation on Families of course as it is not designed to illustrate this, and Families is just a single column table.