The ArrowKeyNav Journey

Mike Wolfe -

The up and down arrow keys move left and right on a continuous form in Access instead of up and down the way Excel works.  Is there anything we can do about it?

Yes!  In fact, I wrote a whole series of articles about how to write event-handling code that overrides this default behavior.  I broke down every single step along the way in a series of nearly two dozen articles.  This relatively simple event-based programming challenge provides an opportunity to demonstrate a bunch of rather advanced VBA topics in an easy-to-understand way, such as:

  • the WithEvents keyword
  • recursion
  • variable scoping

This article lays out the entire journey in one place.

tl;dr can i haz teh codez?

Sure. Fine. Whatever.  Here's the code: weArrowKeyNav Class

Event-Driven Programming

Let's start with some background information.  What exactly is "event-driven programming?"

Event-Driven Programming in VBA
Event-driven programming may sound complicated, but the concept is really quite simple.
No Longer SetMike Wolfe

That's cool.  Can we raise and handle our own custom events in VBA?  Sure thing:

Raising Custom Events in VBA
This quick tutorial will have you writing custom events in VBA in no time.
No Longer SetMike Wolfe

Defining the Problem

So what exactly is the problem we're going to be solving with this fancy "event-driven programming?"

Navigating Continuous Forms
If Up is left and Down is right, you must be navigating a continuous form in Microsoft Access.
No Longer SetMike Wolfe

Understanding the Keyboard Events

These two articles provide some basic information about the keyboard events attached to form controls and how to handle them.  

First, an article about the three different keyboard events:

  • Key Down
  • Key Press
  • Key Up
Handling Keyboard Events in Access
This beginner article will step you through the process of how to begin writing code that runs when a user presses a key on the keyboard.
No Longer SetMike Wolfe

Next, an article about the two arguments passed to the Key Down and Key Up event handlers: KeyCode and the Shift bit mask.

KeyCode and Shift Arguments
The KeyDown and KeyUp events include KeyCode and Shift arguments to identify which keys the user pressed. Let’s explore those arguments in more detail.
No Longer SetMike Wolfe

The Simple Approach

In this next article, I demonstrate the "brute-force" approach to writing code-behind in the form's module to override the default arrow key navigation.

This article provides the foundational logic for moving between records instead of controls when the up and down arrow keys are pressed.  It's the meat and potatoes of our solution:

The ArrowKeyNav Routine
Enable Excel-like navigation in your continuous forms by overriding the default behavior of the up and down arrow keys.
No Longer SetMike Wolfe

This very naive approach requires calling the behavior-overriding function from every control's KeyDown event handler.  To increase the readability of those extra calls, I wrote an article about using the colon character to merge short subroutines into a single line of code:

Introducing WithEvents

Here's where things start to get interesting.  What is this mysterious WithEvents keyword and how does it work?  It all sounds so complicated.  Don't worry, we'll keep things simple.  

In this first article, we move the code from the form module to a separate class module.  To minimize the number of new concepts we're introducing, this article only uses WithEvents to handle KeyDown events for text box controls.

Using WithEvents to Encapsulate Event Handling Code
You don’t need to call the event handler for every control to handle its events. Instead, you can use WithEvents to encapsulate that code in a class module.
No Longer SetMike Wolfe

Of course, we need to handle more than just text box controls.  So, in this next article, we add support for combo boxes and check boxes.  We also encapsulate that added complexity within the class and expose only a single Control property (rather than separate TextBox, CheckBox, and ComboBox properties).

Handling Multiple Control Types in a WithEvents Class
Using WithEvents to subclass form controls is a powerful technique. Here’s one way to handle multiple control types in a single class.
No Longer SetMike Wolfe

To wrap up the WithEvents mini-series, we end with an article that introduces what I think is the most advanced technique of the entire series: maintaining a private collection of objects that are instances of the class itself.

Many Objects, One Class Module
Check out this trick for reducing boilerplate code: maintain a private collection of objects that are instances of the class itself.
No Longer SetMike Wolfe

Smoothing the Rough Edges

In this next round of articles, we address some of the rough edges that crop up as part of implementing our solution.

First up, what happens if there is a combo box in our continuous form and it is dropped down?  We want the up and down arrow key to move within the list of combo box choices.  Up until now, though, our custom behavior would move to the previous or next record on the form.  That is not what we want.

ComboBox Dropped Down State
Overriding the up/down arrow key behavior improves the user experience on a continuous Access form. But what if the user drops down a combo box?
No Longer SetMike Wolfe

Previous versions of the code in this series use the .AddNew method of the form's recordset to move to a new record.  But using that technique causes excessive incrementing of the underlying table's autonumber field.  Let's fix that problem, too.

AddNew Increments AutoNumber Fields
Thanks to Ben Clothier, we’ve got another improvement to our Arrow Key Navigation class.
No Longer SetMike Wolfe

The final improvement to the code is a more reliable way to find the form object associated with the control we are handling.

Get Form By Control
Let’s use recursion and the TypeOf...Is expression to get the first form parent of any control on our form.
No Longer SetMike Wolfe

The Code

When you put everything together, you get the weArrowKeyNav class module:

weArrowKeyNav Class
Do you want the up and down arrow keys to move to the previous and next records in a continuous Access form? Here’s how to do that with only two lines of code.
No Longer SetMike Wolfe

The Presentation

If you prefer to get your information in video or audio form, you're in luck!  I gave an hourlong presentation on this topic to the Access Pacific User Group on May 6, 2021.  Copies of the slide deck and the sample database I used are available here:

ArrowKeyNav Presentation
A list of resources and further reading to support my presentation on Navigating Continuous Forms using WithEvents.
No Longer SetMike Wolfe

The Video

The video of the presentation is available on YouTube.  The link in the article above will take you to the same video as the one I'm posting below:

Image by Ditto Shafarnahariy from Pixabay