problem cancelling a new record addition

[Paul]

I have a subform with a combo box bound to a primary key in the subform
table that is used to add new records to the subform. I also have code in
the Not in List event that opens another form to add a new record if the
user's entry isn't already in the combo box.

A problem occurs, however, if a user tries to cancel the entry by using the
Backspace key to delete all the characters instead of pressing the 'Esc'
key. If the user has deleted all the characters from the combo box and then
tries to exit the record or close the form, Access produces the following
error message which is useless to the average user:

You tried to assign the Null value to a variable that is not a
Variant data type.

When the user clicks OK, Access then produces another equally helpful error
message saying

The value in the field or record violates the validation rule for
the record or field.

Of course this problem can be solved if the user knows to press Esc before
doing anything else, but no matter how much effort is expended to convey
that inofrmation, there are always going to be some people who don't get the
message.

This situation can be especially frustrating to the user that doesn't think
to press the Esc key, because those two error messages keep reappearing
again and again if the user tries any other means of cancelling the new
record addition, such as pressing the either the Tab or Enter keys, or
clicking elsewhere with the mouse. In fact, when the user is in this
confusing situation, he can't even close the form, because those two error
messages keep nagging him.

I've tried to trap this in the Before Update event by running a Me.Undo and
Cancel = True, but that didn't work. I also tried If
len(nz(me!controlname)) = 0, then Me.Undo, but that didn't work either.

I wish I could come up with something that would undo the entry regardless
of whether the user tries to exit with either the Tab or Enter keystrokes,
as well as the mouse. A very distant second choice would be to trap the
error so that I could at least present the user with something in
understandable language that a normal person can understand, such as

"Press the 'Esc' key on your keyboard to cancel the entry.

But again, my first choice would be to figure out a way to get VBA to run
Me.Undo at the right moment, so we don't have to require the user to do
anything other than exit the record.

Can anyone tell me which event I can use to cancel an entry anytime the user
has cleared out all characters from a new entry with either the Backspace or
Delete keys?

Thanks in advance,

Paul

 

[Jeanette Cunningham]

Hi Paul,
those errors come from Form_Error.
Set up a sub for Form_Error and trap those errors there.


Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia

 

[Paul]

Ah - that's it!

I did notice that when I only put Me.Undo in the Form_Error event, it solved
the main problem, because it cancelled the entry, and eliminated that error
message loop. Access still displayed the "You tried to assign the Null
value to a variable . . . " message, but only one time. However, when I
added "Response = acDataErrContinue," even that last unwanted error message
went away.

Thanks so much for giving me the solution to this problem, Jeanette.

G'day from Sacramento, California,

Paul

 

[Jeanette Cunningham]

You're very welcome .

Jeanette Cunningham MS Access MVP -- Melbourne Victoria Australia