Handling Keyboard Events in Access

Mike Wolfe -

Yesterday, I wrote about the unintuitiveness (is that a word?) of navigating grid-like continuous forms in Microsoft Access.  The problem can be summed up thusly: Up is left; Down is right.  

Pressing the up or down arrow key in an Access form moves you to the previous or next control, respectively, in the tab order.  That makes sense when you are working with a form in "Single" view.  

But when you are working on a form in continuous view–where the controls are laid out in a single horizontal line within the detail section–this navigation feels awkward.  As a user, you would expect the up and down arrows to move to the previous and next records and not to the previous and next controls.  Observe:

Using the up arrow moves left to the previous control instead of up to the previous record.

Events in VBA

Recently, I wrote about events in VBA.  I also wrote about raising custom events. However, to take advantage of event-driven programming concepts in Access, you don't need to raise custom events.  Rather, you can take advantage of a wealth of events that are built in to Access objects (forms, reports, controls, etc.).

Today, we're going to take advantage of the TextBox control's KeyDown event.  We'll use this to override the default behavior of the up and down arrow keys.  In future articles, we will expand on this concept to encapsulate the functionality and make it more easily reusable.

The Keyboard Events

The TextBox control has three events related to pressing a key on the keyboard:

  • Key Down: this event is triggered when the user initially depresses a key on the keyboard while the textbox control has the focus
  • Key Up: this event is triggered when the user releases a key on the keyboard while the textbox control has the focus
  • Key Press: this event is triggered when an ASCII key code is sent to the form, usually by pressing a key on the keyboard

This excerpt from the Key Press help page clarifies the order of these three events:

If you press and hold down an ANSI key, the KeyDown and KeyPress events alternate repeatedly (KeyDown, KeyPress, KeyDown, KeyPress, and so on) until you release the key, and then the KeyUp event occurs.

The KeyDown and KeyPress events also differ in the arguments they receive:

  • Event KeyDown(KeyCode As Integer, Shift As Integer)
  • Event KeyPress(KeyAscii As Integer)
  • Event KeyUp(KeyCode As Integer, Shift As Integer)

By the way, this information is available via the Object Browser in VBA (shortcut key [F2]):

The VBA object browser. Learn it; use it; love it.

The KeyDown Event

In most cases, when I'm looking to override the behavior of specific keyboard keys, I use the KeyDown event.  People expect their actions to have immediate consequences, so KeyUp does not make sense.

Between KeyDown and KeyPress, you can think of KeyDown as more of a low-level event and KeyPress as a high-level event.  What do I mean by that?  If you press [Shift] + [A], the KeyPress event will be raised with a KeyAscii value of 65, which corresponds to a capital "A."  The KeyDown event, though, will be raised with a KeyCode of vbKeyA and a Shift bitmask integer equal to acShiftMask.  In other words, KeyDown tells you exactly what keys were pressed, while KeyPress tells you the result of that action.

Handling the KeyDown event

The first step when handling events on forms and reports is to set a value in the Event tab of the object's Property Sheet for the appropriate event. (hint: press [F4] to toggle the Property Sheet in the main Access window).

You can assign a Macro to run when the event is triggered, but I never do that.  Instead, I simply fill in "[Event Procedure]" and handle the event within VBA, as God intended. (hint: you can double-click in an empty cell to the right of the event name and Access will set it to "[Event Procedure]" automatically)

Then, I click on the ellipsis on the far right, which auto-generates the subroutine declaration and takes me to its location in code.

Handling built-in events on forms and reports in Access.

The auto-generated code

When you click on the ellipsis, Access will jump you straight to the code-behind module for the form or report.  Specifically, it will take you to the subroutine that handles the event.  If the routine does not yet exist, Access will create it automatically.

Here is the auto-generated code that handles the KeyDown event of the tbDiscount text box from the screenshot above:

Private Sub tbDiscount_KeyDown(KeyCode As Integer, Shift As Integer)

End Sub

Do Something Useful

At this point, we haven't actually done anything useful yet.  We'll cover that particular topic in future articles.

Image by Daniel Agrelo from Pixabay