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

Thanks.  The form their dealing with is complicated with lots of various controls and subforms.  It would be more of a pain for them to have to fill out the form in a particular order than it would be to have to click "OK" a bunch of times along the way.

But on the other hand since they have to click "OK" so many times it's easy to imagine that they'll do it blindly when I want the completion check to REALLY run -- when they close or move to another record in the parent form.

Answer 2

I'd suggest checking Ken's demo - he writes terrific code and I've used many of his samples! If it doesn't meet your needs, you might also want to consider having the subform's Enabled property set to No, and maybe even making it invisible, until the needed data is entered on the mainform. Once the mainform record is shipshape you could then make the subform enabled and set focus to it. Sometimes you just have to lead users by the hand to get them to go the right path!

Answer 3

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)?

You are correct about the formComplete function, it's not overly easy to do what it needs to do, but I think it's pretty much OK.

Answer 4

You cannot prevent a parent form's current record being saved when you move focus to a subform to enter data into a referencing table.  Even if you could, referential integrity would be violated.  I assume that your formComplete function returns a Boolean False if no matching rows exist in the related table on which the subform is based, as well as there being any omissions from mandatory controls in the parent form.  The only way you could cover both requirements would be to omit the check on matching rows existing in the related table from the function.  You could then call it in the parent form's BeforeUpdate event procedure, as at present, to validate the mandatory controls in the parent form before moving focus to the subform.

To enforce the insertion of at least one row in the subform you can use the technique illustrated in the file Families.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

In this little demo file, if the user attempts to navigate to a new record in the parent form, or to close the form, code in the parent form's Current and Unload event procedures detects the absence of a matching row in the referencing table (FamilyMembers) and returns the user to the record just entered.  The user must then either enter at least one row in the subform or delete the record in the parent form.  Otherwise the process is repeated ad infinitum until they do one or the other.

Answer 5

I suspect the problem has to do with the way Access handles subform data. The BeforeUpdate events of the mainform and the subform are (pretty much) independent: each fires when that form is ready to be updated.

The problem is that the mainform record updates (and its BeforeUpdate event executes) the instant you set focus to the subform; it must do so, in order to allow referential integrity to be enforced (you need the parent record saved before you can create the child record). Similarly, the subform updates as soon as you set focus back to the mainform or move to a new record on hte subform. There is no event which fires when all of the records that the user intends to enter on the subform (or all of the multiple subforms) are complete.

Depending on what your formComplete function does, it may or may not be appropriate; and it may be nontrivial to determine that the main and subform records are complete to your satisfaction.