The ArrowKeyNav Routine

Join me on Thursday, May 6, 2021 at 9:30 PM EDT (May 7, 01:30 UTC) for my presentation, Navigating Continuous Subforms using WithEvents, to the newly-minted Access Pacific User Group.

I've written several articles over the past few days about overriding the default behavior of the up and down arrow keys on a continuous form in Access.  Refer to these previous articles if you have any questions about how we got to this point:

• Navigating Continuous Forms: a brief overview of the problem
• Handling Keyboard Events in Access: step-by-step instructions for getting Access to generate the code we need to handle the keyboard events
• KeyCode and Shift Arguments: detailed info about the KeyCode and Shift arguments of the KeyUp and KeyDown events

The Code: ArrowKeyNav Routine

Let's jump right into the routine we're going to use to override the default behavior of the up and down arrow keys, and then we'll break it down afterwards:

Sub ArrowKeyNav(ByRef KeyCode As Integer, Shift As Integer)
    If Shift <> 0 Then Exit Sub
    
    Dim SaveKeyCode As Integer
    SaveKeyCode = KeyCode
    KeyCode = 0
    
    Select Case SaveKeyCode
    Case vbKeyUp
        Me.Recordset.MovePrevious
        If Me.Recordset.BOF Then Me.Recordset.MoveNext
    Case vbKeyDown
        If Me.NewRecord Then Exit Sub
        Me.Recordset.MoveNext
        If Me.Recordset.EOF Then
            If Me.AllowAdditions Then
                Me.Recordset.AddNew
            Else
                Me.Recordset.MovePrevious
            End If
        End If
    Case Else
        KeyCode = SaveKeyCode
    End Select
End Sub

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

Guard clause

We start out with a guard clause:

If Shift <> 0 Then Exit Sub

This checks to see if the user is holding down the [Ctrl], [Alt], or [Shift] keys at the time they press the up or down arrow key.  If they are, we exit immediately without overriding any behavior.

SaveKeyCode

This is a little bit of overkill for a routine where we are only overriding the behavior of two keyboard keys, but I wanted to demonstrate the concept.

Generally speaking, you'll want to set the KeyCode to zero for a key whose behavior you are overriding.  Otherwise, you'll get the new behavior you code for plus the original behavior of that key.  

Rather than set the KeyCode to zero for every key I'm overriding, I start by setting the KeyCode to zero at the top of the routine.  This cancels the default behavior for every key.  At the end of the Select Case statement, I restore the default behavior for any key whose behavior I did not override.

This is a bit of defensive programming.  If I decide to handle another keyboard key at some point in the future, I don't have to remember to set the KeyCode to zero for that other key.  I simply add a Case statement for the new key and the SaveKeyCode algorithm takes care of the rest.

Select Case statement

Next, we use a Select Case statement to override the behavior of the up and down arrow keys.  We check the value of the SaveKeyCode variable because we've temporarily set the KeyCode argument to zero.

We use the KeyCode constants instead of raw integers for more readable code.

vbKeyUp

When the user presses the up arrow key, we want to move up a row on the form.  That is, we want to move to the previous record: Me.Recordset.MovePrevious.

What happens if we're already at the first record on the form, though?  If we are on the first record of the form, the recordset's BOF property (Beginning Of File) is False.  But if we move to the previous record in the recordset, the BOF property is now True.  

The Access user interface looks the same whether the form's recordset is at BOF or at the first record (where .BOF = False).  However, these are separate positions on the recordset.  If we perform a .MoveNext from .BOF we are at the first record (not the second).

We can always safely move to the previous record in the recordset, even if we are starting at the top record.  We just need to check to see if we've moved into the .BOF position.  If we have, we simply .MoveNext to get our recordset cursor back to the first record.

vbKeyDown

When the user presses the down arrow key, we want to move down a row on the form.  That is, we want to move to the next record: Me.Recordset.MoveNext.

Before we do that, though, we have a guard clause to check to see if we are on a new record:

If Me.NewRecord Then Exit Sub

If we're already on a new record, there's nowhere further to go so we exit immediately.

The .EOF property is similar to the .BOF property in that it is true when the recordset cursor is past the last record in the recordset.  So, we handle it in much the same way as the .BOF property.  There is one extra wrinkle, though.  We need to account for adding new records.

If the form allows for adding new records, then we move to the new record row the recordset's AddNew method: Me.Recordset.AddNew.  If the form does not allow for adding new records, then we use the .MovePrevious method to back up the recordset cursor to the last record in the recordset.

Case Else

As I mentioned earlier, the final step is to restore the default behavior for every other key besides the two whose behavior we overrode.

Calling the Code

To make this code actually run, we still need to call it from the KeyDown event of every control whose behavior we want to override.  

In future articles, I'll show you some tips to improve that part of the process by using:

• The colon character (:) to combine lines of code
• The WithEvents keyword to encapsulate this functionality in a class module

Image by xuuxuu from Pixabay