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
- 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
Let's start with some background information. What exactly is "event-driven programming?"
That's cool. Can we raise and handle our own custom events in VBA? Sure thing:
Defining the Problem
So what exactly is the problem we're going to be solving with this fancy "event-driven programming?"
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
Next, an article about the two arguments passed to the Key Down and Key Up event handlers: KeyCode and the Shift bit mask.
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:
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:
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.
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).
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.
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.
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.
The final improvement to the code is a more reliable way to find the form object associated with the control we are handling.
When you put everything together, you get the weArrowKeyNav class module:
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:
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: