Dec. 22 Post Cont.

[Dan McClelland]

Here's what I posted on Dec 22 and got a couple of
responses: SQL Server 2K back end, Access XP adp front end.

Desired effect: Bound form, user entering new record,
leaves a required field incomplete and clicks the
application close box. User is warned (USING MY CUSTOM
MESSAGE AND NOT THE GENERIC ACCESS MESSAGE) that a
required field is empty and given the opportunity to fill
it in before exiting the application.

Correct me if I'm wrong...the only way to suppress the
generic (and potentially confusing) Access message is to
use the form's OnError event and setting the intrinsic
constant Response to acDataErrContinue. But, by
definition, this setting will do TWO things: suppress the
Access message and ignore the error. Upon ignoring the
error, the partly-entered new record disappears and the
user is returned to a new blank record. (That's right, it
disappears. It is not saved in the table. It is NOWHERE.)

Let me be clear. If I don't use the form's OnError event,
I can find no way of avoiding Access' generic message. No
way. However, if I do use OnError, and set Response =
acDataErrContinue, the generic Access message is
supressed, but the error is ignored too. WHY, oh WHY
isn't there a way to suppress the message but NOT ignore
the error so I can handle it? There's no reason the
record should just vanish.

Randy Wayne's response: Have I tried using the
Before_Update event? Yes I have. Remember, this
situation only happens when the user clicks the
application close box. If I use Before_Update and DON'T
use Form_Error, the generic Access error message appears,
which I want to avoid. If I use them together, the
Before_Update code is triggered (it even includes Cancel =
True), but the Form_Error event fires anyway, skipping the
generic Access message (which is good), but causing the
application to exit and the new record to be discarded.

TC's response: Never had a problem with the Form_Error
event halting the process. Well, me either TC. Unless I
click the application close box, in which case, the form's
behavior seems to be a little different. I've tested it
with Jet data as well, same result.

If I allow the Access generic error message, I can prevent
the application from exiting. If I suppress the generic
error message using the Form_Error event, the application
will exit EVERY TIME.

My solution (although I don't like it): If the user clicks
the application close box, they are warned that changes to
their new entry will be lost (because I can give them a
msgbox but can't stop the process) and that they should
avoid closing the application that way in the future.

 

[Guest]

Using a bound form to handle a new record when you want to carefully control the data entry creates these sort of problems. Try using an unbound form and manually adding the data when the Save button is pressed. The advantage in your case is that any unchecked or incomplete data is not added to the database unless it passes through the Save button where you can do your checks.
HTH
Terry