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:
- 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)
Figure 2-6 shows the form and its properties.
Select Case KeyCode
Case vbKeyPageUp, vbKeyPageDown
KeyCode = 0
Case Else
' Do nothing.
End Select
End Sub
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.