Enterprise Software

Provide easier navigation between Access main and subforms

Form navigation within Microsoft Access can prove tricky, especially if an app hasn't simplified the process for novice users. See how to create a Visual Basic event procedure to make navigating between Access forms far more intuitive.

For the experienced Microsoft Access user, navigating between main forms and subforms is an intuitive function. By pressing the [Tab] or [Enter] key within Access, you can move from control to control on a form or to a subform. As far as Access is concerned, an embedded subform is just another control. Access gives focus to the subform just as it does any other control—within the set tab order. Once you're in the subform, pressing [Tab] or [Enter] cycles through the controls in the subform, until you get to the last one. At that point, pressing [Tab] or [Enter] moves the focus to the next control in the main form's tab order. However, a problem arises when trying to get back to the main form when working within a subform.

While in the subform, there are two ways to move back to the main form:
  • Press [Ctrl][Tab] to give focus to the next control in the tab order of the main form.
  • Press [Tab] or [Enter] until you move through all the controls in the subform—then press it again to give focus to the next control in the main form's tab order.

The problem
Even though pressing [Ctrl][Tab] seems like a simple enough solution, it's not intuitive. There is no way for a user to guess that [Ctrl][Tab] returns focus to the main form. You must provide that information via a text message on the form, training, or documentation.

A bigger problem arises when a subform is an embedded continuous form. By embedded, I mean an existing form that's added to another and not the result of a form wizard. You don't have the same kind of access to, and control over, a wizard-produced subform. The [Ctrl][Tab] keystroke combination still works—at any time, it will give focus to the next control in the tab cycle on the main form. However, cycling through the controls until you reach the last one and then pressing [Tab] or [Enter] will not give focus to the next control in the main form. Instead, it will give focus to the first control in the subform's last record. Having to remember to press [Ctrl][Tab] isn't a huge burden, but it can be a nuisance to users who are viewing or entering data, especially if it breaks their concentration.

Providing a consistent and easy-to-use interface is the developer's job. The user needs consistency, and being forced to press [Ctrl][Tab] instead of [Tab] or [Enter] to exit a continuous subform isn't consistent. Do you really want to force users to catch on to using an application? Even though this behavior is built in, you'll get the blame when users forget the keys to press and get frustrated.

The solution
Giving your users an alternative to the [Ctrl][Tab] combination is a good idea. Make the subform perform just like all other subforms. When the focus is in the subform's last control, pressing [Tab] or [Enter] will give focus to the next control in the cycle on the main form. Fortunately, this is easy to do by adding a procedure to the last control's KeyUp event. This event checks each keystroke. When [Tab] or [Enter] is pressed, the procedure gives focus to the next control in the subform.

Figure A shows a simple form—frmOrders and frmOrderDetailsSub—based on the Orders And Order Details tables in Northwind (the sample database included with Access). You can use any main form/subform arrangement. Before modifying the subform, spend some time navigating through the form and the subform. Once in the subform, press [Tab] or [Enter] until you reach the last control in the new (blank) record—that would be the Discount control for the blank record. Pressing [Tab] or [Enter] moves the focus to the first control (OrderID) in that new record, not the ShippedDate control, as you might expect.

Figure A


I'll add an event procedure to this subform to force the continuous form to behave as other subforms.

Adding the event procedure
To add an event procedure that will make the continuous form more consistent with other forms, press [Alt][F11] to launch the Visual Basic Editor (VBE). Select the subform in the Project Explorer window and then enter the procedure in Listing A into the subform's module. We added the procedure to the Discount control because it is the last control for each record in the subform. The first two lines go in the declarations section because they are module-level constants. These two lines allow you or your users (through some interface you provide) to choose whether to use [Tab] or [Enter] to cycle through controls. This just makes the application more flexible and friendly for the user. You could use conditional compilation if you have some condition that you can use to set the appropriate key. In this example, the vbKeyReturn line is commented out, so I'm using the [Tab] key. To change the setting, simply comment out the vbKeyTab statement instead.

Return to the form in form view, select a control in the subform's last record—the one for Mozzarella di Giovanni. Don't select the new record. To follow our example, press [Tab] until you reach the last control in that record and then press [Tab] once more. Doing so gives focus to the ShippedDate control that immediately follows the subform in the current tab cycle (for the main form).

Unfortunately, the procedure still needs some work. In the continuous subform, select a control in any record other than the last record, including the new record (the blank record below the actual last record). Tab to the end and then press [Tab] one more time. The procedure gives focus to the ShippedDate control in the main form and not, as you'd probably expect, to the next record in the continuous form. The procedure needs to restrict this behavior to the last record. Return to the subform's module and modify the KeyUp event as shown in Listing B.

Return to the form in form view and select the first or second record (but not the last or the new record) and cycle through the controls. When you reach the Discount control, pressing [Tab] will cycle through to the next record in the continuous form. Unfortunately, there is still a problem. Select the new record and cycle through its controls, including the last control.

The code you just added checks each record to see whether it is the last record in the form. When the last record has the focus, the if statement gives focus to the next control in the main form's tab cycle. The new record isn't the last record, so the SetFocus method isn't executed. This problem is easy to remedy with a simple property check. Return to the subform's module and modify the event again, as shown in Listing C. If the subform you're using doesn't allow you to enter new records in the subform, this additional code isn't necessary, as the new record won't be displayed.

Return to the form in form view and cycle through the subform's controls one more time. When you reach the last control in the last record or the last control in the new record the KeyUp event will be fired. As a result, it gives the focus to the next control in the cycle on the main form—the ShippedDate control.

One more problem
The event procedure in Listing C works in the example, but it is not appropriate for every case. It doesn't allow for situations when the subform is the last control in the main form. Ordinarily, pressing [Tab] or [Enter] when the last control in the subform's last record has the focus will display the main form's next record. However, a continuous subform has the potential to trap you on the current record if the subform is the last control in the main form. There's no automatic way to give focus to the main form's next record.

In this case, let the KeyDown event give focus to the main form's next record instead of to the main form's next control. To do so, open the main form (frmOrders), delete all the controls below the subform, and save the changes as frmOrders2. (You don't have to rename the form, but it might be less confusing to work with two different main forms.) Next, replace the discount control's KeyDown event procedure (from the previous example) with the procedure in Listing D.

Return to the main form in form view and select a control in the last or new record and press [Tab]. When you press [Tab] when the last control has the focus, the event procedure executes the following statements to give focus to the main form's next control:
frm.RecordsetClone.MoveNext
frm.Bookmark = frm.RecordsetClone.Bookmark

An If…ElseIf...Then block might not be the most efficient way to execute the action statements in either example, but this arrangement does give the clearest view of the logic flow and responses.

Wizard main form/subform
Using the wizard to produce a main form/subform eliminates most of the navigational problems encountered with embedded subforms. Although using a form wizard may be an option to reducing navigational problems, the wizard can be limited in other respects. You may find the form wizards a big help while prototyping, but most developers seem to prefer creating forms from scratch.

Be consistent
Adding a continuous subform to a form can create navigational problems for your users. By creating an event procedure for the subform's last control that gives focus to the next control in the main form or displays the main form's next record, you can build consistent navigational techniques from form to form.

About

Susan Sales Harkins is an IT consultant, specializing in desktop solutions. Previously, she was editor in chief for The Cobb Group, the world's largest publisher of technical journals.

2 comments
gelkins4
gelkins4

Susan thank you for all your great articles!  TechRepublic- please keep your links properly updated!

anehmer
anehmer

This is exactly what I need and the links for Listings A, B, C, and D are broken. :(((( I know 2003 is stone age, but unfortunately that's what I'm still stuck with at my company.

Editor's Picks