GIVING SPREADSHEET-TYPE CURSOR MOVEMENTS TO CONTROLS

One thing that past and present spreadsheet users find frustrating is the inability to cursor, or move, around on a form as you can on a spreadsheet. This is especially true when the form itself is set up in such a way that it resembles a spreadsheet. An example of this might be financial information for a customer going over three years.

Figure 10.29 shows an example of a form that's set up in columnar format. This form, SpreadSheetTypeMovementExample, is found on the CD-ROM in the file Chap10.mdb, in the ExamplesChap10 folder.

Looking at the Problem

The form in Figure 10.29 has calculated fields in the middle, so using a subform really isn't an option.

The form is set up in such a way that when you press the down- or up-arrow key, the cursor goes to the field horizontally either after or before, wrapping at the edges of the screen. The other alternative is to set the tab order vertically, but you still can't go all four directions by using the arrow keys. The technique discussed in the following section takes care of this problem by using two VBA routines and some form settings.

Creating a Solution

In the SpreadSheetTypeMovementExample form, an event procedure is added to each KeyDown event of the text boxes that contain input. Figure 10.30 shows an example of this for the Year1 field.

The call to ap_ProcessKeys takes three parameters, which you can see in Listing 10.9. The first parameter, intKeyCode, is the ASCII value last pressed. The next parameter, intDownMove, is the number of fields to move down. Last is intUpMove, which is the number of cells to move up.

Listing 10.9. Chap10.mdb: Controlling Keystrokes

Sub ap_ProcessKeys(intKeyCode As Integer, intDownMove As Integer, _
   intUpMove As Integer)

   Dim strFieldToMoveTo As String
   Dim frmCurrent As Form

   On Error GoTo ap_ProcessKeys_Error

   '-- Grab the current form
   Set frmCurrent = Screen.ActiveForm

   '-- If the current keystroke is the Down Arrow
   If intKeyCode = apDownArrow Then

      '-- If a value has been entered for moving down, do it
      If intDownMove Then

         frmCurrent(ap_NextFieldTab(frmCurrent, intDownMove)).SetFocus
         intKeyCode = 0

      Else '-- else stay put.

         intKeyCode = 0
      End If

   '-- If the current keystroke is the Up Arrow
   ElseIf intKeyCode = apUpArrow Then

      '-- If a value has been entered for moving up, find
      '-- it and set focus to it.
      If intUpMove Then

         frmCurrent(ap_NextFieldTab(frmCurrent, -intUpMove)).SetFocus
         intKeyCode = 0

      Else '-- else stay put.

         intKeyCode = 0

      End If

   End If

   Exit Sub

ap_ProcessKeys_Error:

   MsgBox Err.Description
   Exit Sub

End Sub

The code for the ap_ProcessKeys routine can be found in the ap_SpreadSheetkeystrokes module, also in the Chap10.mdb database on the accompanying CD-ROM in the ExamplesChap10 folder.

The main task that the ap_ProcessKeys routine does is to SetFocus on the new field, based on the number of field requests to skip. If a zero is passed, the keystroke is ignored and the cursor stays on the current field. This process handles the first and last rows on the form. For the first row in the field, the following call would be used because you want to go three fields down but no fields up:

ap_ProcessKeys KeyCode, 3, 0

If you were in a middle row, you would make the following call for three up or down:

ap_ProcessKeys KeyCode, 3, 3

On the bottom row, you would make this call:

ap_ProcessKeys KeyCode, 0, 3

The code for locating the new field to jump to is actually in the ap_NextFieldTab() function. This function takes the current form and the number of fields to move forward or backward in the tab index. Listing 10.10 shows the code for the ap_NextFieldTab() function.

Listing 10.10. Chap10.mdb: Handling the Keyboard with VBA

Function ap_NextFieldTab(frmCurrent As Form, intMove As Integer) _
   As String

   Dim ctlCurrent As Control
   Dim intCurrTab As Integer
   '-- Errors will be trapped.
   On Error Resume Next

   '-- Examine each of the controls on the current form.
   For Each ctlCurrent In frmCurrent

      '-- This next code provides two purposes, one is if the current
      '-- control doesn't have a TabIndex property, you don't want it.
      '-- The other is that the TabIndex is stored in a variable.

      intCurrTab = ctlCurrent.TabIndex

      If Err = 0 Then

         '-- Look for the control that is intMove's away from the
         '-- current control.
         If intCurrTab = Screen.ActiveControl.TabIndex + intMove Then

            '-- Store the name and exit the function.
            ap_NextFieldTab = ctlCurrent.Name
            Exit Function

         End If

      Else

         '-- Reset the error and try the next control
         Err = 0

      End If

   Next ctlCurrent

   '-- If none is found, an error has occurred and
just stay put.
   ap_NextFieldTab = Screen.ActiveControl.Name

End Function

This function takes into consideration the display-only field by working off the TabIndex prop erty. This function also takes the current control's TabIndex property and adds the number of tabs, up or down, to move to. It then passes back the name of the new control.

That's really all there is to generically moving up and down on a spreadsheet-type form. Another task that's sometimes useful is displaying certain command buttons that are based on other choices. To perform this task, you must be able to manipulate controls through code.