Chapter 14

Navigating in Forms and Applications


An expert solution does not point users to the Database window and let them guess what to do next. On the contrary, the hallmark of an expert solution is how well it guides the users to data and helps them understand it, manipulate it, and perfect it. This chapter describes techniques that you can apply to your forms and program code to help your users navigate smoothly through your applications. It provides strategies for the following form development areas:

Controlling user movement between the forms in your application

Providing switchboard forms for navigation

Using menus and toolbars to navigate and to initiate application events

Storing application information where forms can utilize it

Paradigm D‚j… Vu


Since our first Access application, we have always enforced a high degree of modality (one form at a time). To us, a linear model is the only choice for database applications because the developer is highly motivated by the needs of data integrity to force the user to finish one task before starting another.


Thus, in 1992 and 1993 when Microsoft was highly touting the Multiple Document Interface (MDI), we were building applications that were very linear, using a Single Document Interface (SDI) paradigm instead. We believed our users were not power hungry or sophisticated enough to be juggling multiple forms, records, or data management processes at the same time. Instead, we presented them with one form at a time and a clear path forward and backward between related records and forms.

So, it was with a sense of satisfaction that I observed a Microsoft presentation in 1994 and listened to an Excel program manager extolling the benefits of their new tabbed workbook layout. Their research had shown that users were most likely to work on one task at a time instead of tiling multiple windows. He also said that Microsoft had decided to downplay the use of MDI in their Office product line.

Although the Database window is a wonderful place for you as a developer to hang out, it's a dangerous place for users. They can delete your objects, modify your code, enter records into tables out of sequence, bypass batch processes, and generally make trouble (usually unintentionally). When you create a solid application centered around expert forms, you must consider whether your users should ever see the Database window at all. Or, perhaps they should be directed through the application from start to finish.

Most of our applications do not provide the everyday user with access to the Database window. Instead, users navigate from form to form, occupying a very restrictive and well-defined application universe. Chapter 17, "Bulletproofing Your Application Interface," discusses strategies for locking the user out of the Database window.

Our preferred user interface model directs users through forms one by one, or at least limits them to some small, manageable group of related forms at the same time (see Chapter 8, "Designing Effective Interfaces," for more information on user interface design). Data-bound forms really should be structured into a cohesive, restrictive application. Consider the following problems that can occur when you enable your users to open multiple Access forms at random:

To prevent these situations, you can restrict how users interact with your applications by establishing limits that dictate the application flow. By enforcing only permitted behavior and events, users are guided through the application in a friendly and logical manner.

Providing your users with a directed path through the application entails some extra effort. It requires money or time or both. Consequently, when you are building expert forms, the best strategy is to develop repeatable systems and then to use them in multiple applications, instead of reinventing the user interface paradigm with each new application that you create.

Your standard user interface model will include some or all of the following components:

Using Switchboard Menus

At the top of almost every application's navigation pyramid is a switchboard menu. These are usually constructed as a single-page form with a button for each main functional area in the application. Each menu button contains attached code that opens a form, prints a report, runs a data process, or opens another switchboard.

There are as many varieties of switchboard menus as there are developers. The next four topics describe my favorite options for switchboard navigation in expert solutions.

Using and Extending Switchboard Manager Menus

In addition to switchboard variances between developers, Microsoft's examples for switchboard menus were not consistent in previous years. However, the new Switchboard Manager add-in defines a standardized approach for switchboard menus. You may opt to use this new look for your switchboards, and you may even choose to use the code generated by the add-in as a starting point for your switchboard functionality (see Figure 14.1).

Fig. 14.1

This simple menu was generated by the new Switchboard Manager add-in.


The type of switchboard created by the Access 97 Switchboard Manager is generally the same as the layout used in Access 95.

The Switchboard Manager's interface model uses a column of small buttons, each with a text descriptor next to it, as shown in Figure 14.1. The strength of this design style is the capability of conveying varying lengths of explanatory text to the user for each application option without creating wide command buttons.

The Switchboard Manager places eight buttons on the form and loads the text for each button from a table called Switchboard Items. The table has the structure shown in Figure 14.2. The Switchboard Manager also creates code behind the form, which loads the switchboard options and executes user selections based upon entries in the supporting table.


If you use the Switchboard Manager to create a switchboard and then later add, delete, or rename form objects in your application, the switchboard does not detect the changes and may no longer work. You may need to update the driving table or re-run the add-in.


Fig. 14.2

This menu item table supports the Switchboard Manager menu in Figure 14.1.

Although the Switchboard Items table is created by the add-in, once you understand the structure you can modify the switchboard menu yourself by changing the table records. You can use the generated switchboard as a template, making changes to it to customize the look and functionality for your specific application. What follows is the table structure:

1

Display the switchboard page whose SwitchboardID field contains the number designated in the Argument field for the record.

2

Open the form in add mode whose name is specified in the Argument field for this record.

3

Open the form in edit mode whose name is specified in the Argument field for this record.

4

Open the report in preview mode whose name is specified in the Argument field for this record.

5

Run the Switchboard Manager to customize the menu.

6

Close the current database (ending the application).

7

Run the macro named in the Argument field.

8

Run the function or Sub procedure named in the Argument field.

1

The page designator for the switchboard page. This designation is only used when running the Switchboard Manager and it must be the string "Default" for the application's default menu page.

2

The name of the form to open for entry.

3

The name of the form to open for editing.

4

The name of the report to preview.

5

No value (blank).

6

No value (blank).

7

The name of the macro to run.

8

The name of the procedure to run. The procedure must be a Public function and must be stored in a standard module.

To create switchboards for expert forms, you can run the Switchboard Manager to build a switchboard form and its code, then you can add or modify table entries according to your application's needs. What follows are some suggestions and pitfalls related to Switchboard Manager menus:

Listing 14.1 AES_Frm2.Mdb-Custom Modifications to the HandleButtonClick() Function

...

' Run code.

Case conCmdRunCode ' Command = 8

Application.Run rst![Argument]

' Print directly, added by developer.

Case conCmdPrint ' Command = 9

DoCmd.OpenReport rst![Argument], acNormal

' Any other command is unrecognized.

Case Else

MsgBox "Unknown option."


Once you have changed a switchboard's options, structure, or code, you may be unable to use the Switchboard Manager to maintain the form-depending on the scope of your modifications.

To execute a function whose name is stored in the Argument field of the table, the Switchboard Manager includes the following line of code in its forms:

Application.Run rst![Argument]

Note the use of the new Run method to execute a function whose name is supplied by an expression. This method provides a simple mechanism for executing a function without storing the function's name in the calling code. Previously, you used the Eval() function to run a procedure whose name was supplied as an expression or variable at runtime. Now, you might prefer to use Run as it is superior to Eval() in the following two ways:

' Pass one argument to the called function

Application.Run strProcName, Me.Name

Using the Switchboard Manager to shell out form menus can be an effective use of an Access add-in.

Using List Boxes in Menus

Unlike a button-based metaphor, a list box enables the list of menu options to be essentially infinite. For example, the list of options in a list box menu could conceivably exceed fifty or a hundred items.

As an additional bonus, a list box menu can be driven by a table containing its options, much like the Switchboard Manager metaphor shown previously. Modifying the application's capabilities becomes simply a matter of adding or editing the table's records. Figure 14.3 shows a menu layout built around a list box.

Fig. 14.3

A switchboard menu is built around a List Box control.

By altering the width of the List control, you can provide room to display the menu option information with the degree of clarity you want. For example, a list box several inches wide can display as many as a hundred characters of descriptive text for each menu option.

The table that supports the menu shown in Figure 14.3 is captured in Figure 14.4. The table specifies the sort order that the menu items are displayed using a field called SortKey. Options in menu lists are usually grouped by functional area, then sorted based on any model that supports users. The following are the most common:


Note in Figure 14.4 how I create numbering blocks for each group of menu options (for example, 20 through 29 are reserved for printing features). Such gaps enable you to add menu items later without renumbering the majority of table records.

Fig. 14.4

This menu item table supports the ListBox-based menu in Figure 14.3.

The function MenuDummy listed repeatedly in the figure is a placeholder indicating that a menu function has not yet been created for the option. In your menus, the MenuProc column for each menu item would name the related function for the option.

The following are other techniques I've employed on the list box menu in Figure 14.3:

SELECT MenuItem, MenuProc FROM tmnuList _

WHERE UserLevel >= [Forms]![fmnuList]![zhtxtUserLevel] _

ORDER BY SortKey


Without a title bar, a borderless form can't be moved by the user. Therefore, this style is most useful for modal menu forms, which are usually displayed by themselves on the screen and never on top of another form.

List box menus can be useful for applications that the feature set changes frequently, the process flow might frequently be redesigned, the order of options must change on a per-user basis, or development resources for friendlier menu forms are limited.

Creating Tabbed Menus

When you create a switchboard menu for a complex application, you might need to display dozens of options to users. Grouping the options together based upon their functionality or attributes enables you to present the menu options to users in manageable subsets.

One of the great interface devices for helping users navigate through subsets of information is the tabbed dialog box. Applying this model to switchboard menus is easy with the help of Access 97's new TabControl object.

Figure 14.5 shows a simple switchboard menu that uses the tab control to group menu items by functional area.

Fig. 14.5

This switchboard menu is built around the new tab control.


The sample menu in Figure 14.5 uses List Box controls embedded on tab pages to display items for users. Like other menus centered on list boxes, a table is used to feed user options to the list. The table for the tabbed menu in Figure 14.5 is shown in Figure 14.6.

Fig. 14.6

This menu item table supports the tabbed menu in Figure 14.5.

The table structure to support the tabbed menu includes a TabNum field containing the target tab (page) number for each item. When a tab is selected, the source for the list box is rebuilt to pull option information from the table for the selected tab, as in Listing 14.2 shows.

Listing 14.2 AES_Frm2.Mdb-Rebuilding the List Box Items when a Tab Is Selected by the User

Private Sub tabMenu_Change()

' Purpose: Set the listbox focus

Dim strLst As String

' The tab page number is in the control name

strLst = "lstOption" & CStr(Me!tabMenu)

Me(strLst).SetFocus ' Move to the list box

If Me(strLst).ListIndex = -1 Then ' Nothing selected

Me(strLst).ListIndex = 0 ' Select the first item

End If

Call cbfStatusSet ' Set the status bar text

End Sub

Private Sub cbfStatusSet()

' The status label text is in the second column of the list

Me!lblStatus.Caption = Me("lstOption" & CStr(Me!tabMenu)).Column(1)

End Sub


Form controls can bind with specific tabs of the TabControl object, hiding and showing with the parent tab. Nevertheless, the form itself is still aware of the controls and they can be addressed directly with Me!controlname or Me(expression), as shown in Listing 14.2.

In a list box menu, the user selects an option by double-clicking a list item or by clicking the Select button. Note the following user interface devices that are employed in the tabbed menu in Figure 14.5:

Me!lblStatus.Caption = Me("lstOption" & CStr(Me!tabMenu)).Column(1)

Application.Run Me("lstOption" & CStr(Me!tabMenu)).Column(2)

Tabbed menus are less flexible than the simple list box menus shown in the previous section because the form layout is not completely table-driven. (In other words, a developer must add another tab to the control's layout each time a new group of options is needed, or write the code to do so dynamically.) Nevertheless, such menus are very attractive, well organized, and highly functional.

Creating Fast, Simple Switchboards

Although it is appealing to employ one of the three previously discussed menu techniques in your application, there are times when the old-fashioned, brute-force approach has more value than newer, advanced approaches. In the old-fashioned paradigm, your switchboard has buttons and the buttons have Click events that run code directly. Figure 14.7 shows a simple form that applies the older model.

Fig. 14.7

The simplest variety of switchboard form has only a few buttons and no underlying menu options table.

Compared to table-based menu forms, this option is very inflexible. To add a feature to the menu, a developer must add a button and its related code to the form. On the positive side, simple switchboards load and execute faster than forms tied to a menu options table, and might be better for very simple applications and for applications targeted to slower workstations.

Another positive aspect of button-based menus is the capability to selectively enable and disable features "on the fly" by toggling the Enabled property of each button. Achieving this result is much more difficult in a menu based on a list box, in which you must change a flag value in the underlying table and requery the menu. (Consequently, you must also keep the menu list in the local database so that table changes do not affect other users, which may adversely affect your application maintenance strategy.)

As an example, assume that the workflow required in a company using an invoice entry system is as follows:

  1. Scott enters invoice records for payments.
  2. Diana, the boss, approves each invoice in the batch.
  3. Scott posts the batch to the ledger table when all the invoices are approved.

Each of the invoice entry tasks is controlled by a separate button on the main switchboard form. The following list describes how the buttons follow the workflow:

  1. When Scott logs in, he presses the Invoices button on the main menu, which always is enabled so that he can enter new invoice records. However, the Posting button on his menu is only enabled when all of the invoices that he entered have been approved by Diana for payment.
  2. When Diana logs in, the Invoices button on her main menu only is enabled if Scott has entered at least one invoice for approval since the last batch posting.
  3. Each time Scott's application returns to the main menu form, code runs to detect whether or not every invoice in the batch has been approved for payment by Diana. If so, the application sets his Posting button to an Enabled state so that he can press the button to post the batch.
  4. After Scott posts a batch, there are no invoices for Diana to review and approve until he enters another batch. Each time her main menu is displayed, logic runs to enable or disable the Invoice button depending on the availability of invoices to review.

Thus, Diana can see if she has any invoices to review by simply looking at the button status on her main switchboard, and Scott can also see from the menu if it is time to post a batch.

These very simple programming techniques convey immediate status information to the users via the menu form and also prevent the premature posting of a batch of invoices. In addition, the techniques produce large and highly visible results for the small amount of development time it takes to create a "smart" menu.

Navigating Between Forms

When you first create your application, you build table structures. Next, you produce the forms and reports that give your users access to their data.

Once your forms are created, you must add the navigation elements that differentiate a simple database file from a true application:

The navigation code you implement must open, close, hide, and show forms in a desired sequence. Also, the code must never permit either too many forms or too few forms to be displayed to the user. The next three topics discuss the mechanics of this navigation.

Hiding versus Closing Forms

Opening Access forms can be one of the slowest areas of your application. As you move from frm1 to frm2, if you intend to return to frm1 when you are finished, there may be no need to close the form and reopen it later. Hiding frm1 instead, and re-displaying it when you need it, has great performance advantages.

For example, assume your application opens frmCust to edit a customer record. To change a customer's phone number, frmCustPhone must be opened from frmCust. When this occurs, you have three choices regarding how to handle the required navigation, as follows:

To hide a form, simply set its Visible property to False in your program code. To redisplay the form, set the property to True again. You can also use DoCmd.OpenForm formname to redisplay a hidden form. There is no performance advantage for either technique because OpenForm merely makes the form visible if it finds that the form is already loaded. The effect on the form's recordset is the same whether you change the Visible property or use an OpenForm. In either case, the data for the form is refreshed, but not requeried.


Applying the OpenForm method to a form that is already open but hidden enables you to alter the view, filter, Where clause, or OpenArgs as the form is redisplayed. However, be alert to the potential for confusion when users go back to a form they were viewing earlier and they find that its characteristics have changed.


Under what circumstances should you select one approach over the other? You should use OpenForm in an application that is not using expert forms techniques, but which is attempting to be user-friendly. Consider what happens in an application that the user can press a button on frm1 to open frm2, yet neither form is made modal nor hidden in the process. Imagine the user moving the focus from frm2 back to frm1 and closing it, then returning to frm2 and closing it. If frm2 has any code to make frm1 visible again by setting its Visible property, no instance of frm1 currently exists, and an error results. In contrast, if the user gets in this situation and frm2 uses OpenForm to return to frm1, the code will either redisplay frm1 if it is open and hidden or open it again if it is closed. In either case, the code will not fail. Thus, if your interface is not highly restrictive, OpenForm provides an insurance policy that prevents navigation collisions.

It may seem at this point that OpenForm is a good way to navigate backwards through forms, but there is a downside. Continuing our example, assume that frm1 was originally opened with a Where clause supplied by its calling form. When the user closed frm1 manually, then closed frm2 and invoked an OpenForm on frm1, the OpenForm could not possibly know the filter information originally passed to frm1. Thus, when frm1 is displayed on the way back from frm2, it has a different (that is, unfiltered) recordset than was originally presented to the user. Usually, this situation is quite inappropriate.

Not only should this example dissuade you from relying on OpenForm to navigate backwards through forms, it should alert you to the dangers of enabling users to work with more than one form at a time. It should clarify why my preferred techniques for navigation depend upon hiding the first form as the second is opened, and redisplaying the first form with Visible = True when the second is closed.

When using the technique to hide and show forms during navigation, be wary that hiding a data entry or edit form may introduce several dangerous concurrency situations into your application, as follows:

Obviously, you must be consciously aware of data currency situations as you craft your application navigation. If a current operation (including data additions, edits, and deletions) might affect the accuracy of data on a hidden form, set a flag during the operation to requery the hidden form as it is displayed. Similarly, if the user edits lookup table values or otherwise affects records in the combo or list boxes of a hidden form, the affected controls must be requeried by your code.


Only requery the hidden object that is specifically affected by data changes. In other words, requery a combo box directly if only that control's data was affected, and only requery an entire form when absolutely necessary.

If your code must requery a form, remember that such an event changes the current record displayed to the first one in the form's recordset. If you want to display the same record to the user after the requery that was showing previously, store the primary key of the current record in a variable before sending the Requery method. After the requery, synchronize the form to the record that was originally displayed. This form synchronization technique is described in Chapter 16, " Presenting Data to Users."

Opening Forms in Sequence

When hiding and showing forms for navigation purposes, you must keep track, in your program code, of the forward flow of the application so that you can go backward to the appropriate object. The simplest method is to place commands in your form module code to display the calling form when a form is closed. This strategy, however, is too inflexible to be very useful. For example, if your code opens frm2 from frm1 then hides frm1, the Close button event code for frm2 might look something like this:

Forms!frm1.Visible = True

DoCmd.Close acForm, Me.Name

Because this code references frm1 by name, it does not enable you to call frm2 from any other form except frm1. In keeping with the expert forms objective of creating reusable objects, you should be able to call a properly coded form from practically any other form in the application. Thus, each form needs a custom property (let's call it CalledFrom) that can be used to store the name of the form that called it when it was loaded. Looking in the property, the form knows which form to redisplay as it closes itself.

What follows are several possibilities for providing a CalledFrom property setting to assist with form navigation:

' In the calling form, pass its name to the called form via OpenArgs

DoCmd.OpenForm "frm2", , , , , , Me.Name

' In the called form, redisplay the calling form during close

Forms(Me.OpenArgs).Visible = True

' In the calling form, pass its name to the called form via the Tag

DoCmd.OpenForm "frm2"

Forms!frm2.Tag = Me.Name

' In the called form, redisplay the calling form during close

Forms(Me.Tag).Visible = True

Public mvarCalledFrom As Variant

DoCmd.OpenForm "frm2"

' Set the caller's name into a form property

Form_frm2.mvarCalledFrom = Me.Name

Listing 14.3 AES_Frm2.Mdb-Passing the Calling Form Name into a Custom Property

' This routine is in the calling form frmPrpCalledFrom1

Private Sub cmdForm2_Click()

' Purpose: Open the dependent form

DoCmd.OpenForm "frmPrpCalledFrom2"

' Set the caller's name into a form property

Form_frmPrpCalledFrom2.prpCalledFrom = Me.Name

Me.Visible = False

End Sub

' The remaining routines are in the called form frmPrpCalledFrom2

' In the Declarations section, create the property variable

Private mvarCalledFrom As Variant

Property Get prpCalledFrom() As Variant

' Purpose: Retrieve the calling form name from the custom property

prpCalledFrom = mvarCalledFrom

End Property

Property Let prpCalledFrom(rvarForm As Variant)

' Purpose: Custom property for the calling form

mvarCalledFrom = rvarForm

End Property

Private Sub cmdClose_Click()

' Purpose: Close the form

DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()

' Purpose: Open the calling form

Forms(Me.prpCalledFrom).Visible = True

End Sub

' This code resides in the calling form

DoCmd.OpenForm "frm2"

' Set caller's name into a form control

Forms!frm2!zhtxtCalledFrom = Me.Name

Me.Visible = False

Figure 14.8 shows a conceptual diagram of the various techniques for assigning a custom property value to a form. The options are equally effective, and any of them is an appropriate choice to use as a form navigation strategy.

Fig. 14.8

You can use a variety of methods to assign custom values (user-defined properties) to a form.


You may want to try each of these techniques in a different application before you select a standardized approach for your development team. Each has strong and weak points, so one of the techniques will probably be a "best fit" for your unique development needs.

My development team and I favor two of these techniques. For new applications, we use the third option on the list: creating a simple property in each form as a module-level variable. It's very easy to implement-we simply modified our company Form Wizards to create the single-line property variable declaration in each new form. The only drawback is that, as a variable, the CalledFrom value is cleared during debugging if you select the Reset menu option or toolbar button. Thus, after a code reset, you may have to navigate backwards manually from the current form by using Window, Unhide to display the previous form.

For applications developed in Access 2, we have always used the last option on the list: a hidden system control placed on each form. We continue to use this in new Access 2 applications, and we keep the methodology in place when converting Access 2 applications to Access 97-there's no economic value for our clients to switch from the TextBox approach to the custom property approach.

The navigation techniques described in this section are highly flexible because you are no longer hard-coding the path back through your application's form stack. Because any given form no longer permanently points to a specific calling form, but instead points to the form that called it this instance, you can easily reorder the sequence of events in your application with no backwards navigation changes. You can also call one form from many different forms, thus achieving the objective of reusable objects.

Once you have standardized a form navigation strategy, you can formalize it by creating a library routine to make your coding tasks easier. Listing 14.4 shows our standardized routine for opening forms.

Listing 14.4 AES_Frm2.Mdb-A Standardized Library Routine for Opening Forms

Public Function lci_FrmOpenStd(rstrFormTo As String, _

rstrFormFrom As String, Optional ByVal nvvarFlt As Variant, _

Optional ByVal nvvarWhere As Variant, _

Optional ByVal nvvarMode As Variant, _

Optional ByVal nvvarWndMode As Variant, _

Optional ByVal nvvarOpenArg As Variant) As Boolean

' Purpose: Standardized form open

' This routine plugs view argument to Normal only

' Sets pvarCalledFrom and pintEditMode in the called form

' Arguments: Requires only the first two arguments

' rstrFormTo:=Name of form to open

' rstrFormFrom:=Name of calling form

' nvvarFlt:=Filter name or Null

' nvvarWhere:=SQL WHERE clause or Null

' nvvarMode:=acAdd, acEdit, acReadOnly

' nvvarWndMode:=acNormal, acHidden, acIcon, acDialog

' nvvarOpenArg:=OpenArgs to follow the calling form name

' Returns: True/False, True=form was opened

' Examples: lci_FrmOpenStd("frmCust", Me.Name)

' lci_FrmOpenStd("frmCust", "", "qfltCust", nvvarMode:=acAdd)

' Pseudocode:

' 1. Plug any missing Optional arguments to Null

' 2. Open the called form

' 3. Set the navigation properties

' 4. Hide the calling form

On Error GoTo lci_FrmOpenStd_Err

Const cstrProc As String = "lci_FrmOpenStd"

Dim intLoc As Integer

' Cleanup optional args

If IsMissing(nvvarFlt) Then

nvvarFlt = Null

End If

If IsMissing(nvvarWhere) Then

nvvarWhere = Null

End If

If IsMissing(nvvarMode) Then ' Default data mode to Edit

nvvarMode = acEdit

End If

If IsMissing(nvvarWndMode) Then ' Default window mode to Normal

nvvarWndMode = acNormal

End If

' Set first argument of OpenArgs to calling form

If IsMissing(nvvarOpenArg) Then

nvvarOpenArg = Null

End If

' Open the form

DoCmd.OpenForm rstrFormTo, acNormal, nvvarFlt, nvvarWhere, nvvarMode _

, nvvarWndMode, nvvarOpenArg

' Set the properties of the form, ignore if they don't exist

On Error Resume Next

Forms(rstrFormTo).pvarCalledFrom = rstrFormFrom

Forms(rstrFormTo).pintEditMode = nvvarMode

On Error GoTo lci_FrmOpenStd_Err

' Hide the calling form

If Len(rstrFormFrom) > 0 Then

Forms(rstrFormFrom).Visible = False

End If

lci_FrmOpenStd = True

lci_FrmOpenStd_Exit:

Exit Function

lci_FrmOpenStd_Err:

Call lci_ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number _

, Err.Description, True)

Resume lci_FrmOpenStd_Exit

End Function

Why use a complex library routine to perform something as simple as opening a form? I can offer three justifications:

If you keep too many hidden forms open at one time, the performance of your application may suffer. We have successfully nested forms twelve levels deep using the navigation techniques described here, but with an expected performance hit on the application. However, most applications are not-and should not be-designed to lead the user to so many layers in a particular logic path.


If you find your application stacking more than four or five hidden forms at one time, consider reviewing the application logic and the users' needs in order to determine if the data entry/edit objective that produces this much stacking can be achieved by designing the task in another way. Users often find applications confusing when they must drill through many layers of forms to find a specific record or task.

Exploring Advanced Form Navigation Techniques

In the preceding section, we explored some standard approaches to navigating through forms and tracking the form call tree. The techniques described were adequate for handling the majority of your navigation needs, or for ideas that you can explore when devising your own approach.

In order to be comprehensive, in this section I'll mention some more advanced form navigation concepts. Although these concepts will not be applicable to all of your applications, they should give you food for thought nevertheless.

Opening Forms via Code Reference

Chapter 12 discusses how Access forms are now class modules, or templates from which multiple form instances can be created. It is possible to open a form by referring to a property of the form class directly in code, which invokes the default instance (a single instance) of the form. The following code line opens and displays the default instance of the frmCust form:

Form_frmCust.Caption = "Hi Mom"

The following are the features and limitations of such a direct-referencing scheme:

Form_frmCust.Visible = True

Also, I noted in Chapter 13, "Mastering Combo and List Boxes," that forms can be instantiated via the use of form variables. The following code opens an instance of the frmCust form:

Dim frm As New Form_frmCust

frm.Visible = True

Unlike the previous syntax that opened the default instance of the form without a variable, the creation of a form variable in this manner enables multiple instances (via multiple variables) to be created from the same form class. This capability can be very useful when using forms as hidden classes that contain code engines. But I do not recommend creating programmatic form instances this way as a navigation strategy. The lifetime of the called form is limited to the lifetime of the calling procedure or the referencing variable, whichever expires first.

Advanced Form Stacking Concepts

"Opening Forms in Sequence" in this chapter explained strategies for keeping a "stack" of open forms by teaching each form how to return to its calling form. There are two other mechanisms for keeping a stack that should be mentioned here: a collection of forms and an array of form names.

Collections of forms are described in detail in the "Using Form and Control Collections" section of Chapter 12. Here, for our purposes, it is sufficient to note that the technique involves adding a reference for each form object to a collection of forms when they are opened. However, collections in Access have the following serious syntactic limitations (as described in Chapter 12):

These limitations make it very challenging to use a collection to determine which form to display when another form is closed. As forms are added to and removed from a collection, the "stack" becomes rearranged and this collection behavior makes it difficult to manage the restructure.

Thus, a collection of forms can serve a variety of purposes, but navigation is not one of them.

In contrast, maintaining an array with a form call tree is an easier proposition. Simply create a Public string array to contain form names:

Public gstrFrmStack() As String

As each form is opened, add its name to the array:

ReDim gstrFrmStack(UBound(gstrFrmStack) + 1)

gstrFrmStack(UBound(gstrFrmStack)) = Me.Name

When a form is closed, your code can determine where that form is located in the array, remove its entry, and walk backwards through the array to the preceding item, which holds the name of the calling form. See the sample in Listing 14.5.

Listing 14.5-Finding the Calling Form's Name in a Navigation Array

Function FrmCallerGet(rstrName As String) As String

' Purpose: To return the name of the next form down the stack

' Arguments: rstrName:=Name of called form

Dim iint As Integer

For iint = 0 To UBound(gstrFrmStack)

If gstrFrmStack(iint) = rstrName Then

Exit For

End If

Next iint

FrmCallerGet = gstrFrmStack(iint [ms] 1)

End Function

Closing Forms in Sequence

In the previous section, "Opening Forms in Sequence," the code samples clearly reflected my preferred approach to closing forms. The code is summarized in Listing 14.6.

Listing 14.6 AES_Frm2.Mdb-Closing a Form and Opening the Calling Form

Private Sub cmdClose_Click()

' Purpose: Close the form

DoCmd.Close acForm, Me.Name

End Sub

Private Sub Form_Close()

' Purpose: Open the calling form

Forms(Me.prpCalledFrom).Visible = True

End Sub

The following are a few interesting points about closing forms:

A slightly different variation on this philosophy occurs when you decide to write a library routine to handle the closing of forms. Listing 14.7 shows a standardized form close routine that works in conjunction with the standardized open routine shown in Listing 14.4.

Listing 14.7 AES_Frm2.Mdb-A Standardized Library Routine for Closing Forms

Public Function lci_FrmCloseStd(rfrm As Form) As Boolean

' Purpose: Close the form and move back up the stack

' Assumes the form to display next is in rfrm.pvarCalledFrom

' Arguments: rfrm:=Form object to close

' Returns: True/False, True=form was successfully closed

' Example: lci_FrmCloseStd(Me)

' Calls: lci_IsBlank

On Error GoTo lci_FrmCloseStd_Err

Const cstrProc As String = "lci_FrmCloseStd"

Dim varWork As Variant

On Error Resume Next

varWork = rfrm.pvarCalledFrom

On Error GoTo lci_FrmCloseStd_Err

' Show the calling form, ignore if doesn't exist

If Not lci_IsBlank(varWork) Then

Forms(varWork).Visible = True

End If

DoCmd.Close acForm, rfrm.Name

lci_FrmCloseStd = True

lci_FrmCloseStd_Exit:

Exit Function

lci_FrmCloseStd_Err:

Call lci_ErrMsgStd(mcstrMod & "." & cstrProc, Err.Number _

, Err.Description, True)

If lci_FrmIsLoaded(rfrm.Name) Then ' If the close failed

Forms(varWork).Visible = False ' Put stacked form away

End If

Resume lci_FrmCloseStd_Exit

End Function

Note that this library routine closes the named form; thus, it can't be located in a form's Unload or Close event. Such a routine usually goes in the event for your custom Close button on the form.


Locating the library routine in your button event brings to light the primary navigation challenge that will confront you: your application can't enforce navigation unless you control how the user closes the form. If a user can close a form with the Control menu or File, Close from the menubar, your navigation library routine will be skirted. Therefore, unless you are committed to absolutely controlling how the user closes a form, abandon the library routine and employ the simpler approach described at the beginning of this section.

Enforcing the Modal Interface

When you create your application according to the Single Document Interface principle, there will be only one form displayed at a given time. To facilitate this metaphor, you must tightly control the display properties of your application's forms.

Displaying Dialog Box Forms

Although one of our expert forms objectives is to reduce the number of objects displayed to the user at a given time, there are places in your applications where a form should "pop up" on top of a related form in order to gather additional information from the user. Such a form is called a dialog form or dialog box, as exemplified by the About dialog box that Figure 14.9 shows.

Fig. 14.9

An About dialog box is a common example of a modal pop-up form.

If a dialog box is displayed in a non-modal state, a user can click behind its window to the underlying form and "break free" of your intended navigation. To more strongly enforce the flow in this scenario, set the Modal property to True (Yes) for each of your dialog box forms. A modal form, once opened, will not release control (focus) to any other form until it is closed or hidden.

For dialog boxes that have command buttons like OK and Cancel, you should also set the PopUp property to True, which disables access to the menubar and toolbar. Finally, setting the BorderStyle property to Dialog creates the proper fixed border as in dialog boxes in Office applications. The dialog box form in Figure 14.9 has the following settings:

Modal = True

Popup = True

BorderStyle = Dialog

A second technique for creating dialog forms is to open the form as a dialog box using the WindowMode argument of the OpenForm command, as in the following code sample:

DoCmd.OpenForm "frm1",,,,,acDialog

This command overrides the form's current settings for the Modal, PopUp, and BorderStyle properties to create a form that mimics the three property settings I listed earlier for dialog forms.

As a general rule, the property settings (Modal, Popup, and BorderStyle) that you set and save with a form are useful for determining how the user views the form from the Database window. Because it is important for a developer to control the form view that the user sees when the application is running, setting the window mode argument of a form from code during its OpenForm method is a critically useful habit that you should develop.


Testing and debugging dialog forms is easier if you are able to open the forms non-modally from the Database window during development. To do this, you can keep the Modal and PopUp properties of your dialog box forms set to False, and instead have your code open these forms with the WindowMode argument of OpenForm set to acDialog.


The downside of this strategy is that the dialog box nature of your form will not be reflected in its saved properties. Thus, other developers reusing your form object will need to know the special OpenForm requirements of the form when they use it.


Dialog forms have some additional interesting characteristics, as follows:

Displaying Entry/Edit Forms

Because entry/edit forms usually make use of the menubar and toolbar, you should not set their PopUp properties to True nor open them as a dialog form via OpenForm.

However, when a form is set to be modal but not pop-up, the user can access the menubar and toolbar without having access to any other displayed forms. This is exactly the situation I want in the majority of my expert forms.


You can open one modal form on top of another, so you can actually stack entry forms while still preserving the enforced navigation of your application. However, users might be confused by having forms shown to which they can't send the focus.

In addition to being a display concept, modality is also a navigation concept in the expert forms model, which states that the user should be doing only one thing at a time. To strictly enforce modal navigation, consider setting the ControlBox property to False (No) for each application form. Once removed, the user can't close the form without triggering an event that your code can trap.

In summary, I use the following settings to serve my entry/edit form navigation objectives:

Modal = True ' Or alternately use OpenForm with acDialog

Popup = False

BorderStyle = Dialog ' Or alternately use OpenForm with acDialog

ControlBox = False


Regardless of the actual settings for the MinMaxButtons and CloseButton properties, when ControlBox is set to False on a form, the Close, Minimize, and Maximize buttons are not displayed.

The form in Figure 14.10 has no Control menu, Close button, Minimize button, or Maximize button. To completely control the application's flow, I eliminated the user's ability to go in any direction other than the one I intend.

Fig. 14.10

This expert form controls all aspects of user navigation.

Of course, without built-in tools on the form that the user can click to close it, you must add your own Close button to each form, as Figure 14.10 shows. From the Click event of your Close button, you can trap the user's intention to close a form at a very early stage (before the form has actually started to close), and react accordingly in your code by validating the record or performing a related operation.

If you enable the user to close a data form with the Control menu or built-in Close button instead, you are forced to place your final code for validation and other operations in the form's Unload event. This event occurs after the current record is saved, which makes the flow of events in your form overly complex and makes validation and other processes difficult to connect to the form.

When you require users to close a form with your button, you intercept save and close events at a manageable point in the application flow. Thus, in achieving a navigation objective, you have also taken a step toward making your validation objectives easier to achieve. See Chapter 15, "Protecting and Validating Data," for a discussion of record validation on forms.

Handling Form Keyboard and Resize Events

Simply removing the Control menu and Close button from a form goes a long way toward putting your application in control of its form navigation events. However, Access enables the user to bypass your efforts by providing keyboard shortcuts that replace the buttons you've removed.

For an entry/edit form opened modally, the user can press Ctrl+W or Ctrl+F4 to close the form. However, you can disable these keystrokes throughout the application by simply redefining them to do nothing via the AutoKeys macro. Chapter 17, "Bulletproofing Your Application Interface," discusses this technique in detail.

For a dialog form, Access disables both of these keystrokes automatically. However, this doesn't mean you are out of the woods because Access provides the Alt+F4 keystroke combination as a shortcut for closing dialog forms only. Because this combination cannot be remapped in AutoKeys, you will not be able to prevent the user from applying it. Therefore, if your dialog form must be aware of the instant when it begins closing, you must place code in the Unload or Close event to trap the user's intention. Alternately, you can attempt to detect and discard the Alt+F4 combination with a keyboard handling procedure, as described in Chapter 17.


When no dialog form is displayed, the Alt+F4 keystroke combination closes the current application and Access itself. Because this harsh exit may bypass some of your application code or potentially cause premature termination of a data process, you should discourage users from applying these keystrokes in any Access application or form.

In addition to undesirable keystrokes, your application should also be alert to one undesirable mouse event. If you choose to set a form's border to Sizable instead of my suggested Dialog value, users will be able to drag the form's border to a new size with the mouse. When they do this, they will expect your code to rearrange form control locations or sizes to fit the new form dimensions.

However, most forms are designed to be a specific size, and I've not built or seen more than a few applications over the years that trap the Resize event on a form and rearrange the size or shape of controls (this is very complex and expensive coding). Unless you intend to trap the resize and act accordingly, I can think of no good reason not to use the Dialog setting for your forms' borders.

Navigating via Menus and Toolbars

The form in Figure 14.10 used embedded (on the form) toolbar buttons to enable the user to move between records and close the form. Embedded buttons give the user of the form the ability to launch a form navigation or processing action.


In addition to buttons, you might enable the user to double-click a List Box, Text Box, Hyperlink, or Label control to open a form related to the current form. See "Synchronizing Record Displays" in Chapter 16, "Presenting Data to Users."


Instances arise in an application when navigation between forms with an embedded button is not efficient. The simplest example is provided by the ubiquitous About dialog box. You should not place an About button on each form simply to show this dialog box. Thus, you can add a menu option to the Help menubar, a custom menu, or a custom toolbar to display the dialog box. Because the About form is a dialog box, there are no associated navigation issues-simply open the form as a dialog box over the top of the current form and close the form when the user is finished, to return control to the current form. This procedure emulates that of Access and other retail applications.

A more complex situation arises when you choose to move beyond embedded buttons for navigation and you attempt to create complex menus or toolbars to help users move between forms and records.

Creating Your Own Record Navigation Routines

To enable users to navigate between records, you can very easily provide the internal Access record movement capabilities to a form. Simply enable any combination of the following features:

If the capabilities of the Access navigation buttons are satisfactory to your users, the built-in features listed are highly useful and cost effective. On the other hand, some situations may arise that you choose to replace the built-in navigation options with your own navigation features. The following application needs provide examples of such situations:

I generally prefer to add my own First Record, Previous Record, Next Record, Last Record, and New Record navigation buttons to forms, and to attach the corresponding Click event code. Such record navigation buttons were obvious in the form shown in Figure 15.10.

Fig. 14.10

A sample form.

The following two sections discuss replacing the Access navigation buttons with your own if you choose to follow my lead.

Replacing the Record Number Box

Access displays a record position indicator in the record number box on a form's navigation button control. User's often assume that this is an identifier for the record. Thus, if the number 4 shows in the navigation buttons, a user may send e-mail to cohorts directing them to "review customer record 4" or reports to Technical Support asking why this week's record 4 is different than last week's. I have long wanted Access to enable us to remove this number from the navigation buttons (see the sidebar). In the absence of such a capability, you must remove the navigation buttons entirely and create your own record position indicator and navigators as required.

Everybody's Number One


In one of our first Access applications years ago, we created a form to track customer information for a client. When viewing the customer information form, the user could click a button that opened a second, pop-up form to display only the phone numbers for the current customer. We used the Access navigation control on the pop-up form, and because our client had only entered one phone number for each of his customers, each record that appeared on this pop-up had the number 1 in the record number box.


After using it for a few days, our client called and said, "Your program has a really big bug. Every customer in my application has exactly the same phone number." It took us just a few minutes to sort out the real problem.

Even though the phone number and the primary key of the phone number record were displayed differently on each pop-up, the client had focused on the positional number in the record number box, mistaking it for the phone number record key. Thus, to him, each time the pop-up loaded, the number 1 in the record number box indicated that this was the same record (with an ID of 1) that was displayed for all of his customers. (Never mind that the actual phone number on the screen was different, he based his opinion on the assumption that the record number was the primary key.)

The very next day, we threw up our hands, abandoned the built-in navigation button control, and started to code our own navigation button routines.

If your forms need to show a positional record number, Listing 14.8 shows how to get the same record number Access displays in its navigation buttons, by using the AbsolutePosition property of the form's recordset.


With Access 95, the record navigation control added the "of n" string to show a record's position in the recordset. You can include such information in your custom record number display by showing a record position label in the format "1 of n" instead of merely a record number. This listing includes this technique. Our clients agree that this format is less confusing than a record number by itself.

Listing 14.8 AES_Frm2.Mdb-Creating a Record Number Indicator from Code

Private Sub Form_Current()

' Purpose: Show current record position

If Me.NewRecord Then

Me!lblPos.Caption = "New"

Else

Me.RecordsetClone.Bookmark = Me.Bookmark

Me!lblPos.Caption = CStr(Me.RecordsetClone.AbsolutePosition + 1) _

& " of " & CStr(Me.RecordsetClone.RecordCount)

End If

End Sub


A form does not have a Bookmark property when it's on a new record. If your navigation routines empower the user to move to a blank record, include a provision in the code for detecting the NewRecord property, as Listing 14.8 shows.


In addition to the AbsolutePosition property shown in Listing 14.8, experiment with the PercentPosition property, which shows the record location within the recordset as a percentage. Your users may find a percentage useful, yet less confusing than a record number. Note, however, that the PercentPosition property is not perfect either: it assumes that the first record in the recordset is at zero percent and that the last record in the recordset is something less than 100 percent. Thus, you may prefer to calculate the percentage position yourself in code, using the position and record count numbers provided in Listing 14.8.

Replacing the Navigation Buttons

To remove the Access VCR control from your forms, set the NavigationButtons property to No. Then, via code add any removed functionality that your users still require.

One approach is to place buttons directly on the form's header, creating our own navigation toolbar as shown in Figure 14.10. These navigation buttons can be made much more flexible and powerful than the Access buttons in the following ways:

Coding homemade navigation buttons is not difficult. It requires a few global library routines and a smattering of Click event code attached to each button in the host form's module.


Even if you don't intend to adopt my techniques for record navigation, a close study of the code in this section should give you valuable insights about how to move within an Access form's recordset, how to trap various form conditions, and how to build upon a foundation of reusable library routines.

Listing 14.9 shows the Click event code behind our five standard custom navigation buttons: First Record, Previous Record, Next Record, Last Record, and New Record. The Current event for the form is also shown. The library routines called by code in the listing are explained after the listing.

Listing 14.9 AES_Frm2.Mdb-Click Routines for Form Navigation Buttons That Move though a Form's Recordset

Private Sub btnAdd_Click()

Call lci_FrmFocusSave(Screen.PreviousControl)

Call lci_FrmNavGotoNew(Me)

Call lci_FrmFocusRestore

End Sub

Private Sub btnFirst_Click()

Call lci_FrmFocusSave(Screen.PreviousControl)

Call lci_FrmNavGotoFirst(Me)

Call lci_FrmFocusRestore

End Sub

Private Sub btnLast_Click()

Call lci_FrmFocusSave(Screen.PreviousControl)

Call lci_FrmNavGotoLast(Me)

Call lci_FrmFocusRestore

End Sub

Private Sub btnNext_Click()

Call lci_FrmFocusSave(Screen.PreviousControl)

Call lci_FrmNavGotoNext(Me)

Call lci_FrmFocusRestore

End Sub

Private Sub btnPrev_Click()

Call lci_FrmFocusSave(Screen.PreviousControl)

Call lci_FrmNavGotoPrev(Me)

Call lci_FrmFocusRestore

Private Sub Form_Current()

' Purpose: Reset nav. buttons and show current record position

Call lci_FrmNavCurrent(Me)

End Sub

Note that the routines in Listing 14.9 are quite similar and have the following characteristics in common:


The coding model shown in Listing 14.9 places the focus management in the event code behind the button. The focus code can just as easily be located in the library routines for navigation. However, I have located the focus routines with the form in order to keep the library routines more generic. In other words, the library routines sans focus management can be called from routines other than button events, but can only be called from controls if the focus information was moved into them.

The library routines that support the navigation strategy in Listing 14.9 are shown in Listing 14.10.

Listing 14.10 AES_Frm2.Md-Library Routines That Support Form Navigation Buttons

Public Sub lci_FrmNavGotoFirst(rfrm As Form)

' Purpose: Goto first record in form recordset

If rfrm.mtdValidate Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToFirst)

End If

End Sub

Public Sub lci_FrmNavGotoLast(rfrm As Form)

' Purpose: Goto last record in form recordset

If rfrm.mtdValidate Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToLast)

End If

End Sub

Public Sub lci_FrmNavGotoNext(rfrm As Form)

' Purpose: Goto next record in form recordset

If rfrm.mtdValidate Then

If Not lci_FrmNavIsLast(rfrm) Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToNext)

End If

End If

End Sub

Public Sub lci_FrmNavGotoNew(rfrm As Form)

' Purpose: Goto new record in form recordset

If rfrm.mtdValidate Then

If rfrm.AllowAdditions = True Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToNew)

End If

End If

End Sub

Public Sub lci_FrmNavGotoPrev(rfrm As Form)

' Purpose: Goto previous record in form recordset

If rfrm.mtdValidate Then

If Not lci_FrmNavIsFirst(rfrm) = True Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToPrevious)

End If

End If

End Sub

Note that I used the new RunCommand method in an attempt to speed up the record navigation code. In prior versions of Access, I utilized recordset operations to navigate, such as the following alternative to the code in lci_FrmNavGotoFirst shown in Listing 14.10:

frm.RecordsetClone.MoveFirst ' Move to the first record

frm.Bookmark = frm.RecordsetClone.Bookmark ' Sync form

This type of navigation uses the form's RecordsetClone property, which is the only way to use DAO recordset operations like Move and Find operations on a form. The previous code shows how to synchronize the form's displayed record to the record that is current in the clone by synchronizing their respective bookmarks.

Navigation code based on recordsets still works fine in the current version of Access, so you can use either the recordset approach or the RunCommand approach in your navigation routines. My only rationale for using RunCommand is a secret hope that it executes a bit faster than the corresponding VBA code.

The next piece of our navigation puzzle involves creating the supporting library routines that are called by the navigation routines in Listing 14.10. These supporting routines test for the beginning and end of the form's recordset and are shown in Listing 14.11. The two location checking routines in the listing help the navigation routines determine what record a form is pointing to. Based on this information, the navigation buttons are enabled or disabled accordingly, as shown in Listing 14.12.

Listing 14.11 AES_Frm2.Mdb-Library Routines That Support Programmatic Record Navigation by Testing for the First or Last Record

Public Function lci_FrmNavIsFirst(rfrm As Form) As Boolean

' Purpose: Is form on first record in form recordset?

Dim rst As Recordset ' Form Recordset

Set rst = rfrm.RecordsetClone ' Get form recordset

If Not rfrm.NewRecord Then

rst.Bookmark = rfrm.Bookmark ' Sync Recordset to the form

If rst.AbsolutePosition = 0 Then

lci_FrmNavIsFirst = True

End If

Else ' On new record, just check for one more

If rfrm.RecordsetClone.RecordCount = 0 Then

lci_FrmNavIsFirst = True ' No records yet

End If

End If

End Function

Public Function lci_FrmNavIsLast(rfrm As Form) As Boolean

' Purpose: Is form on last record in form recordset?

Dim rst As Recordset ' Form Recordset

Set rst = rfrm.RecordsetClone ' Get form recordset

If Not rfrm.NewRecord Then

rst.MoveLast ' Get the record count

rst.Bookmark = rfrm.Bookmark ' Sync Recordset to the form

If rst.AbsolutePosition + 1 = rst.RecordCount Then

lci_FrmNavIsLast = True

End If

Else ' New record is automatic last

lci_FrmNavIsLast = True

End If

End Function

The next component of the navigation puzzle to explore is the most interesting. In the preceding two listings, I showed you how navigate within the form's recordset using command buttons. From the developer's perspective, this record navigation system is powerful, because each navigation event can be detected, enhanced, and controlled. From the user's viewpoint, however, simply moving between records is straightforward and almost boring. The best part of our custom navigation style from the user's perspective is that the forms self-configure the navigation buttons to indicate the user's position in the recordset.

Using the library routines we're exploring here, when the user is on the first record in the form, the First Record and Previous Record buttons are disabled. When the user is on the last record, these two buttons are enabled but Next Record and Last Record are disabled, and so on. The buttons provide visual clues about the form's recordset. In this respect, our custom navigation buttons are smarter than those in Access, which still do not self-configure properly even in the 97 release. Access' built-in First Record button does not disable when the user is on the first record, nor does the Last Record button disable on the last record. Thus, if you use these controls on your custom menus, you inherit this odd behavior.

In order to self-configure the navigation buttons, a form's Current event must check and reset the buttons with each record move. Referring back to Listing 14.9 you'll see the Current event procedure for a form with custom navigation; the event calls the lci_FrmNavCurrent library function as shown in Listing 14.12. This routine configures the navigation buttons.

Listing 14.12 AES_Frm2.Mdb-Library Routine That Supports Programmatic Record Navigation by Setting Navigation Buttons

Public Sub lci_FrmNavCurrent(rfrm As Form)

' Purpose: OnCurrent processing for forms with lci nav buttons

rfrm!btnFirst.Enabled = Not lci_FrmNavIsFirst(rfrm)

rfrm!btnPrev.Enabled = Not lci_FrmNavIsFirst(rfrm)

rfrm!btnNext.Enabled = Not lci_FrmNavIsLast(rfrm)

rfrm!btnLast.Enabled = Not lci_FrmNavIsLast(rfrm)

End Sub

The final component of manual navigation is a record validation methodology. Note that the navigation routines in Listing 14.10 are careful to call the form's validation method before moving. This approach ensures that a form record is painstakingly validated by code before any navigation occurs. The validation method mtdValidate for the form is simply a Public function in the form that tests the record and returns a True/False value to the navigation routines. See Chapter 15, "Protecting and Validating Data," for an expanded discussion of this validation method.

With custom record navigation, your code always knows where it is in the recordset, your users see what you want them to see on the navigation buttons, and data-oriented events are detected as soon as they are triggered.

Exploring Toolbar and Menu Strategies

You may have noticed that my expert form examples in this book do not use the built-in toolbars of Access, but rather create custom interface devices: toolbar-like buttons directly on the forms (see Figure 14.11), customized toolbars using the new command bars technology, or customized menubars.

Fig. 14.11

This application form has embedded toolbar buttons only and a minimal bar menu.

The use of embedded toolbars creates a form "component object" that is self-contained and provides your application with the highest degree of control over events triggered by user interaction. Other reasons for considering the use of embedded toolbars are explained in Chapter 15, in the "Deciding Between the Access Commands and Your Own" section.

You can also create a hybrid style, which uses a generic Access toolbar for operations that are truly generic (do not require custom code) and embeds custom tools on forms for the rest of the work, as in Figure 14.12.

Fig. 14.12

This application mixes custom embedded buttons on the form with the Access built-in toolbar buttons.

The following list offers some built-in toolbar buttons that you might consider as candidates for a generic entry/edit form toolbar.

Note that the listed tools do not change focus from the current record to another record; thus, they do not affect your navigation or validation strategies. Tools that cause a record save and all of its related events may be placed on an Access toolbar as well, but you will have to work harder to enable this approach. You will find that you have more control of the application environment if your code is aware of navigation and validation events.

For example, you must locate your record validation code in the BeforeUpdate event of any form that enables navigation from standard navigation buttons placed on an Access toolbar. You must also devise a strategy to cancel any record saves in progress that are initiated by a toolbar selection but that do not survive the validation process or meet your other criteria for allowing a record move. Although this may present no problem in some interface metaphors, forms with complex validation generally benefit from very tight application control over the saving and testing of records.

A detailed discussion of the benefits and liabilities of Access built-in features placed on form toolbars and menubars is in the "Selecting the Appropriate Menu Options" section of Chapter 15. However, in this chapter we are concerned with the mechanical aspects of implementing toolbar and menu strategies.

Although Access's menu macros from previous versions are still supported in Access 97, you will learn to appreciate the powerful but complex command bar technology for menu and toolbars. The next two sections provide an introduction to this technology.

Introducing Command Bars

As an active member of the review and testing process for each new release of Access, I see a lot of the feedback that the Access team gets from developers. Year after year, one of the top wishes from developers has been "Give us programmable menus, like in Visual Basic." With Access 97, these wishes because a reality.

Access actually didn't add programmable menus, it inherited them. The new CommandBar object and its object model is a feature of Office that Access leverages. The command bar model in Access is the same as the one in Excel, Outlook, PowerPoint, and Word. This situation is both a positive and a negative.

On the positive side, if you develop Office-centric solutions you need only learn one object model and create one code library to provide custom menus in multiple host applications. The negative aspect is that the Access team was not able to fine-tune command bars for Access; as a generic Office object they work as you would expect: generically. This means that command bars are not very aware of the special needs of Access.

As an example, the ability to call a custom form method from a custom toolbar function is not properly enabled in Access 97. You must instead create a "helper function" in a standard module, call that function from your command bar object, and have the helper in turn initiate the form's method code. (The function is called a "helper" because its only purpose is to call the form code that the command bar cannot execute directly.) Another example of the lack of full command bar integration with Access is that you cannot nest command bars on an Access form or within a form control.

Despite the limitations, command bars are a viable and useful technology for creating navigation elements in expert solutions. The next three sections will show you how.

Using Command Bars for Form Menus

The best use of command bars in your applications is for form menus. Most application have some kind of menu system. Based on the navigation models described in this chapter, you will decide to enable user navigation and other form operations via some combination of form Command Button controls, command bar toolbars external to the form (see the next section), and command bar menus.

To create a command bar menu, select View, Toolbars from the menu system or select Customize from the shortcut menu on the Access menu workspace. The Customize dialog box appears, as shown in Figure 14.13. From this dialog, you create, modify, and remove all of your application's command bars.

Fig. 14.13

Command bars are created and modified using the Customize dialog box.

Notice in the figure that I have already created three custom command bars that are supplied in the custom database for this chapter: lci_mbrFrmStd, lci_mbrFrmSimple, and lci_tbrFrmStd. From my naming conventions you can probably divine that the first menu is a standard (default) menu bar object for use with forms, the second is a sparse menu with few options, and the latter is the default form toolbar. lci_mbrFrmStd and lci_tbrFrmStd are displayed in the figure.

To create a custom menu bar like the one shown in the figure, follow these steps:

  1. Create a new command bar. Select New from the Customize dialog and name your new command bar.
  2. Set the properties for your new command bar. Highlight it in the list of toolbars in the Customize dialog's Toolbars tab and click Properties. The Toolbar Properties dialog box appears, as shown in Figure 14.14. By default, new command bars are created as toolbars, but you can select the style for your custom toolbar using the Type drop-down as shown in the figure. Also note in the figure that I have opted not to show the standard form toolbar on the shortcut menu for toolbar selection (Show on Toolbars Menu is off) and have decided that users cannot modify the toolbar (Allow Customizing is off).

When you are first building a new command bar, leave the Allow Customizing option in the Properties dialog on or you will not be able to add and modify menu options for the object. When you are finished designing the command bar, return to the Properties dialog and turn this option off.

Fig. 14.14

Customizing the attributes of a command bar with the Toolbar Properties dialog.

  1. Drag existing command items to the toolbar or create new commands. To add an Access menu option to your toolbar, drag it from the Commands tab of the Customize dialog and drop it onto your command bar. To create a custom command bar item that does not tie to any built-in Access functionality, drag the item named Custom from the tab to your command bar, as shown in Figure 14.15. In the figure, the Custom option is being dropped between the Preview and Print... menu options, as indicated by the locator bar and mouse cursor.
  2. The type of menu item created is a function of the destination that you drop it to. Dropping a built-in or custom menu option on the menu bar area creates another top-level option on the bar menu. Dropping a menu item onto a toolbar creates a new button. Dropping a menu option onto a drop-down for a menu bar item adds it to the drop-down. To place a new menu item onto a drop-down, you must first drag it to the bar menu item. As you hover over the item, it will open and reveal its drop-down. You can then drag the item downward onto the drop-down.

Fig. 14.15

Add a custom menu item to a command bar by dragging the Custom item from the Customize dialog to the destination location.

  1. Set the properties for the custom menu option. Right-click on the menu option you want to customize and select Properties from the shortcut. The Control Properties dialog is displayed, as shown in Figure 14.16. For custom command bar menu items, you will want to set the menu caption, tool tip text, help file, help context ID link, and display style.

Fig. 14.16

Property settings for an individual menu item on a command bar are made in the Control Properties dialog box.

  1. Assign an action to the control. When you create a new menu item by dragging an Access built-in menu item to a toolbar, the menu item inherits from Access the same event that would result from the item's selection from a built-in menu. In other words, if you add the built-in Undo menu option to your custom command bar by dragging it to the menu from the Commands list in the Customize dialog box, it will function the same way in every respect as the Undo option on Access' Edit menu. In contrast, if you create a custom menu item with the Custom option as described in step 3, you will have to designate the action to perform when the user selects the option.
  2. With the Control Properties dialog from step 4 still open, enter a custom action in the On Action property item. The entry here can be either a macro or a Public Function procedure in a standard module. The function procedure's name must be preceded by the = character and must be followed by parentheses even if no arguments are required. Here is the full text of the action entered into the On Action property setting in Figure 14.16:

=lci_CbrNavGotoFirst(Screen.ActiveForm, 1)

Once you've created a command bar menu and its custom menu options and an action, you can attach the menu to a form by entering the menu name in the form's MenuBar property. When the form opens, the menu is displayed, as shown in Figure 14.17.

Fig. 14.17

A custom menu built with command bars is attached to this form.

When the user selects a custom menu option from your command bar menu, the function specified in the On Action property setting is run. In the case of our current example, selecting the custom Records, First menu option would run the function lci_CbrNavGotoFirst that was attached to the menu item in step 5. Listing 14.13 shows the code for this function

Listing 14.13 AES_Frm2.Mdb-A Function That Provides a Custom Menu Action for a Command Bar

Public Function lci_CbrNavGotoFirst(rfrm As Form, rbytCbr As Byte)

' Purpose: Goto first record in form recordset

' Arguments: rfrm:=Calling form

' rbytCbr:=Which command bar is used:

' msoBarTypeNormal=0=toolbar, msoBarTypeMenuBar=1=bar menu,

' msoBarTypePopup=2=shortcut

If rfrm.mtdValidate Then

rfrm.SetFocus ' Just to be sure

Application.RunCommand (acCmdRecordsGoToFirst)

End If

End Function

This code routine provides the ability for the user to navigate to the first record on a form by selecting a custom menu option. Because we code our expert forms using a component object model, the menu is generic and has no awareness of the specific form that it is attached to at the current moment. Thus, the function call from the menu to the library navigation routine must pass in a handle to the active form without knowing its name, so it uses Screen.ActiveForm. The library function receives this pointer to the current form object and moves the record pointer for the form.

You may have noticed that this library routine is almost identical to the library routine for navigation via embedded form toolbar buttons, lci_FrmNavGotoFirst, shown in Listing 14.10 previously. The only difference is that the menu bar action passes in an argument value of 1 to the function in the rbytCbr argument position. The function does not currently use the argument but provides a placeholder for the it. This allows the function to be enhanced in the future to recognize the type of command bar that called it.

In Listing 14.12 previously, you explored the library routine lci_FrmNavCurrent that cleverly enables and disables a form's embedded navigation buttons. It is possible to follow a similar path and to make your custom command bar menu selections come and go based on context. This creates menus that are smarter than those provided by Access and are user friendly to the highest degree possible.

In the Current event of a form using the custom menu bar shown in this section, I call the function provided in Listing 14.14 and pass in a handle to the form and the type of command bar to configure:

Call lci_CbrNavCurrent(Me, msoBarTypeMenuBar)

The listing not only shows how to smartly configure command bar menu items from code, it provides an opportunity for you to begin to explore the object model of the CommandBar objects that define the menus.

Listing 14.14 AES_Frm2.Mdb-Current Event Processing Allowing a Form to Configure its Menu Bar Options

Public Sub lci_CbrNavCurrent(rfrm As Form, rbytCbr As Byte)

' Purpose: OnCurrent processing for forms with lci nav command bars

' Arguments: rfrm:=Calling form

' rbytCbr:=Which command bar is used:

' msoBarTypeNormal=0=toolbar, msoBarTypeMenuBar=1=bar menu,

' msoBarTypePopup=2=shortcut

' The constants for the command bar name are set at the module level:

' mcstrMbr = "lci_mbrFrmStd"

' mcstrTbr = "lci_tbrFrmStd"

Select Case rbytCbr

Case msoBarTypeNormal ' Toolbar

CommandBars(mcstrTbr).Controls("btnFirst").Enabled _

= Not lci_FrmNavIsFirst(rfrm)

CommandBars(mcstrTbr).Controls("btnPrev").Enabled _

= Not lci_FrmNavIsFirst(rfrm)

CommandBars(mcstrTbr).Controls("btnNext").Enabled _

= Not lci_FrmNavIsLast(rfrm)

CommandBars(mcstrTbr).Controls("btnLast").Enabled _

= Not lci_FrmNavIsLast(rfrm)

Case msoBarTypeMenuBar ' Bar menu

CommandBars(mcstrMbr).Controls("&Records").Controls("&First").Enabled _

= Not lci_FrmNavIsFirst(rfrm)

CommandBars(mcstrMbr).Controls("&Records").Controls("&Previous").Enabled _

= Not lci_FrmNavIsFirst(rfrm)

CommandBars(mcstrMbr).Controls("&Records").Controls("&Next").Enabled _

= Not lci_FrmNavIsLast(rfrm)

CommandBars(mcstrMbr).Controls("&Records").Controls("&Last").Enabled _

= Not lci_FrmNavIsLast(rfrm)

Case msoBarTypePopup ' Shortcut

' Not implemented

End Select

End Sub

The key difference between the way that Command Button controls were enabled and disabled by the routine in Listing 14.12 and the way that command bar controls are managed in Listing 14.14 is in the addressing of the target object. A command bar object, whether menu or toolbar, has a Controls collection. Each control in the collection can be addressed by name or index. Thus, for a toolbar object, your code can address the navigation button btnFirst on the toolbar with this:

CommandBars("lci_tbrFrmStd").Controls("btnFirst")

For a menu bar object, each drop-down menu is a control on the menu bar. Within each drop-down menu object, however, is another Controls collection with the buttons on the drop-down. Thus the syntax elements must be nested to show this relationship. This line of code addresses the Records, First menu option:

CommandBars("lci_mbrFrmStd").Controls("&Records").Controls("&First")


Notice the different naming convention issues between toolbar buttons and menu options. Command bar items do not have an actual Name property; instead, the Caption serves as the item's name. For menu items, the caption displayed to users also provides the name of the menu item in the object model. Thus the reference in the previous code line to the command bar control &First. For toolbar buttons that display text, you will also provide a caption that has meaning to the user and inherit this caption as the menu item's object name. For toolbar buttons that display only a graphic and not the caption, I use an object name for the caption that follows LNC style, such as btnFirst.

Using Command Bars for Form Toolbars and Shortcut Menus

When you determine that a command bar toolbar is an appropriate addition to a specific form, you must first define the composition of the toolbar. Usually, a custom toolbar contains a mix of built-in buttons borrowed from Access and your custom buttons. For example, there is usually no need to write your own undo code when you can now place the Access Undo toolbar button on your custom toolbars and inherit that code. On the other hand, record deletions are often tightly managed by program code and will usually be custom-coded, as described in Chapter 15, "Protecting and Validating Data." Your custom code for record deletions on your expert forms can be invoked as an action of a custom toolbar button.

Creating a new custom toolbar is similar to creating a command bar menu as described in the steps and figures in the preceding section. The primary difference is that the Type value you select in the Toolbar Properties dialog box will be Toolbar as opposed to Menu Bar.

Toolbars are very flexible. In addition to button controls, you can place edit (text) boxes, drop-down list boxes, and combo box controls on a toolbar. You can also place a drop-down menu on a toolbar.


There is a control type in the command bar model for each toolbar control used in the Office applications, but most cannot be created from Access code. For example, the Style drop-down in Word, which mixes text and graphics, is a command bar object, as is the grid control in Word that allows you to identify the size of a new table. Hopefully these control types will be exposed to Access developers at some time in the future.

Unlike with the Windows 95 Toolbar control in the Office Developer Edition, you cannot attach ActiveX controls to a command bar toolbar or menu.

Figure 14.18 provides an example of the new toolbar capabilities introduced in Access 97. The figure shows a custom form toolbar that includes a combo box control allowing the user to navigate to a record in the form's recordset.

Fig. 14.18

A custom toolbar with a combo box control.


Minus the combo box control, which is useful but not mandatory for form navigation, the toolbar in the figure provides a good example of an optimal set of controls to provide to an expert form. The mixture shown allows a user to navigate, add, delete, undo, and print, but does not provide options like bulk replace that are dangerous to the data nor provide controls that are only marginally useful.

For each custom toolbar control you create, you will assign the name of a Public function procedure to the On Action property; this attaches event code to the button click. For example, the action assigned to the combo box control shown in Figure 14.18 is:

=lci_CbrNavFrmSync(Screen.ActiveForm _

, Commandbars!lci_tbrFrmStd.Controls!cboCust.Text)

This action calls the public helper function lci_CbrNavFrmSync and passes in two arguments: a handle to the active form, and the current value of the combo box control. The helper function is called only to activate the form code that the command bar cannot call directly; it contains this line:

Call rfrm.mtdSync(rvar)

The helper initiates a customized synchronization method (mtdSync) on the form whose purpose is to move the form to the record that is specified in the combo box control, as shown in Listing 14.15.

Listing 14.15 AES_Frm2.Mdb-A Custom Form Method to Move to a Specific Record, Initiated by a Command Bar Combo Box

Public Function mtdSync(rvarFind As Variant) As Boolean

' Purpose: Synchronize the form to a specific record

' Arguments: Company name to find

Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "CompanyName = """ & rvarFind & """"

If Not rst.NoMatch Then

Me.Bookmark = rst.Bookmark

End If

rst.Close

End Function

The code in the custom method synchronizes the form's recordset to the value from the toolbar combo box, as passed in as an argument from the helper function. Notice the generic nature of this process: neither the toolbar button nor the helper function are aware of the name of the form, nor the contents of the form's custom method. This allows different forms to provide different features in their mtdSync method, and yet all of the forms can benefit from one standard toolbar coded to this method name.


The action for the custom combo box control on the toolbar passes back the value displayed to the user (cboCust.Text). If the position of the user's selection in the combo's list is more important than the text, you can pass around the Index property of the combo box instead of the Text property.

Context (shortcut) menus can also be created using command bar objects. First, create a new command bar as a menu or toolbar and set the properties of the command bar and its controls. Then, change the Type property of the command bar to Popup in the Toolbar Properties dialog. This converts the command bar to a shortcut menu.

To view a shortcut menu for customization, you must display the container for all shortcut menus by selecting Shortcut Menus from the Toolbars list in the Customize dialog box, as shown in Figure 14.19.

Fig. 14.19

To customize a shortcut menu you must display the Shortcut Menus bar.

To attach a shortcut menu to a form, simply set the form's Shortcut Menu Bar property to the name of menu. Figure 14.20 shows my standard form shortcut menu as designed in Figure 14.19 at work on a form.

Fig. 14.20

A custom shortcut menu used on a form.Programming Command Bars

Menu bars, toolbars, and shortcut menus are each simply command bars with specific property settings, as opposed to different object types. Thus, the command bar object model is actually simpler than it seems. Command bars in your applications will be primarily created in the user interface, but can also be created and modified from program code as required.

By creating a reference to their object model, command bars become programmable from Access. The object model is stored in the Office object library MSO97.DLL. You must create a reference to this file in the References dialog box in order to use program code to address the components of command bars.

There are three primary varieties of command bar objects. Each menu option and toolbar button is a CommandBarButton object in the object model. A CommandBarPopup object represents a context menu or a (cascading) submenu. A CommandBarComboBox control represents a text box, drop-down list box, or combo box control on a command bar. Each of these is programmable.

The three command bar object types are subordinate to a parent CommandBar object, which is a member of the CommandBars collection for the application. Within this collection, you can do most of the things you expect from a collection, such as inspect its Count property to see how many command bars are in the application or use the Add method to create a new command bar. The example code in Listing 14.16 creates a new menu bar and adds a drop-down with one item to the menu.

Listing 14.16 AES_Frm2.Mdb-Creating a New Menu Bar From Program Code

Dim cbr As CommandBar

Dim ctl As CommandBarControl

Dim pop As CommandBarPopup

' Create a new menu bar command bar object

Set cbr = CommandBars.Add(Name:="mbrTest" _

, Position:=msoBarTop, MenuBar:=True)

With cbr

.Protection = msoBarNoCustomize ' Don't allow user changes

.Visible = False ' Do not display

End With

' Create a drop-down menu

Set pop = cbr.Controls.Add(msoControlPopup)

pop.Caption = "&Batch"

' Add an option to the drop-down

Set ctl = pop.Controls.Add(msoControlButton)

ctl.Caption = "&Post"

The Add method in the listing is used to create a new command bar or a command bar control, depending on the context. Setting attributes of a command bar or control is similar to setting properties for a built-in Access object.

You cannot create a CommandBarComboBox control (text box, drop-down list box, or combo box) from the user interface, you must use code. Thus, the combo box control shown on the form toolbar in the previous section was created using the code in Listing 14.17.

Listing 14.17 AES_Frm2.Mdb-Adding a Combo Box Control to a Toolbar From Program Code

Public Sub lci_CbrCboCreate()

' Purpose: Example of programmatically creating a combo box on a toolbar

' This code will not run if you do not own Office and set a reference

' to its type library MSO97.DLL

Dim cbr As CommandBar

Dim cbo As CommandBarComboBox

Set cbr = CommandBars("lci_tbrFrmStd")

' Create a combo box

' Control is located one position in from the right

Set cbo = cbr.Controls.Add(Type:=msoControlDropdown _

, Before:=cbr.Controls.Count, Temporary:=False)

With cbo

.Caption = "cboCust"

.DropDownLines = 10

.DropDownWidth = 180

.ListHeaderCount = -1

.OnAction = "=lci_CbrNavFrmSync(Screen.ActiveForm," _

& "Commandbars!lci_tbrFrmStd.Controls!cboCust.Text)"

.Width = 150

End With

End Sub

The code in the listing simply appends another control (cboCust) to the existing named command bar. The Add method allows you to specify the location of the control on the toolbar; in this case, I chose to place the control just to the left of the rightmost control, the Help button, which is located in the Controls.Count (maximum) location on the toolbar.

Once the control is created, the code in the listing simply establishes its properties such as height, width, and action. If the values in the combo box's list were to be permanent, the listing could include AddItem method statements to insert items into the list. However, the purpose of the combo box, as demonstrated in the previous section, is to dynamically display records from a form's recordset in its list. Thus, the combo list values must be loaded from code as the form loads. This code is shown in Listing 14.18.

Listing 14.18 AES_Frm2.Mdb-Loading Values Into a Combo Box Control on a Toolbar

Private Sub Form_Load()

' Purpose: Place record ID values in toolbar combo box

Dim ctl As CommandBarControl

Dim rst As Recordset

Set ctl = CommandBars!lci_tbrFrmStd.Controls!cboCust

Set rst = Me.RecordsetClone

If rst.RecordCount > 100 Then

ctl.Visible = False ' Don't use if too many records

Else

With ctl

While Not rst.EOF

.AddItem rst!CompanyName

rst.MoveNext

Wend

.Visible = True

End With

End If

rst.Close

End Sub

The code in the listing simply moves through the form's recordset and adds the company name value for each record to the combo box list. For performance and usability reasons, the listing does not attempt to load the combo box if the form contains more than 100 records. The form code to move to an item as dictated by the combo box was shown previously in Listing 14.15.

The structure of the object model for command bars can be discovered by browsing the Object Browser and experimenting with the code I've provided on the CD. Command bars offer a wealth of possibilities for custom programming and dynamic configuration of the user's environment.

Passing Information to a Form

A form often needs information that originates outside of itself. One simple example of such information is the name or security level of the current user. Forms may find value in such information.

What follows are the various ways your program can supply information to a form:

DoCmd.OpenForm "frmCust"

Form_frmCust.Tag = "Sally"

Fig. 14.21

The DefaultValue property for this Text Box control refers to the form's Tag.

DoCmd.OpenForm "frmCust", , , , , , "Sally"


If your code opens a form as a dialog box by using OpenForm with its WindowMode argument set to acDialog, the calling code pauses at the OpenForm line and does not continue until the form is closed. The only viable strategy for passing information to a form called this way is via the OpenArgs argument. Because the calling code pauses, any code after the OpenForm line that sets a form's Tag, custom variable, or custom property will not run until after the form closes, producing an error.

=lci_gstrCurrentUser

=[Form].[mvarCalledFrom]

=[Form].[prpCalledFrom]

Forms!frmCust!zhtxtCalledFrom = "Sally"

To be fair, system forms also have a disadvantage: they do not appeal to programming purists who believe that everything important must happen in VBA code. If you are a purist, you will use global variables, a global array, or a global collection to store system-wide values that are transitory.I've reviewed six options for passing application information around between code and forms. You may want to evaluate each method and to select a single approach as a standard. Alternately, you may find that each application has unique needs and each expert form may even require its own special approach.

Navigation Issues for Reports

Why is a report issue cropping up in a forms chapter? Because, I prefer that my expert forms call reports in many cases when the user chooses to print, rather than simply printing the current form.

Why? What's so bad about printing the current form? It's true that Access forms can be made to print very attractively, complete with headers and footers whose DisplayWhen property ensures that they only show up on the hard copy, as Figure 14.22 shows. However, my style choice is based not so much on presentation quality, but rather on usability.

Fig. 14.22

This form has custom property settings that show the page header but not the form header when printing.

Consider the following issues that derive from print previewing a form:

The first two problems can be bypassed if you disable print preview and only provide users with direct printing from the form. But most users immediately notice and complain about the removal of print preview from an application. Chapter 18, "Creating Expert Reports," expands on this discussion of printing forms as reports.

Our expert forms approach is to create reports for those forms for which the user expects a printing option. The custom logic on a form's matching report can be made much smarter than the printing of a form by Access via a toolbar button. With custom printing logic, users can select only the current record, a range of records, or all records as determined by the reporting code that you write.

In addition, closing the print preview of a report does not close or otherwise affect the underlying form. You are also able to intercept the display problems noted previously with respect to a maximized window state by simply placing the following code in each report's Close event:

DoCmd.Restore

This code undoes a maximization state induced by the user during preview and returns your form to its original size.

From Here...

Creating the visual elements of a form does not take very long, but defining and building a robust user interface mechanism to make the form more usable and powerful can be very time consuming. This chapter defined some of the strategies for creating forms that guide users gently through your applications.

The following chapters in this book provide information about expert forms and related concepts:


1996, QUE Corporation, an imprint of Macmillan Publishing USA, a Simon and Schuster Company.