Access Cookbook - Ken Getz [39]
See Also
See How Do I Create a Module in the Preface for information on creating a new module.
2.3. Restrict the User to a Single Row on a Form
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?
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:
Create your form. Set its Cycle property (on the Other properties page) to Current Record. This causes the Tab and Shift-Tab keys to work correctly.
Set the form's KeyPreview property (on the Event properties page) to Yes. This causes the form to intercept keystrokes before any controls on the form can react to them.
Enter the following code for the form's KeyDown event (see the Preface for information on creating event procedures).
Private Sub Form_KeyDown(KeyCode As Integer, Shift As Integer)
Select Case KeyCode
Case vbKeyPageUp, vbKeyPageDown
KeyCode = 0
Case Else
' Do nothing.
End Select
End Sub
Figure 2-6 shows the form and its properties.
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.
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