Canceling the OnKeyDown event

[John S. Ford, MD]

I'm using Access 97. I have written an event handler to control the
NotInList event for a combobox to display my own error message and not allow
the user place an inapproriate entry and then hits <Return>:

Private Sub cboListBox_NotInList(NewData As String, Response As Integer)
Response = acDataErrContinue
MsgBox "You must enter a valid entry.", vbExclamation
Me.Undo
End Sub

The code works perfectly except for one thing. This same ComboBox also has
an event handler for the OnKeyPress event that moves the focus to a subform
on the main form if the user hits <Return>. (This is to facilitate ease of
data entry.) If the user is entering incorrect info into the ComboBox, I'd
like the focus to STAY in the ComboBox and not advance to the next entry
field. I tried inserting a Me.SetFocus line at the end of the above code
but this generates an error message (Error #2449, invalid method in
expression).

Any simple way to resolve this? Can I somehow "cancel" the OnKeyDown event
in my code?

John

 

[Dirk Goldgar]

I'm using Access 97. I have written an event handler to control the
NotInList event for a combobox to display my own error message and
not allow the user place an inapproriate entry and then hits <Return>:

Private Sub cboListBox_NotInList(NewData As String, Response As
Integer) Response = acDataErrContinue
MsgBox "You must enter a valid entry.", vbExclamation
Me.Undo
End Sub

The code works perfectly except for one thing. This same ComboBox
also has an event handler for the OnKeyPress event that moves the
focus to a subform on the main form if the user hits <Return>. (This
is to facilitate ease of data entry.) If the user is entering
incorrect info into the ComboBox, I'd like the focus to STAY in the
ComboBox and not advance to the next entry field. I tried inserting
a Me.SetFocus line at the end of the above code but this generates an
error message (Error #2449, invalid method in expression).

Any simple way to resolve this? Can I somehow "cancel" the OnKeyDown
event in my code?

Is the behavor when the user hits {Enter} intended to be different from
the behavior when the user types a value in the combo box and presses
{Tab}, or from when the user uses the mouse to drop down the combo's
list and select a value? If not, then it seems to me your end would be
better served by using the combo box's AfterUpdate event, rather than
the KeyDown event, to send the focus to the subform. That way, if you
disallow the update in the NotInList event, the AfterUpdate event won't
fire and the focus will stay in the combo box.

 

[John S. Ford, MD]

Dirk,

The behavior should be identical for hitting <Enter> or <Tab> so your
solution might work for me. However, if I put the code for shifting focus
to the subform in the AfterUpdate event, will it get triggered by clicking a
valid choice of the ComboBox with the mouse also?

John

 

[John S. Ford, MD]

Well I just tried your solution and it works great if the ComboBox's data is
in fact updated either after being confirmed with the <Enter>, <Tab> or if
entered directly with the mouse. However, if I'm merely using the <Return>
or <Tab> key to "navigate" to my next field (which requires code because
it's on a subform) then it doesn't get triggered?

Any modifications you can think of?

John

 

[Dirk Goldgar]

Well I just tried your solution and it works great if the ComboBox's
data is in fact updated either after being confirmed with the
<Enter>, <Tab> or if entered directly with the mouse. However, if
I'm merely using the <Return> or <Tab> key to "navigate" to my next
field (which requires code because it's on a subform) then it doesn't
get triggered?

Any modifications you can think of?

Let me see if I understand what you want, as this is developing more
complications as I find out more about it. I think you are saying that
you want to go to the subform, and to a particular field in the subform,
any time the user moves forward from the combo box (where "forward" is
defined according to the form's tab order), and any time the user
updates the value of the combo box. However, you don't want to go to
the subform if the user tabs backward out of the combo box. Is that
right?

If so, we can address these requirements as follows:

1. Make the subform control (the control on the main form that displays
the subform) next in the tab order after the combo box. That way,
tabbing or "entering" forward out of the combo box will automatically
put the focus on the subform.

2. Create an event procedure for the Enter event of the subform control.
In that procedure, set the focus to the particular control on the
subform that you want to become active whenever the subform gets the
focus. That will ensure that tabbing into the subform also puts the
control where you want it to go in the subform. In writing the event
procedure, be aware that this procedure is part of the module of the
main form, not the subform, so you must qualify the subform reference
appropriately. For example, if your subform control is named
"sfMySubform" and you want to go to the control named "txtMyTextbox" on
the form being displayed by that subform, then your Enter event
procedure might look like this:

Private Sub sfPlacements_Enter()

Me!sfMySubform.Form!txtMyTextbox.SetFocus

End Sub

3. After you've set things up as described in steps 1 and 2, I think
that all you need to do in the AfterUpdate event of the combo box on the
main form is set the focus to the subform; e.g.,

Private Sub cboMyCombo_AfterUpdate()

Me!sfMySubform.SetFocus

End Sub

I think that should take care of it. Of course, all this is assuming
that I've understood correctly how you want this to behave. Also, if
there's a case where you might want to enter the subform and *not* go to
the specified control, that will complicate things.

 

[George Nicholson]

Consider using the combobox_Exit event instead of KeyDown. It should fire
whether the control has been updated or not and if it HAS been updated, the
NotInList and AfterUpdate events will have already resolved themselves.

 

[Dirk Goldgar]

Consider using the combobox_Exit event instead of KeyDown. It should
fire whether the control has been updated or not and if it HAS been
updated, the NotInList and AfterUpdate events will have already
resolved themselves.

I thought of that, but that would mean that tabbing backward out of the
combo, or clicking to some earlier control on the form, would also set
the focus to the subform. I don't think that's what Dr. Ford wants to
have happen.

 

[John S. Ford, MD]

LOL. I was hoping to simplify the problem. Basically, I have a ComboBox
that I want my user to use to enter data (with data from the list). When he
hits <Return> or <Tab> to move to the next field I use the KeyDown event to
identify these keycodes and move the focus to the next field which happens
to be on a subform in table view. (I need additional code on it to get the
new record). Simply tabbing to the next control

Your solution of putting the navigation code in the OnUpdate event handler
works perfectly UNLESS the value in the ComboBox isn't actually updated
(altered) ie. if the user after landing in that ComboBox then wishes to
leave it for the next field without changing the data contained in it. In
that situation, the focus goes to the next control in the tab order which is
a page of the tab control containing the subform.

If you want to bail, no problem. I didn't think it would be this involved!

John

 

[Albert D. Kallal]

Well I just tried your solution and it works great if the ComboBox's data
is
in fact updated either after being confirmed with the <Enter>, <Tab> or if
entered directly with the mouse. However, if I'm merely using the
<Return>
or <Tab> key to "navigate" to my next field (which requires code because
it's on a subform) then it doesn't get triggered?

Any modifications you can think of?

Up to this point, you got the right approach. The only thing I would change
is to NOT switch the focus in the after update event...

Further, you don't need the keydown event here (just yet).

However, if I'm merely using the <Return>
or <Tab> key to "navigate" to my next field (which requires code because
it's on a subform)

Why do you need code for the above? Can you not just have the sub-form the
next object in the tab order?

If for some reason you can NOT have the sub-form next in the tab order, you
simply place a control next in the order right after you combo box...and you
use the on enter event of this text box to set the focus to the sub-form.
You can place (and size) this text box real small so in fact you can't
really see it.

So, either
a) - place the sub form control next in the tab order
(if you need to run some code BEFORE you jump into the sub-form,
then use the sub-forms control on enter event
NOTE that a sub-form is JUST control on the screen. ...if you
click once on the sub form control..you can then select the
events tab (notice how there is ONLY TWO events available for
this control if you selected the sub-form control correctly)

b) - place a standard text box control right after the combo box
(make sure you set the tab order), and then simply
run your setfocus (and other code) in the controls on-enter
event.

I have used both the above approaches many times....