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
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.
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.
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:
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.
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:
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.
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
WithEventskeyword to encapsulate this functionality in a class module