Chapter 8

Designing Effective Interfaces

I've struggled in vain over the years to find a good discussion of the issues involved in developing user interfaces for database applications, so I've written my own here. Sure, there are quite a few resources available from Microsoft and others that remind you that all Windows dialog boxes should have OK and Cancel buttons, or tell you to how to add a keyboard accelerator to a button. But nobody bothers to discuss the deeper philosophical issues specific to databases. Does Cancel undo the changes that are visible on screen, or the edits that have been most recently saved as well? Does OK save the current record and prepare for another one, or does it close the form?

I see users of custom Access solutions grapple with these kinds of usability issues day after day. Writing code is so consuming for most developers that they end up scrimping on the amount of attention given to the part of the application that the user actually sees. In this chapter, I'll lay the foundation for your interface design efforts and define the issues that are most important to creating a highly usable expert solutions.

Look and Feel

"In the Access group, we have two full-time graphic designers who are empowered to find ways to make the Access interface friendlier and easier to use. (They also come in real handy when we're creating Access T-shirts and other partyware!)"

Tod Nielsen, General Manager, Microsoft Access Business Unit

No doubt you've seen what I call a "rainbow form"? Someone in your company or at an Access user group meeting comes up and proudly wants to show you their application. When you see it, you wonder if you've drifted into a Picasso exhibit: blue prompts, red messages, and yellow rectangles, all on a black background.

Designers of rainbow forms and similarly ineffectual interface elements demonstrate that they have lost track of one of the key tenets of application design: The success of any application will be determined by how well it solves a business problem, not by how it looks, what technology it employs, or how sophisticated the core code is. While visually enhancing an application is not a bad thing per se, the use of illustrative fonts and colors rates very low on my list of "Important Concepts In Business Process Reengineering."

Creating a friendly and usable Access application involves mixing together multiple components, such as:

Not coincidentally, the flow of this list matches the outline of this book-creating expert solutions is about creating and mixing each of these elements well. However, of the elements on the list, forms have the most interaction with users and consume the majority of your development effort.

Thus, a big part of this book is devoted to forms. But before you can master creating powerful and complex forms, you should understand and inherit the body of interface design work that has already been done on behalf of your users. If they have ever seen Windows 95 or Office, they have already been exposed to paradigms originated from years of usability research at Microsoft. Rather than invent your own interface paradigm, why not simply borrow Microsoft's?

To some extent, you don't have a choice but to inherit it, because the toolset in Access defines the limits of your user interface, and the toolset dovetails into the Windows paradigm. (Just try building your own unique menu bar control in C++, getting it to nest and dock in Access and paint correctly, and making it communicate its events to VBA, and you'll quickly determine that the Access extensibility model does not include letting developers redefine the user interface paradigm.)

On the other hand, you are no longer stuck with only the interface elements that are built-in to Access. Anything you can code into an ActiveX control and teach to behave well on an Access form or report becomes a component of your own personal user interface construction kit. Thus, if your users really don't like the way a standard Windows List Box control works, you can make them a new one.

However, it is not economically prudent to build all of your own form classes and controls from scratch. Nor is it particularly exciting to experiment with font, color, and effect properties simply to spice up an application. Thus, the majority of your applications will make use of certain Access and Windows defaults. In this chapter, I clarify and classify those defaults for you, and help you determine how to mix and match them to achieve the highest degree of usability and user satisfaction.

Unfortunately, the Windows styles only take your application part of the way down the road. Conveying database-type concepts to users of an Access form is more complex than, for example, configuring a printer in a Windows dialog box. As a result, I'll also discuss in this chapter how to go beyond the Windows interface metaphor and establish your own enhanced interface metaphor for expert solutions.

Exploring Essential Interface Concepts

Because of the serious needs of database applications as detailed throughout this book (entering valid data, protecting data from erroneous interaction, display complex data simply, and so on), you should place quite a bit of emphasis on user interface elements during your design and development work. My own interaction with database application users tells me that perhaps 75 percent of all of their problems and frustrations stems from three things:

You're probably wondering what comprises the remaining 25 percent of user frustration with database applications. Mostly these issues: application or Access bugs, setup/configuration issues, and inadequate performance.

The first item can't be corrected with good interface design; application enhancements are required when the application has feature limitations . When you design an application according to the LDF precepts in Chapters 2 through 4, you end up with a planned feature set that matches the users needs.

The second and third areas of frustration can be corrected by designing better interfaces. When you have properly defined the feature set for an application, the next step is to expose these features to users through a well-engineered interface. A good application interface (Access or otherwise) survives these tests:

It takes extra effort to create forms that are uncluttered, functional, and consistent, but the effort is worthwhile if user satisfaction is higher as a result. Visit Chapters 13 though 17 for specific lessons on creating expert forms that meet the criteria listed above.

While part of creating good interfaces is adhering to feature sets that are realistically programmable and commonly recognizable, the other part of the job is to match the users' workflow. These two objectives don't always meet. I have yet to see an interface metaphor for managing contact information, for example, where the Windows interface style, the limitations of relational database structures, and the complexity desired by users didn't collide. It is very difficult to automate certain types of information in one or two, Windows-standard screens. Nevertheless, you are obligated to try.

As you mesh standard interface components with the needs of your users, first consider how the users work, then ponder the interface requirements afterward. Discern from the users the following:

Each of these scenarios should be accommodated by your interface. And not every one of them can be accommodated from the Windows style guide. For example, assume that processing a batch of transactions in your new system will take two hours and must be supervised by a human. If the process starts at 4pm and the user must leave at 5, does your application facilitate pausing the process in mid-stream and restarting where it left off? (Forget for a moment the repercussions for the data in this scenario, I'm asking an interface question.)

And if you are able to accommodate a pause in the process, how does the user restart it the next morning? The Windows/Office interface style has no standard menu option for Restart, no Resume Where I Left Off toolbar button, and no terminology for processes like these. You are on your own in areas like this, and such scenarios are common when you create database applications. That's why asking the right questions and planning an effective interface model is so important in Access solutions. You must create standard approaches to non-standard problems, and deploy them your application or in all of the applications for a specific workgroup.

Here are some practical tips to keep handy as you sit in front on your blank paper preparing to lay out the forms for your next application:

Designing Friendly "Office-like" Forms

For several years Microsoft has had an initiative to convince authors of Windows-based software to follow the user interface metaphor defined in Office. The program is called Office Compatible, and is largely responsible for the fact that all of the Office applications have similar interface metaphors, as do many third-party Windows products. For example, why did View, Options... in Access 2 become Tools, Options... in Access 95? To become Office Compatible.

The Office Compatible specification defines several elements that make up a minimally standardized document-centric Windows application. Those that are relevant to your Access applications follow:

You have probably noticed that the menu terminology standard for Office-type menus employs a mix of nouns (File and Tools) and verbs (Edit and View) along the menu bar. This mixture provides you with no clear signal as to how your menus should be structured. In other words, should you have an Insert menu bar option with Transaction on the drop-down, or a Transaction menu bar item with Insert on the drop-down? The section "Conveying Database Issues to Users" later in this chapter addresses this issue in more detail.

Fig. 8.1

Standard Access bar menu items for forms.

Fig. 8.2

Customizing a built-in menu option like File Save is acceptable if the meaning or intent of the action does not change substantially.

You can read the entire Office Compatible specification online on the Internet at At that location, you can also request a hard copy of the specification from Microsoft.

While Office Compatible provides a good framework for the basic elements of interface design, it is mute on database-specific problems and issues that are common to you and I. In areas where it is mute, you must still establish your own standards. See the discussion of database interface issues in "Conveying Database Issues to Users" later in this chapter.

In addition to these interface elements discussed by the Office Compatible guidelines, there are several commonly-accepted Windows metaphors for designing interface elements. The next six sections provides more information on common Windows interface guidelines that will help shape the look of your application interfaces.

Employing Standard Windows Terminology

I can't begin a discussion of Windows interface issues without defining the basic terms that are relevant to such discussions and listing the commonly accepted interface practices you should use. There are quite a few terms and concepts that Microsoft has standardized over the years for use by Windows application developers. However, I'll only mention those that are important specifically to Access solution creators in an effort to keep things uncomplicated.

The following terms should be standardized in your application, both in the message that you show on the screen, in the training materials, in the Help file, and in the printed documentation. This glossary matches the terminology used in Microsoft's product documentation and includes usage examples:

If you use these same terms as are found in the Office product documentation, your application and its materials will already speak a language that its users understand, and you will have no retraining or clarification expense when teaching them to use your solution.

Executing Actions From the Keyboard

As you design applications, do not presume that your users enjoy reaching for the mouse every few seconds. Take advantage of the built-in Access keyboard programming features and leverage them to make the use of your applications via the keyboard more friendly.

By default, all Windows application menu commands are accessible from the keyboard, often in more than one fashion. For example, in Access you can select File Close with the keystrokes Alt+F and C (called access keys), or with Ctrl+W (called a keyboard shortcut). If you use command bars to create your own custom application menus, you should provide access keys for any menu options you devise.

Access keys are for selecting menus by name, menu drop-down commands, dialog box options, and command buttons, and utilize the Alt key plus a letter. As an example, assume that you are adding a custom Post menu bar option with a Batch option on its drop-down. Here are the general rules to follow when defining the access key combination for a menu bar item (Post):

  1. Utilize the first letter if the keystroke will be unique. Because there is no Alt+P combination on the Access bar menu system, you could safely assign this access key to Post.
  2. Use the most distinctive available consonant if the first letter is already in use. For example, if your custom bar menu option was Reconcile instead of Post, Alt+R would be unavailable because it belongs to the built-in Records menu bar item, so you would use the Alt+C as the access key instead.
  3. Use the first available letter. If the first two options fail you.

When creating menu access keys for drop-down menu options, follow these rules:

  1. Use the first letter of most important word. In the case of Batch, the choice of B is obvious. If the menu item was Audited Invoices, I would opt for I instead of A, delegating Invoices the more important term in the option.
  2. If the access key from the most important word is taken, use the first letter of the first word.
  3. If both of these options fail you, use the first available letter within the most important word.

Access keys used for command buttons, option buttons, and other form controls are also termed accelerators, such as Alt+N to click the New button in the Database window. The rules for assigning the hot key in an accelerator are the same as listed for access keys. Access keys and accelerators usually operate on visible options, while function keys can have broader scope. Thus, when creating a shortcut to jump directly to an application form, process, or dialog box (this is often called a "back door"), assign the task to a function key (or other "keyboard shortcut", described next) as opposed to an accelerator.

If an accelerator on a form control utilizes the same keystroke sequence assigned to a displayed bar menu item, the keystroke will go to the form and not the menu.

Keyboard shortcut keys, in contrast to accelerators, execute an option whether or not the menu or button for the action is displayed. Keyboard shortcuts involve the Ctrl key in conjunction with the Shift key, a function key, and/or another keyboard character. The section "Defining Keyboard Shortcuts" in Chapter 20, "Applying Development Shortcuts," details all of the keyboard shortcuts built in to Access and describes how to create your own with an AutoKeys macro.

So when do you utilize keyboard shortcuts and accelerators in an application? The answer is that you can create as many keyboard mappings as are valuable to your users. The two most common scenarios are:

Users with keystrokes in their motor memory can be dangerous when learning your new system if some of your accelerators and shortcuts work differently than the keystrokes in the users' memories. During application planning, always query users to determine what keystrokes they use most often in the system you are replacing and in other systems that they will continue to use. If the keystrokes can be remapped in Access to perform the same function, users will be appreciative.

If, on the other hand, the keystrokes they used in a prior system perform a different action in Access, determine what the risk to the application is if users mistakenly press the old key sequence in the new application. You may need to remap certain keystroke sequences to be inert in Access to prevent accidents in the new system. Alternately, you can place MsgBox function code in the keystroke sequence to ask the user if they want to proceed with the operation; this provides a layer of protection from the negative aspects of motor memory. The section "Trapping Dangerous Keystrokes" in Chapter 17, "Bulletproofing Your Application Interface," shows how to disable built-in keyboard shortcuts that may be dangerous to your specific users.

To create an accelerator key or access key, place the ampersand (&) character before the designated character in the caption of the menu item or control. For example, to use Alt+P for your custom Post menu bar option described earlier in this topic, enter &Post as the menu item's name when you build the command bar.

When you enter an accelerator in the Caption property of a Label control bound to another form control, pressing the accelerator jumps to that control. For example, an accelerator in the label of a text box will move focus to the text box.

When the focus moves to a boolean-state control (check box, option button, or toggle button) via an accelerator, the control's value is automatically toggled (checked or cleared, whichever is the opposite of the current state). When focus moves to a Command Button control via an accelerator, the button is automatically clicked.

Figure 8.3 demonstrates the three user interaction scenarios described in this section:

To enable the use of the Alt+> and Alt+< navigation keystrokes for users, I actually used the captions &, and &. on the command buttons. Because the user would actually have to press the Shift key in order to get to the < and > symbols on the keyboard, I've used the unshifted versions of these keys as the actual accelerators to save the users wasted keystrokes.

Call Forms(Screen.ActiveForm.Name).mtdPrint

Keyboard shortcuts that are applied uniformly across an application can be built in the AutoKeys macro group. To create a keyboard shortcut that is unique to a form, you can monitor the keyboard for the designated keystroke sequence using the form's KeyDown event. A procedure that monitor's the keyboard for specific keystrokes is called a "keyboard handler"; this type of procedure is described in Chapter 17, "Bulletproofing Your Application Interface."

Fig. 8.3

This form employs all three types of Windows-standard keyboard extensions: access keys, accelerators, and shortcut keys.

This first release of Access 97 has some limitations with respect to keyboard shortcuts. The shortcomings are a reflection of the difficulty involved in bringing the Office command bar model into Access. I expect that these deficiencies will be fixed in a future major or minor release of Access(perhaps even by the time you read this). Here are the problems:

Understanding the Windows Menu Metaphors

Menu systems in Windows applications employ these components:

With Access 97, menu objects can now be "docked" at any of the four edges of the Access window. Figure 8.4 shows a menu bar and toolbar that have been located at the edges of the Access window. The menu also contains a custom Post option in the proper location next to Help.

Fig. 8.4

Access command bar objects can be docked at any edge of the Access window.

You can create each of these types of menus using the new command bar technology in Access 97. While command bars come with some modest limitations and quirks, they are an effective tool for you to enhance your application interfaces. See Chapter 14, "Navigating in Forms and Applications," for examples of command bars at work and tutorials on how to create and program them.

Maximizing the usability of your application's menu system interface involves finding an appropriate balance between these different menu components. The balance point between menu options, toolbars buttons, and buttons on forms is a hard one to find. It is generally accepted as an application design goal for Windows applications that every option on a form button, toolbar button, and shortcut menu is available on the bar menu as well. This rule was designed to achieve two goals:

In your Access applications, providing a bar menu option for each button and shortcut menu item is not a goal you will pursue with the same vigor as do developers of mass-appeal retail software applications, for three reasons:

Given this information, how do you decide where to locate application features within the menu system? Use these guidelines:

Chapter 14, "Navigating in Forms and Applications," and Chapter 17, "Bulletproofing Your Application Interface," provide many screen figures that exemplify different menu, toolbar, and form button layouts.

Using Windows Common Dialogs

Within Access, dialogs like Open, Save and Print are inherited by calling an external service, they are no longer built-in to the Access code. Thus, to perform a File Open menu action, Access asks Windows: "Please display your standard Open dialog box and send me back the user's selection." Your application can make use of these standard Windows interface elements as well.

If your application will require users to jump to an external document, locate the back-end database, or insert a document into an OLE Object table field, an expert solution should go beyond simply providing the user with a File Name text box. The standard metaphor to apply here is to create a Browse... button on the form and call the Windows Open dialog box. By passing the appropriate arguments to the dialog box, you can direct the user to a particular folder or filter the list of files shown.

With Access 97, using the standard Windows dialogs for Open, Save, Print, Font, and Color is enabled by placing the ActiveX control COMDLG32.OCX on the form and using VBA code to initiate the desired dialog and dispose of the result. The common dialog control file is supplied with the Office Developer Edition. Once you have installed it on your system, you add it to a form by selecting Insert, ActiveX Control... and clicking the Microsoft Common Dialog Control.

When your application needs to provide any of thestandard services in the control to users, there is no reason to write complex code or re-invent the existing Windows metaphor. The code in Listing 8.1 shows how easily the Open dialog box in the Common Dialog Control is displayed and the user's selection returned to the form.

Listing 8.1 AES_UI.Mdb-Calling the Common Open Dialog

With Me!cdlg

.DialogTitle = "NorthWind Database" ' Window caption

.FileName = " NorthWind.Mdb" ' Initial/returned filename

.Filter = "Access Databases (*.mdb)|*.mdb" ' Type filter combo

.FilterIndex = 1 ' Seed for Type filter combo

' Set return value parameters

.Flags = cdlOFNFileMustExist Or cdlOFNPathMustExist

.InitDir = SysCmd(acSysCmdAccessDir) & "Samples"


If Len(.FileName) > 0 Then

Me!txtFileName = .FileName

Me!txtFileTitle = .FileTitle

End If

End With

Figure 8.5 shows the dialog box produced by this code. Note how the initial values for the dialog title, file name, and other options were passed from the code to the dialog box.

Fig. 8.5

This Windows common dialog was displayed by form code.

An ActiveX control that is properly installed on your system and entered into the Windows Registry can be inserted into a form and referenced by code. However, to address the control's type library (object model definition) from program code, you may need to set a reference to the control file in the References dialog box.

For example, the code in Listing 8.1 references constants declared in the control's type library such as cdlOFNFileMustExist. These constants are not available simply by adding the control to a form, you must also add a reference.

See Chapter 11, "Expert Approaches to VBA," for further discussion of type libraries and references.

Whenever possible, use established interface metaphors like the Windows common dialogs in your applications. Reusing application components familiar to Office users provides a common interface for your users across all of their tasks.

Selecting Fonts and Colors To Use

Deciding how to use fonts and colors in Access is really quite simple-you simply accept the defaults. If you do that, you can create forms that are simple and match the Windows standard. The Access control defaults set most controls to black text on a white background, with the exception of Command Button controls and form sections that inherit their colors from the Windows desktop scheme (how they do it is described in the next section).

A Colorful Story

Remember how I introduced you to the concept of rainbow forms in the introduction to this chapter? In preparing for this topic, I decided to unarchive one of the many client applications we've debugged or enhanced over the years that provided our office with a sense of artistic ambiance as a result of its forms.

As an example of what not to do in your applications, consider what I found in the relatively simple application written by a neophyte corporate developer and sent to us for rework. The BackColor property settings he had used on the forms included:


Dark Red




Dark Green


Olive Green


Light Green


Light Yellow


Dark Blue


Dark Magenta


Dark Cyan


Light Blue


Light Magenta


Light Cyan



That's right, 13 different text box and section background colors were in use in the application, and ten of them appeared at one time on a single form! Sounds dizzying? It was.

The ForeColor property settings in the application showed only a slightly less enthusiastic variety:




Dark Red


Light Red


Dark Blue


Light Blue


Bright White

If you think that laptop users have a hard time seeing light blue text on a light cyan background in a sunny office, you're completely right.

Mercifully, the application made use of (only) five different fonts, less than many applications we've seen but still too many for my tastes (the use of Arial Rounded and System were unnecessary):


Arial Rounded

MS Sans Serif


Times New Roman

The lesson in this story is that the colors and fonts did nothing to add value to the application. They added value only to the job of the programmer, who spent an extra few days colorizing the application and got paid for his time. In fact, the excessive gaudiness of the application actually made it more difficult to use, and highly distracting to walk past when on-screen.

For each new control you create, Access' default font setting is MS Sans Serif 8. Oddly, the default font of Windows is Arial 8, which Access correctly uses for report controls. So, if you want your forms to be more Windows-compliant (and to print nicer as well), change your control defaults for all form controls except for command buttons to Arial 8 (command button and title bar captions are the two places where Windows uses MS Sans Serif font).

In pre-95 versions of Windows and Access, bold font was used more readily for label captions and so forth, but this emphasis has been removed. You will not want to use bold in your application interface unless you prescribe an application-specific use for the attribute and explain this need to your users.

The use of bold text on a phrase or setting words to all capital letters is referred to in user-interface parlance as shouting. Do not make your applications shout at users without a good reason.

Does this mean that any colors and fonts other than the Windows defaults are anathema? Not necessarily. The key is to emphasize visual elements in moderation, and to apply valid logic when varying from the standards. For example, there may be group captions on your forms that are more usable when bold emphasis is applied to them, or a warning message displayed on a form may be more obvious in blue or red. But the properties you use for basic data entry/edit form control should not be changed from the Windows defaults.

In Chapter 18, "Creating Expert Reports," I note that it is acceptable to vary from the standard form background color of gray when forms are intended for printing as reports. In this scenario, using a white background facilitates printing.

Making Use of the System Color Scheme

You probably know that you can go into the Windows Control panel and change to coloration of your desktop, but you may not know that your Access application can be designed to inherit the color scheme you create there.

For example, open Access and create a new form. Leave the form open on the screen. Now open the Control Panel on the Start menu, open the Display Properties dialog, change the Scheme setting on the Appearance tab, and apply the change. Does your Access form's background reflect the color change? Yes, it does.

When users modify the look of their desktop, they will expect all well-behaved applications to inherit the change. For your system to keep pace, you must assign the appropriate values to the color properties of Access form elements. Fortunately, Access does some of the work for you.

For Command Button controls, Access sets the ForeColor by default equal to the button text color setting for the current desktop (the property value is -2147483630). For form detail sections, Access sets the property to -2147483633, which tells it to inherit the 3D Objects color from the desktop color scheme. For all other controls, Access hardwires the color property settings to Black (0)on Bright White (16777215).

To enable other form controls to change color dynamically to synchronize with the Windows desktop, you set the BackColor, BorderColor, and ForeColor properties equal to specific numeric values, as shown in Table 8.1.

Table 8.1 Color Property Settings That Prescribe System Defaults


Property Value

3-D Dark Shadow


3-D Face


3-D Highlight


3-D Light


3-D Shadow


Active Window Border


Active Window Title Bar


Application Background


Button Text




Dimmed (Disabled) Text




Highlight Text


Inactive Window Border


Inactive Window Title Bar


Inactive Window Title Bar Text


Menu Bar


Menu Text


Scroll Bar


Title Bar Text


Tooltip Background


Tooltip Text




Window Frame


Window Text


You can misuse these property values quite easily, because Access allows you to enter any setting listed in the table for any color property on any control. For example, you can assign the Highlight Text color value -2147483634 to the ForeColor property of a command button instead of the appropriate Button Text color value. You'll have to determine which combinations make sense for your application.

You will usually accept Access' suggestion that command buttons and form sections inherit their scheme from Windows. This allows your Access forms to follow suit when a display style change is made.

You may also want to teach other controls to inherit the Windows defaults. To do this, you override the Access default property settings for form controls and establish new defaults that mimic the Windows desktop. For example, assume that you do not want your Text Box controls to display black text in a white window regardless of the display environment; instead, you want the control's colors to change when the Windows desktop changes in the same manner that text boxes built-in to Access and Windows change. Follow these steps:

  1. Create a new Text Box control on a form.
  2. Set the Fore Color property value of the control to -2147483640, which tells Access to apply the desktop scheme color for Window Text to the control.
  3. Set the Back Color property value of the control to -2147483643, which tells Access to apply the desktop scheme color for Window to the control.
  4. Select Format, Set Control Defaults from the menu. This teaches Access to apply these two property settings to all text boxes created from this time forward.
  5. Follow this same strategy for any other form controls that you want to build custom default setting for.

Make sure that you consider the ramifications of any unique color settings that users may apply. For example, accepting Access' default text box setting of black text on a white background may be expedient in organizations where users are discouraged from changing their desktop display. In cases where a user can change the Windows color scheme for Window Text to white, your Access forms will display white text on a white background and become unusable. In this kind of environment, forcing the text box attributes to inherit the Windows attributes would prove more usable.

Guiding Users Through the Application

Forms are usually the most critically important objects in your application, for the reasons that follow:

Because forms are so important, you must make them easy to use and easy to move within and between. After you sketch out an application's forms and their properties, ask these questions:

As you create and enforce an application flow, you will combine various interface elements: buttons, menus, toolbars, shortcuts/accelerators, windows (forms), messages, and dialog boxes. Knowing which element is most appropriate for an application task is important to crafting an expert interface.

Choosing a Navigation Paradigm

My 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. This model is called Single Document Interface (SDI) in contrast to the Multiple Document Interface (MDI) model that Microsoft Office applications, including Access, employ by default. The MDI model is acceptable for slides and documents, but data-bound forms really cry out to be structured into a cohesive application.

In fact, one of the stated design goals for Windows 95 was to define more of an SDI model for users, based on feedback from Windows 3.x usability testing that users become overwhelmed when too many options are presented. Notice, for example, how the Windows Explorer doesn't let you tile multiple drive listings as File Manager did, or how linear the Start menu is when compared to Program Manager.

Consider the following problems that can arise when you allow your users to open multiple Access forms at random:

Applying interface techniques that circumvent these and other situations is the responsibility of both the design and development teams. All of Chapter 14, "Navigating in Forms and Applications," is devoted to issues that arise as you create navigation methodologies for your application. Refer to that chapter for a complete exploration of navigation strategies. For our purposes here, I'll summarize the key points as follows:

You achieve these objectives by creating menu bar, toolbar, shortcut, or embedded navigation options for the user to click, and then teaching the application to provide the user with interaction within the specified set of objects valid for a given context, and nothing more.

The best approach is to create a standard navigation interface paradigm for your workgroup or enterprise, then create as many reusable objects as you can and employ them in multiple applications. The navigation library code and library toolbars shipped with many of the sample databases in this book is one example of this strategy.

If your navigation model is highly restrictive, you will end up building applications that utilize the runtime version of Access or at least adequately protect the data from misuse and the user from confusion. A restrictive interface usually removes the Database window and is modeled around one or more switchboard menu forms such as the one shown in Figure 8.6. Applications that provide a switchboard menu must provide users with a path to every important system feature via the menu system.

Fig. 8.6

A highly restrictive application provides users with a list of available features so they don't have to wander in the Database window.

Users of an application that restricts and controls navigation will primarily be given a single task to perform at one time. This follows the SDI paradigm and reduces user confusion and application misuse. The application interface shown in Figure 8.7 provides an example of a restrictive environment oriented toward completing a single task before moving to another object. The user can only move to another process by completing the current process (closing the form, in this case).

Fig. 8.7

A Single Document Interface application model displays only one form at a time.

Of course, you can create hybrid navigation systems that deviate from the simple model in ways such as these:

Fig. 8.8

An Access toolbar the mimics the Windows 95 task bar interface style.

Other opportunities for creating a navigation model are limited only by your imagination, your skills, and the needs of your users and their data.

Accommodating Keyboard-Centric Users

Accommodating keyboard-centric users should be of concern to you as you develop your applications. If you've been developing applications only in the Windows era, you may live with one hand glued to the mouse. However, not all of your users will mimic that style.

Before there was Windows, there was DOS. Before there was DOS, there were terminals (okay, I am simplifying a little bit). And what did people do with terminals all day ? Enter and review database records. No word processing. No worksheets with graphics. No e-mail. Just databases.

So when these users try to use your application, they will try to figure out how the keyboard works with it; if the keyboard doesn't work well, you will have missed an important usability opportunity.

Accommodating keyboard users involves providing this minimum set of functionality:

Consider the form shown in Figure 8.9, which provides a prime example of a candidate for the status of "keyboard-optimizable." The form is a dialog box whose purpose is simply to help a user find a product when only a few characters are known.

Fig. 8.9

This form, properly coded, is very friendly to keyboard users.

Here is how a user interacts with the form:

  1. The form opens and focus is on the To Find text box. The Search button is the default button for the form (the button that is clicked when Enter is pressed).
  2. The user types a string of characters to search for and presses Enter. The default button (Search) is clicked automatically and the code in Listing 8.2 runs. The code creates an SQL string that populates the list box with matching product names. If any matching products are found, the code does this: the list box is enabled; the focus is moved to the list; the first item in the list is selected; and the default button is changed from the Search button to the OK button, as shown in Figure 8.10.

Fig. 8.10

All products containing the letter "a" are listed in the list box simply by pressing Enter.

Listing 8.2 AES_UI.Mdb-Executing a Simple Search From a Search Button

Private Sub cmdSearch_Click()

' Create the search string

Me!lstProduct.RowSource _

= "SELECT ProdName, ProdID FROM tblProd" _

& " WHERE ProdName Like '*" & Me!txtFind & "*'" _

& " ORDER BY ProdName"

Me!lstProduct.Requery ' Run the search

If Me!lstProduct.ListCount = 0 Then ' No matches found


Else ' Matches were found

Me!lstProduct.Enabled = True


Me!lstProduct.Selected(0) = True

Me!cmdOK.Default = True

End If

End Sub

  1. The user scrolls through the list to the desired record and presses Enter. The default button (OK) is clicked automatically, the form closes, and the product value is passed to the calling routine.

Notice that the user has quickly located a product record without ever removing his or her fingers from the home position on the keyboard-no special shift keys (Alt, Ctrl, or Shift) or mouse usage was required. Because the code routine sets the focus to the list box and the first item in it, the user can navigate in the list using the first character of the list items, thus the cursor movement keys are not even required. To use this dialog box to select the product Aniseed Syrup, here are the keystrokes required once the dialog has loaded: A Enter A Enter. That's all.

An application that is engineered correctly for the keyboard will allow a keyboard-centric user to perform a standard task faster than a mouse-centric user could perform the same task.

Using the Appropriate Window

By now you've determined that you will probably be creating applications that are very document-centric (to use the Office term), and thus concern themselves with a single document (database record or entry form) at a time. If you consider the parent record in a relationship the primary document, then child and grandchild records of that parent are essentially subdocuments. Within this model, you should strive to present a user with plenty of information about the document and its family, while isolating them from other non-related documents at the same time.

Based on our discussions thus far about MDI versus SDI, it should not come as a surprise to you that the AppWizard in Visual C++, when creating a new database application, employs the Single Document Interface by default. Its developers recognized that, even in retail-quality applications, if the document is data centric then it should be modal.

Access provides several different varieties of forms (windows) to use for the data collection tasks:

Fig. 8.11

The subform on the tab is uncomplicated and makes a good candidate for using the continuous display mode.

Modality is different from, but in some ways related to, border style. In Access, a form's border can be sizable or fixed. In general, you do not want to write the code to rearrange a form's layout when its border size changes, so you will not use the Sizable argument for Border Style unless the form is a datasheet.

Even if a form's border is not sizable, it does not automatically make the form modal. To enforce form modality, you set the PopUp and Modal properties to Yes.

With the new tab control, you can create complex tabbed dialogs that resemble the Options dialog in Access and other Property dialogs in Windows.

When you use dialog forms, there are two common interface elements that you may want to employ:

Providing Clues and Cues to Users

When first establishing our Access interface standards, we wanted to develop a technique for alerting users to the controls that must be filled in on a specific form in order to survive validation. We created forms demonstrating several different types of visual clues and surveyed a subset of our clients to gather their usability feedback on these forms.

Here is a quick summary of the interface devices we tested to denote required fields and the general response from us and our users:

Figure 8.12 shows the various techniques we tested for denoting required elements on forms. See Chapter 15, "Protecting and Validating Data," for a discussion on implementing our chosen strategy for flagging required fields. That chapter also discusses other techniques to help users discern the minimum set of information required by an application process.

Fig. 8.12

Various approaches for helping users determine if an entry is required by a form control.

Enumerating our research process for you should help you see how pioneering interface decisions are made. When you create your design and development standards, and there are no existing metaphors to follow, you must create your own model. As you create any interface standard that is untried or uniquely different from accepted norms, you must prototype the standard on a body of users and weigh their feedback.

Beyond the problem of telling users what values are required, you have several similar usability challenges as you craft your interface. You must teach the users how the application works and also give them some clues regarding what the program expects of them.

You can place helpful information in your interface in a variety of locations. The assistance text can tell users what to do next, what kind of information to enter into a control, what happens when a button is clicked, what to expect during a process, or what the status is of the current process. Here are a few ways to convey such information to the user via the forms interface:

Control tips have, unfortunately, become less useful in Access 97 than they were in Access 95. While both versions accept 255 characters of text in the property setting, only Access 95 actually displays all of the text. In the 95 release, control tip text is wrapped within a rectangular box and the entire string is shown in an attractive manner. In Access 97, tip text does not wrap. It displays on a single line and truncates when it reaches the end of the screen. Thus the amount of text you can display is limited by screen resolution (slightly over 100 characters in 640x480 mode). This horizontal display is also much less visually attractive than in the previous boxed layout.

These controls do not have a ControlTipText property: Line, PageBreak, Rectangle, Subform, and Tab (although tab pages do have the property).

Figure 8.13 shows an example of several of these visual clues provided on a single form. Expanded examples of these and other communication techniques are detailed in Chapter 17, "Bulletproofing Your Application Interface."

Fig. 8.13

This form explicitly indicates field formats, required fields, control groupings, and other data entry requirements.

You will also want to make use of custom help files when your application development process allows. Map the HelpContextID property of each form to a topic in the custom help file that explicitly describes the form's usage and requirements (see the sidebar).

A Hidden Smile

There is an actual Help topic in the Access 95 help file whose title is "Make my computer smile." To find the topic, search for "smile" on the Find tab in Help. Here is the text:

  1. Type a colon, and then type a right parenthesis.
  2. Repeat until the desired level of machine happiness is achieved.
  3. Unfortunately, the topic is gone from Access 97's Help and I haven't found its replacement.

Conveying Database-Specific Issues to Users

Database application forms often provide very interesting interface challenges. The reason is that it is sometimes very hard to explain to users how an action on a form translates into an action against the data. In this section, I'll provide a few examples of this challenge to get you thinking about the subject. However, each application, user group, and enterprise has unique attributes, so it's not possible for me to cover here even a fraction of the kinds of hurdles that will arise as you develop your applications. All I can do here is to spark your interest in the subject of database-centric interfaces in the hopes that you will properly budget the time and mental effort required to properly address these situations as they arise.

As our first example, consider this challenge: When your application's user is entering multiple records as a batch, is it possible for the user to cancel the entire batch? If so, how do you convey this to the user within the Windows interface metaphor? If you simply follow the model for dialog boxes and place OK and Cancel buttons on the form, how does the user know that Cancel undoes multiple records as opposed to only the current record? If you follow the Access model instead of the Windows model, your form will have a control box and close button, both of which save the batch as the form closes. The user will have no clear idea how to perform a "cancel" operation.

The problem arises because there is no distinction in the Windows model between a minor cancellation and a major cancellation-Windows thinks only in terms of OK and not OK, and Access inherits this model. In the current example, your application will need to go beyond the Windows standard and provide more information, perhaps through two buttons: Cancel Record and Cancel Batch.

Similarly, consider the problem of the way Access handles record saves. When the user moves from a parent record into its related child records on an embedded subform, the parent record is saved automatically (called an autocommit). From the user's perspective, the transaction is only half complete-the invoice header has been entered but not the detail records, for example. Yet from Access' perspective, many things have happened without any direct command from the user:

None of these actions is per-se bad, the point here is simply that the user was not asking for these events to happen yet. From the perspective of the user's workflow, saving the parent seems most logical when the complete transaction (including the children) has been entered.

When the parent record is saved before the entire group of related records is entered, the concept of "undo" becomes very confusing for users. Assume that the user simply wants to cancel the invoice he or she is entering. Access and Windows provide no default metaphor for this, because neither facilitates batches of information or form-based transactions. In the current example, it would be nice if the user could simply tell Access to cancel the transaction. Unfortunately for both the user and the developer, when a record autocommits early in the workflow, the only way to undo the record later is to delete it. This means extra coding work for the developer and confusing alerts to wade through for the user.

It takes time and effort to craft an elegant expert solution that hides these issues from non-sophisticated users, but the effort is usually a prudent investment in the success of a project. Here are some common approaches you can take to the problems posed by form data entry transactions:

Consider next another significant issue in database interfaces: bulk processing. Users in a manual system can perform an operation or two on a batch of data, then decide to revert back to a previous data set by simply throwing away some new paperwork and pulling the old papers back from file drawers. For all of its power, Access doesn't think in these terms unless you tell it to.

Assume the following scenario:

  1. The user imports into the application a file of information dumped out of the mainframe.
  2. After loading, the application code audits the imported records and produces a problem report.
  3. The user edits the problem records using a form, and prints a hardcopy report showing the imported information.
  4. The user runs a routine to process existing records in the system by applying cost items to them from the imported data.
  5. The application code produces summary records that recap the processing in the previous step and posts the summary information to a transaction table.
  6. The user runs a process to update master total records with the summary information placed in the transaction table.

Up through step 3, this process can be canceled quite easily and the application can purge itself of any trace of the interrupted process. What happens, however, if the user discovers serious problems after step 5 and wants to cancel. While you can wrap a specific code process in a transaction in your application, Access does not maintain a transaction log after your code has completed. Access has no knowledge of the relationship between the workflow steps in the list, and so it has no idea how to help the user cancel several, or even one, of the steps.

An expert approach to multi-step operations like this one requires that the developer creates the transaction log that allows the process to be undone and writes the code to support the change of heart. The entire process can be run on a copy of the affected records and the changes inserted into the live data after step 6 has run to completion. Alternately, the code can secure an exclusive lock on the database, backup the key tables, and execute the steps.

In either case, the key point here is not so much the problem itself as the limitations in Access that it exposes. Access does very well when thinking in terms of records, but you have to teach it everything you want it to know about processes. Then, you have to expose the processes to users.

A final confusing database-specific issue to consider when creating your interface involves terminology. Should you have an Insert menu bar option with Transaction on the drop-down, or a Transaction menu bar item with Insert on the drop-down? Does the user understand what a "rollback" is or should you be using the terms "revert" or "undo"? Semantic questions arise constantly when creating database applications, and the majority of them have no established standard nor one single correct approach. The key to your success will be to work with the users of the application and developers of similar applications to make sure that the application fits the needs and semantics of the workflow as much as possible.

From Here...

There are many interesting challenges that arise in the course of developing user interface layers over data. One of my favorite aspects of custom application development is the constant variety of challenges presented by the need to automate a workflow we've never attacked before.

In this chapter, you've acquired important knowledge useful when designing complex application forms and other interface elements. Here are other tools this book provides for becoming a designer of expert interfaces:

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