Validation checks in unbound controls

[mrl]

I am using unbound controls (combo boxes) on a form to enable the user to
establish filtering criteria for the records to be displayed. I have macros
doing validation checks on the values entered in these controls before
applying the filters. If the value entered violates the validation criteria,
I want to advise the user of the invalid entry via msgbox, delete the invalid
entry and leave the cursor in the same (now blank) control. "Limit to List"
property set to "No" as the user needs to be able to both select from list
options or key other values. Several different validations (up to 8) need to
be performed, so I use macro/conditions instead of Validation Rule/Text.
I've tried macros with CancelEvent, SendKey {Del} in the BeforeUpdate event
or CancelEvent, SetValue = Null in the AfterUpdate event. Neither get the
exact result for both list choices and keyed entry. Any suggestions?

 

[Allen Browne]

If it's a simple validation, you may be able to use the Validation Rule
property of the control.

If it's more complex, canceling its BeforeUpate event will keep the focus
there. After you set Cancel = True, you may be able to Undo the control, but
the user is stuck there anyway. The control's BeforeUpdate should fire
unless you update its Value programmatically.

Alternatively, you can use the control's AfterUpdate event to run some
checks and assign Null to it if you are not happy with the result. This
guarantees it is cleared, but the focus will move on (i.e. you cannot force
the user to stay in the control if you use this event.)

Note that none of these events can be used to test an unbound control for
null. If the user doesn't enter anything (perhaps doesn't even visit the
control), none of its events will fire. Therefore you might be better served
to run the validation later. For example, if these controls are providing
criteria for a report that opens when you click a button, you could use the
button's Click event to check the controls are all okay.

 

[mrl]

The BeforeUpdate Event with CancelEvent then SendKey {Del} seems to get
closest to the desired result since it always leaves the focus on the proper
control.

There are 2 problems though:

1). Even when the SendKey {Del} action seems (visibly) to clear the
control, Access still recognizes a value in the control (even though the user
can't see it). If the user tries to navigate away from the control, this
causes one or more of the macro validation conditions to still be met,
triggering MsgBox's as if the value were still in the control. Only hitting
{Esc} "clears" the control completely allowing the user to navigate elsewhere
on the form. I can't find the proper action for the macro to completely
clear the control. User can completely clear the control with {Esc} after
the macro runs, but including SendKey {Esc} in the macro does not accomplish
the same thing.

2). If the value is typed in the control rather than selected from the
list, SendKey {Del} does not clear the control. The value remains in the
control with the cursor placed after the last character. Again, hitting
{Esc} completely clears the control.

What commands can I include in the macro to completely clear the control
under both of these circumstances?

 

[Allen Browne]

If you want to clear the control, I think you'd be better using its
AfterUpdate event.

SendKeys is rather kludgy, and has side effects such as messing with the
NumLock state and failing under A2007 (on Vista?)

 

[David W. Fenton]

The BeforeUpdate Event with CancelEvent then SendKey {Del} seems
to get closest to the desired result since it always leaves the
focus on the proper control.

Instead of SendKeys, try:

Me!MyControl.Undo

This should undo the last edit.