Access Cookbook

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:

1. 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.

2. 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.

3. Enter the following code for the form’s KeyDown event ...