Access 2007 BeforeUpdate event firing unexpectedly

Access 2007, Windows 7

I have a main form with a subform.
I do some validation in the mainform BeforeUpdate event procedure.
If the validation fails, I set Cancel = True in the BeforeUpdate procedure.

In the following sequence of events:

1 - focus is on mainform bound textbox A
2 - I click on a subform textbox
3 - mainform BeforeUpdate event fires (dirty=True)
4 - validation fails, (because another required field is blank),
    a msg is displayed (via a VBA msgbox statement),
    Cancel is set to True in the BeforeUdpate procedure.
5 - focus returns (automatically) to mainform textbox A
6a - if I now TAB to mainform bound textbox B, focus moves quietly to B
6b - but if instead I CLICK on mainform textbox B,
     the mainform BeforeUpdate event fires again, (dirty=True)
     and so the same validation message is displayed again.  
7  - if I click on mainform textbox B again, BeforeUpdate does NOT fire.

Why does the mainform BeforeUpdate event fire in step 6b ?
MatthewNicollAsked:
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.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Are you doing anything else in the BeforeUpdate event, like un-doing changes? That could cause the behavior ...
0

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
Jeffrey CoachmanMIS LiasonCommented:
So you are trying to validate the fields "on the fly", individually?.
For me, a lot of times, I will instead do all my validation at one time, ...much like what happens on most web sites.
You enter all your info, one field at a ime, (with no validation after each field is cycled)
...then, ...in the next step (example:, clicking in the subform), ...the system will prompt you about any "invalid" fields.

But going back to yor original question,
...You did not post your code, so it is hard to tell if that is where the flaw lies...
0
MatthewNicollAuthor Commented:
The validation routine I called from the BeforeUpdate event routine did a check which involved a query on the same table that the main form is bound to.  Removing that code helped: the behaviour described in my question now only happens ONCE after a record becomes current.  I'll have to be satisfied with that.  Thanks for your suggestion Scott.
0
MatthewNicollAuthor Commented:
Jeffrey: it is a large form, and there is a lot of code!  I do most of the validation in a routine called in the BeforeUpdate routine.  The trouble is that moving from the main form to a sub-form triggers BeforeUpdate, at which point I don't really want to do all the validation.  (I may ask a separate question about that!)  In the past I have realized that if I want complete control, I have to use unbound controls, but that is not an option at the moment.  Thanks for your comments.
0
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.