Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

Access 2007 BeforeUpdate event firing unexpectedly

Posted on 2014-10-28
4
257 Views
Last Modified: 2014-10-29
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 ?
0
Comment
Question by:MatthewNicoll
  • 2
4 Comments
 
LVL 84

Accepted Solution

by:
Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 40410310
Are you doing anything else in the BeforeUpdate event, like un-doing changes? That could cause the behavior ...
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40411536
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
 

Author Comment

by:MatthewNicoll
ID: 40411551
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
 

Author Comment

by:MatthewNicoll
ID: 40411577
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

Featured Post

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…

808 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question