Recipe 2.3 Restrict the User to a Single Row on a Form

2.3.1 Problem

When you press Tab or Shift-Tab, you can't keep Access from moving the cursor to the next or previous row of data if you happen to be on the first or last control in a form's tab order. The same thing happens when you press the PgUp or PgDn key. Often, however, you want the cursor to stay on the same row, and you want complete control over when the user moves to a different row. Is there some way to keep Access from moving the cursor to the next or previous row when these keys are pressed?

2.3.2 Solution

To gain complete control over row movement, you'll need to incorporate two different techniques. You can use your form's Cycle property to decide whether leaving the first or last control on the row moves you to a different row. If you want to ensure that PgUp and PgDn don't move the cursor to a different row, you'll need to write a bit of code that will trap these particular keystrokes in the KeyDown event for the form and disregard them. This solution uses both techniques to limit row movement.

Follow these steps to add this functionality to your own form:

Figure 2-6. Use the KeyDown event to trap keystrokes and control form movement

To see how this works, open and run frmRestricted from 02-03.MDB. Press Tab to move from field to field. When you get to the final field on the form, press Tab once more, and your cursor will move back up to the first control, rather than moving on to the next row, as it normally would. The same thing occurs when you use Shift-Tab to move backward through the controls. When you reach the first control, the cursor will wrap around and go to the final control on the same row, rather than moving to the previous row. Try pressing the PgUp or PgDn keys: they're completely disregarded. The only way to move from row to row is to use the navigation buttons on the form. Try unchecking the Control Movement checkbox, and see how the default behavior differs.

2.3.3 Discussion

There are actually two techniques at work in this sample form. The first technique, using the form's Cycle property, forces the cursor to wrap around from bottom to top if moving forward through controls on the form, or from top to bottom if moving backward. You can set the property to All Records (the default), Current Record, or Current Page. This example uses the Current Record setting, which wraps around for each full record. The Solution in Recipe 2.5 uses the Current Page setting so that the cursor wraps around on the current page of a multipage form.

The second technique involves trapping keystrokes and convincing Access to disregard specific ones. A form's KeyDown event occurs every time you press any key, and Access informs the event procedure exactly which key was pressed by passing to it the KeyCode and Shift parameters; the former contains the keycode of the key pressed, and the latter is a flag that indicates whether or not the Shift key was depressed when the key designated by KeyCode was pressed. You want Access to ignore the keystroke if you press the PgUp or PgDn key. To make that happen, you can modify the value of the KeyCode parameter, setting it to 0. This tells Access that you want the keystroke to be ignored. Step 3 includes the code that performs this transformation. (Think what fun you could have intercepting each keystroke and converting it to something else behind the scenes, just to amuse your users!)

The sample form uses the following code, in reaction to the check box's AfterUpdate event, to control how the form reacts to keystrokes:

Private Sub chkMovement_AfterUpdate( )
    If Me.chkMovement Then
        Me.Cycle = acbcCycleCurrentPage
        Me.OnKeyDown = "[Event Procedure]"
    Else
        Me.Cycle = acbcCycleAllRecords
        Me.OnKeyDown = vbNullString
    End If
    Me.Prefix.SetFocus
End Sub

If you're going to use the techniques presented in this solution, you'll probably want to provide some method of navigating through the rows on your form. You could use the built-in navigation buttons, but you probably wouldn't have gone to this much effort if you didn't want a bit more control. The Solution in Recipe 2.6 provides a method you can use for placing your own navigation buttons on a form, giving you complete control over the look and placement of the controls. Using those controls, you can ensure that users can't move to a different row until they've satisfied your needs in the current one.

2.3.4 See Also

For more information on handling keystrokes, see Recipe 11.3 in Chapter 11.