Any way to enforce subfrom edits from main form

In my Access 2013 application I have a mainform 'frmDocumentDefinition' with two subforms 'frmDocumentInterestedParties_Sub' and 'frmDocumentTriggeredCosts_Sub' on it.

The two subforms are continuous forms.  Within the subforms I have an edit routine in the 'before update' event for each record entered or updated on the subforms to make sure that all required fields have been entered on each line.  The edits work and put out messages for any errors encountered.  While working only in the subform the user cannot go to another line until all the edits for the current line are passed.

The problem occurs when users clicks anywhere on the main form while working on an incomplete line in one of the subforms.  When that occurs:

The subform edits are invoked and any subform error messages are displayed " Cost Date is a required field."

However, after the error message from the subform is displayed the focus shifts to the main form.  The bound subform record is saved but it has the errors in it.  For example: required fields are blank.

How do I stop the subform from losing focus when there are edit errors on the subform?  

How can I make sure the edits on the subforms are enfoorced from the the main form?
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Anders Ebro (Microsoft MVP)Microsoft DeveloperCommented:
In the subforms before update event, are you setting Cancel=true, when the required fields are missing? The will prevent the record from being saved, and that should also prevent the focus from moving to the main form.

Also if the field must ALWAYS be filled out, then you should, at the table level set the field to be required. Don't get me wrong, the msgbox in the beforeUpdate event is still a good idea, as it presents a much more user friendly message.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gustav BrockCIOCommented:
Also, the subform controls on the main form have an OnExit  event you might be able to use.

Dale FyeCommented:
Agree with Gustav, the Exit event of the subform has a Cancel argument, which allows you to cancel exiting the form.  you could simply test to determine whether the subform is dirty and cancel if it is still dirty, something like:
Private Sub SubformControlName_Exit(Cancel as Integer)

    Cancel = me.subformControlName.Form.Dirty

end sub

Open in new window

mlcktmguyAuthor Commented:
Anders asked: "In the subforms before update event, are you setting Cancel=true"

I was not setting the Cancel=True

That works and keeps the focus on the subform.  Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.