Chapter 16

Presenting Data to Users


Forms are the windows through which users view their data. Forms are not useful if they present too little information, do not allow users to locate desired information, or allow users to become confused about the use of the application. In this chapter, I show these techniques for providing users with maximum value from your forms:

A New Form of Angst


Invariably, I have found that users think of a new application's forms as outstanding for roughly a week. After that time, they begin to discover the "design shortcomings." For example, users on design teams often feel that they do not want phone number text controls to take advantage of the Format property, finding it too restrictive. Once the application is deployed, however, they notice that users enter phone number data in various formats, and then they wish they had implemented tighter restrictions.

Worse, users begin to understand the application better over time and then start comparing it to other applications (Access-centric or otherwise). As soon as they see a screen with a tab control in another application, for example, they wonder why they didn't get tabs in their application, disregarding the fact that it may not have even been an appropriate design option.

I call this ending of the new application honeymoon "designer's remorse."

The difference between a "database" that contains forms and an "application" that also does is that an application has a mission. Forms in a database are often disjointed, created with the Access wizard, and not bulletproofed. Forms in an application, on the other hand, are meant to be friendly to use, to provide navigation to and from other forms, to present information in the way most useful to their users, and to protect the data.

This book concerns itself with the latter type of forms. Because forms are the gateway to data, they must find a balance between usability and data security. More importantly, the ultimate role of application forms is not simply to enter data; forms must make entered data accessible so that people can learn, draw conclusions, or make decisions from it.

Many developers fail to research, plan, or develop with this latter consideration in mind. The result of understanding the ultimate role of forms is that applications must place the same emphasis on presenting data as they do on protecting it during entry. Thus, research and design efforts must include asking users questions such as:

Having asked these questions during your design work, you will have a better idea of how many different personalities of forms will be required to express the data in each table. A given table (or query result set of related records) may be represented on multiple forms in a single application, with each form providing different functionality.

In this chapter, I discuss various techniques for helping users to explore and understand their data using forms. The techniques exemplify the two facets of working with existing data through forms: presentation and selection. Forms must fulfill both of these roles well in order to qualify as expert forms.

Presenting Records to Users

Access 97 introduced no significant new tools for presenting and displaying records within Access itself, with the exception of a tab control. The new Web publishing capabilities, while interesting, are not related to data presentation via forms. Forms, subforms, and list boxes are still the primary presentation instruments in applications. As these elements are combined to help users work with data, keep these considerations in mind:

The following several sections discuss these issues in greater detail and provide suggestions and techniques for optimizing the use of forms.

Exploring Form Layout Options

Within the reasonable limits imposed by the Access forms engine, you can create some imaginative and powerful forms. The ability to nest forms within each other, to create multi-page forms, to display, hide, and resize controls at will, and to make use of event procedures provides you with a high degree of flexibility.

When you design forms in conjunction with your users, make certain that they are advised of their different options with respect to form layouts. Your job as a developer is to balance the users'objectives, usability and access to data, with the capabilities of Access and the available budget.

The following four topics discuss some of the technical and structural options you can employ in your Access forms.

Selecting the Appropriate View of Data

I prefer to create one form for each specific view of the data, and optimize it for that view. In other words, select a ViewsAllowed property of either Form or Datasheet and create a form that works best only in that view. Changing views, especially from form to datasheet, may allow the user to circumvent some usability feature or validation that works better in one view than the other.

For example, datasheet view does not show command buttons, which may provide functionality critical to the user's mission. It is not possible to create a single form that has the same capabilities in datasheet and form views, thus the need to determine which view your users will be seeing.

You can trick Access to some degree by embedding a subform in datasheet view inside a parent host form in normal view. The host form provides the command buttons and display container; and the embedded subform provides the clean, gridded layout of a datasheet, as shown in Figure 16.1.

Fig. 16.1

A datasheet form can be made more usable by embedding it inside a host form.

While datasheets are useful when comparing records or reviewing batches of data, developers place too much emphasis on the display of multiple records together on a single form. You should consider when you really need to present multiple records to users and when one record will suffice. Too much information can add unwanted complexity for users as well as developers.

Ponder the following display options as you design your forms, making sure you match the display of records with the tasks to be performed on them and the aptitude of the users:

You can see from these examples that the common thread when choosing a form layout is balancing these needs: protecting the data, solving the users'needs, and maximizing performance.

When you are building a form to display a large number of fields, you must consider using multiple pages on the form. You can provide the user with buttons to use to move between the pages, with each button issuing the appropriate GoToPage method.


The GoToPage method has a Right argument that prescribes a horizontal offset for the page within the form window. You can use this to create a form with more pages than are allowed in the 22-inch maximum form height by creating two columns of pages within a single form.

Implementing Subforms

What subforms giveth they also taketh away, by which I mean that subforms provide the ability to cram related data onto a parent form, but in the process of "cramming" they also may become difficult to use. With limited space (most subforms consume the lower half of a form, sometimes even less), it can be challenging to display enough data in a subform to make it useful.

Additionally, subforms create development challenges on two fronts:

In a standard form/subform approach, developers often crowd multiple detail records into a subform on the parent form. In this layout, there is often not adequate space to display each value. In a crowded subform, for example, users may be required to press Shift+F2 regularly to zoom on a Comment field's data.

In general, users will not need to see multiple detail records in order to receive value from the subform. Thus, a crowded subform can be made uncrowded by exposing only a single record on it at one time.


It is interesting to watch the facial expressions of users (and sometimes even developers) when they are shown subforms with a layout other than continuous records. Subform examples in print and in demo applications are usually laid out with continuous records, thus many Access owners do not even realize that they have other layout options available to them.

If single-record subforms are usually more friendly and descriptive, why not use such a layout in all cases? Primarily because users will voice two common complaints when you use this layout:

Both of these situations can be resolved, fortunately, through creative form design. For example, the form navigation techniques suggested in Chapter 14, "Navigating in Forms and Applications," can also be used to build friendlier subforms as easily as with parent forms.

Review the form shown in Figure 16.2. The subform contains navigation buttons and positional information such as that found on its parent form. The navigation buttons provide a strong visual clue to the user that there are multiple records inside the subform area. Users are unlikely to be confused by this layout. In addition, the developer achieves two important objectives with it:

Fig. 16.2

This form and its subform use similar navigation strategies.

For data entry/edit forms, it can be quite useful to display subform records in the uncluttered fashion shown in the previous figure. Users will be able to take advantage of standard form features such as combo boxes for selection, option group and check box controls, and text boxes that match the size of their average content.

However, if other types of forms are constructed against the same data, their use may dictate the display of the data in a different fashion. You have several creative options for altering the data display; a few examples follow.

Your first alternative is to create "exploding subforms." Because a subform control on an Access form can be resized programmatically, you can provide the user with the ability to "zoom" the subform on demand.

Assume that your subform control consumes the lower half of its parent form. You can place a Zoom button on the toolbar in the subform's header and toggle the zoom state of the subform between its normal size and a larger size, perhaps even filling the entire parent form. As you resize the subform, you can use the extra space to display additional controls that were hidden or increase the size of existing controls, such as showing ten lines of the Comment field where only one is shown in the default size.


Due to its unique window class, there are often display problems when you place other controls over the top of an Access List Box control-the list portion of the control may bleed through. You should not resize a subform to cover a list box located on the parent form.


If your application and user base comfortably support multi-page forms, you can place a subform on the second page of the parent form in order to provide it with the full height of the parent, but with a lower coding and usability burden than is attached to the zooming technique.

Alternately, you can create a tabbed form layout with the parent form's controls on the first page of a tab control and the subform on the second page.

Another way to alter the display of subform data is to display only fields relevant to the form's specific purpose. A subform for data entry must usually provide access to all editable fields in the underlying recordset. In comparison, subforms on specialty edit or browse forms can be tailored to the task at hand.

For example, assume a customer form whose specific purpose is to flag orders for the customer as they are shipped. The parent form would display the customer information, and the subform would show the orders. There may be no need to display on the subform all of the order information in order to accomplish the task at hand. Instead, you could create a subform that displayed only the fields OrderID, OrderDate, and ShippedDate, which provide enough information for the user to indicate each invoice's payment status.

A subform with many records and few fields (thus a narrow display footprint) is a candidate for a taller display than is normally used with subforms. Figure 16.3 shows an order subform consuming the entire height of its parent form rather than located at the bottom. The subform shows only order date information and would be used by data entry personnel to enter or verify the Date Shipped values.

Fig. 16.3

This subform displays only a selected subset of fields and uses a non-standard layout.

In some cases, a subform itself must be multi-page or must contain other subforms. Technologically, Access supports these needs. However, such complexity may be hard to use and will bump into serious space limitations on lower resolution monitors. At that point, you will need to consider making the subform into a form of its own, and creating methods for moving between the new form and its parent form. Techniques for synchronizing a form with its dependent form are discussed in the section "Synchronizing One Form with Another" later in this chapter.

A final option for creative subform displays is to use the datasheet view of information in a subform. Review Figure 16.1 in the prior section "Exploring Form Layout Options" for an example of this technique.

The primary challenge with a datasheet as a subform is that the program code must be keenly aware of whether the parent form or the subform has the focus. For example, in Chapter 14, "Navigating in Forms and Applications," I showed routines to support custom navigation buttons embedded on a form. The Last Record button on the example form in that chapter calls a library routine for navigation by passing it a handle to the form itself:

Call lci_FrmNavGotoLast(Me)

Consider instead the form shown in Figure 16.1, where the Last Record button is on the parent form but the actual records are on the subform datasheet. To navigate through the subform, the library routine must now receive a pointer to the subform's Form property, not the parent's; thus the button on the parent must issue this code instead:

Call lci_FrmNavGotoLast(Me!subCust_DS.Form)

As another example of the challenges of multi-record layouts, subform datasheets expose their data to bulk deletions. A subform datasheet with the record selectors showing allows for the selection of more than one record before an operation like Cut and Delete. Your code must determine how to best protect the data in such cases. For example, the SelHeight property of a datasheet indicates how many records are currently selected, so your subform's Delete event could contain this code:

If Me.SelHeight > 1 Then

' Show an alert and disallow the deletion

Replacing Forms With Lists

Whenever the updatability of a subform is not required, consider replacing a subform with a List Box control. Often, the nature of subform data prescribes that it need not be editable, so why incur the overhead of subform loading for no good reason?

Assume that you want to display on a parent form its related historical records, audit trail information, transactions, or similar non-editable dependent records. Displaying this information in a list box control instead of a subform would provide you with tangible performance benefits on two fronts:

In a similar vein, you can use list box controls on a form with no subform, replacing the form's continuous display mode with a continuous list. Obviously, if editability is an issue, a list box is not a sufficient replacement for a continuous form. However, you can create a hybrid form that includes both a list box and editable fields, as shown in Figure 16.4.

Fig. 16.4

A list box control provides a handy device to navigate through form records.

To keep the form data synchronized with the list box, use the Find methods against the form's recordset when the list box value changes. See the example in the section "Synchronizing a Form to a Value" following.


This model works best when the fields in the list box are not among those that are edited by the user. Because the list box is unbound from data, if the user changes a value in the editable portion of the form that is also reflected on the list, your code will need to requery the list to display the change.

Using a Tab Control

Before the advent of an intrinsic tab control in Access, we were forced to concoct workarounds for what is an excellent data management metaphor. Imagine a customer record that has multiple invoices, addresses, and contact people as child records. There is no better display model for information like this than a tabbed metaphor with one tab control and several attached subforms, one on each tab page for each of the three child tables.

Prior to a built-in tab control, developers used tab-style ActiveX controls in Access instead. However, you could not bind Access controls to the embedded tabs, because Access did not-and does not-support binding its controls to ActiveX controls. Thus, all movement between tabs and the display of information related to each tab was managed by program code. You can now discard your ActiveX tab controls and their workaround code in favor of the Access tab.

Let's explore a technique for managing parent/child information using the new tab control. Figure 16.5 displays the control on a form. This control has a "body" that hosts a Pages collection of Page objects. Each page can contain different controls that will be displayed only when that page is active (its tab is selected).

Fig. 16.5

This form uses the new tab control, shown here with two tabs.

Working with the tab control is very simple. The current tab number provides the Value property of the control (zero-based), as well as the index into the Pages collection. Thus, for the form in figure 16.5, the Debug window would return these values:

? Forms!frmCust_Tab!tabDetail.Value

0

? Forms!frmCust_Tab!tabDetail.Pages(0).Name

pgeOrders

A control on a tab page is still a member of the parent form (in its Controls collection), but is also a member of the Controls collection for its host page on the tab. Thus, a control on a tab page can be addressed in several ways, as shown in the examples in Listing 11.1. The statements in the listing were run in the Debug window while the form in Figure 16.5 was open.

Listing 16.1 Examples of Syntax for Addressing a Tab Control and its Child
Controls

' A control on a tab is also in the forms Controls list

? Forms!frmCust_Tab.Controls(26).Name

lstOrder

' A control on a tab is also a child of the form

? Forms!frmCust_Tab!lstOrder.Value

10528

' Each tab page has a Controls collection

? Forms!frmCust_Tab!pgeOrders.Controls(0).Name

lstOrder

' Controls on a tab are children of the tab page

? Forms!frmCust_Tab!pgeOrders.Controls!lstOrder.Value

10528

' A tab has a Pages collection with child controls

? Forms!frmCust_Tab!tabDetail.Pages(0).Controls!lstOrder.Value

10528

Figure 16.6 shows the second tab from the form in Figure 16.5. Note how a tab page makes a perfect host for a subform control.

Fig. 16.6

A tab control provides a mechanism for showing different information on each tab.

Synchronizing Record Displays

One of Access's strongest features is the seamless manner in which forms and subforms are linked. Not only are subform records automatically kept synchronized with the parent records, but information that cascades from the parent form to the subform or the reverse is refreshed automatically for the user.

As you build forms that are more complex than standard form/subform pairs, you will find new challenges related to keeping records synchronized. Forms can have relationships with each other; therefore, data on a particular form can have a relationship with data displayed simultaneously elsewhere.

The next several topics discuss some the challenges and techniques related to synchronizing information between forms.

Synchronizing a Form to a Value

By using the FilterName or WhereCondition arguments of the OpenForm method, you can open a form to a single record or to the first record in a designated record source. However, there may be instances where you want to display a record to the user other than the first one. You can use program code to synchronize a form to a specific record.

The simplest technique for making a form's recordset display a specific record is using the FindRecord method. This method invokes the search engine from the Find dialog to locate a control value in the form's recordset.

To move a form to the first record whose CustID value is 100, for example, your form code would issue the following commands (presuming that the table field CustID is bound to text box txtCustID):

Me!txtCustID.SetFocus

DoCmd.FindRecord FindWhat:=100

The shortcomings of this technique are twofold:


If the value you are synchronizing the form to is not unique in the form's recordset, the first FindRecord operation may not locate the expected target. In such a scenario, you must provide users with the ability to initiate an additional FindRecord that starts searching at the current location, so that they can move among the several candidates and decide which match to stop at.

The FindFirst argument of the FindRecord method determines whether the search starts at the beginning of the form's recordset (FindFirst is True) or at the current record (FindFirst is False). Thus the following line would begin searching at (actually after) the current record.


DoCmd.FindRecord FindWhat:=100, FindFirst:=False


A second alternative for locating a specific record in a form's recordset using VBA code is to use the FindFirst method. Because FindFirst is a recordset operation, you must apply it to the form's recordset directly (via its clone). The code to synchronize a form's recordset with a List Box control, as described in the earlier section "Replacing Forms With Lists," would look like this:

Dim rst As Recordset

Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID = '" & Me!lstCust.Value & "'"

Me.Bookmark = rst.Bookmark


To access a form's recordset, use the RecordSetClone property as shown. Move and Find operations applied to the recordset returned by this property do not cause the form to change its current record; thus, you can perform any valid DAO recordset operation against any record in the clone and only show results to the user when you deem it appropriate. The previous code shows how to synchronize the form's displayed record to the record that is current in the clone by synchronizing their bookmarks.


The advantages of the FindFirst approach shown over FindRecord are twofold:


If your needs warrant, your code can call the other Find methods (FindLast, FindNext, and FindPrevious) against the form's recordset in the same fashion as previously shown.


Code to find a specific record can be used to improve a form's recovery from a forced requery. While the previous FindFirst example showed the use of a form recordset's Bookmark property to synchronize to a specified record, be aware that all bookmarks are invalidated when a recordset is requeried. Thus, you can save and reuse bookmarks when working with a form's recordset, but you cannot use a saved bookmark following a requery because all bookmarks are destroyed and rebuilt differently when the recordset is rebuilt. You can use the FindFirst method instead of a bookmark to move to a specified record in this scenario.

For example, when you run code to delete a record programmatically from a form's recordset, you must requery the form to display the change. The requery will cause the form to display its first record again rather than synchronizing back to the pre-requery location. A friendlier approach is to recreate the functionality provided by Access itself when the deletion occurs through the user interface-the record following the deleted record becomes the current record. To do this in code, your routine can preserve the ID (not the bookmark) of the record following the deletion target, then delete the record, then requery the form, and finally resynchronize to the preserved location, as in Listing 16.2.


The code in the listing presumes that the CustomerID values are unique in the underlying table. When you use a strategy like the one shown to locate a specific record in code, make certain that you use a unique (usually primary key) value with the FindFirst.


Listing 16.2 AES_Frm4.Mdb-Resynchronizing a Form After a Deletion

Private Sub btnDelete_Click()

' Purpose: Delete a record and resynch the form to previous location

Dim rst As Recordset

Dim varSave As Variant

Beep

If MsgBox("Are you sure you want to delete " _

& Me!txtCustomerID & "?", vbOKCancel, "Delete") = vbCancel Then

GoTo btnDelete_Click_Exit

End If

Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID = '" & Me!txtCustomerID & "'"

If Not rst.NoMatch Then

rst.MoveNext

If Not rst.EOF Then

varSave = rst!CustomerID ' Save the return location

End If

rst.MovePrevious

rst.Delete

End If

Me.Requery ' Jumps to beginning of recordset

' Resynchronize the form

If Not IsNull(varSave) Then

Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID = '" & varSave & "'"

End If

Me.Bookmark = rst.Bookmark

btnDelete_Click_Exit:

On Error Resume Next

rst.Close

Exit Sub

End Sub

An additional, and friendlier, approach to assisting users with form record navigation is to use a combo box on the form, populated with the same records as in the form's recordset. The combo box contains an AfterUpdate event procedure to synchronize the form to the user's selection, as in the following listing:

Set rst = Me.RecordsetClone

rst.FindFirst "CustomerID = '" & Me!cboCust.Value & "'"

Me.Bookmark = rst.Bookmark

If a form's recordset is large, the selection combo box will be lengthy; and you may decide to filter it to reflect a subset of the form's records rather than the entire recordset. A second combo box, a check box, or an option group are good candidate controls for enabling this feature.


When you filter a selection combo, there is usually no need to filter the form's recordset itself to match that in the combo. The form can continue to include all records even when its selection control displays a subset. Requerying the form when its selector is rebuilt is an unnecessary performance hit given your objective, which it to give the user a list of all or some of the form's ID values from which to choose.

As an example, a combo box containing the states (the filter combo) can be placed next to the record selector combo that lists customers. When the user selects a state, only the records for that state will be displayed in the selection combo box, because the state list will rebuild the selection combo by issuing a Requery method on it.

In turn, the dependent selector combo box must utilize the information from the filter combo in its SQL statement:

SELECT CustomerID, CompanyName

FROM tblCust

WHERE State LIKE Forms!frmCust!cboState


If one of the options in the filter combo box is an asterisk, the selection combo box's SQL statement must use the LIKE keyword as shown so that it will display all possible records when the user selects the "all" (*) filter. (You can learn how to add an "all" marker to a combo box in Chapter 13, "Mastering Combo and List Boxes.")


Because of the need to frequently requery a record selection combo, especially in an entry form where the recordset is changing regularly, you can implement a simplified method for locating form records through buttons. You can place multiple buttons on the form or its header, with the code for each button jumping to a specific location in the recordset.

As an example, you could place 26 buttons on a form whose record source is sorted alphabetically, with the alphabet characters A through Z as the button captions. Clicking a button would use the FindFirst method to synchronize the form to the first record beginning with the selected character:

Me!txtCustID.SetFocus

DoCmd.FindRecord FindWhat:= "w", Match:=acStart


This method is useful for jumping to a relative location in a form's recordset, but not for finding a specific record, unless you can place a button on the form for each record in the recordset.


Techniques for jumping to a specific location in a form's recordset often assume that the recordset is sorted in a manner that supplements the jump. Using the current example, jumping to the first state value that begins with the letter "w" does not provide the user with much worth unless the form's recordset is sorted by state, so that the user can see the other "w" records by moving forward through the recordset after the find operation.


The previous techniques enable you to make locating designated form records easier for your users. Note that the record location and synchronization techniques described in this section differ from the ability to filter a form, as provided programmatically through the Filter property or by using the Records, Filter menu options in the user interface. Form filtration restricts the actual contents of the form's recordset, while navigating to a specific record preserves the recordset unaltered. Each feature is useful, but in different contexts.

Synchronizing One Form with Another

When two forms are related to each other, the need will arise to synchronize the information they display. In the simplest scenario, the user is on frmCust (the "calling form") and selects a related record to view on frmOrder (the "dependent form"). When frmCust opens frmOrder, code can pass a WhereCondition argument in the OpenForm method to display the desired record.

In this example, the recordset on frmOrder is restricted to a single record related to the calling form, so there is no need to resynchronize the forms in the reverse direction, from frmOrder to frmCust, when the dependent form closes.

A more complex situation arises when the calling form displays a dependent form with more than one record, and when both forms must be synchronized to each other. The synchronization must occur at one of two locations in the flow:

Let's inspect a code sample that synchronizes the display between a calling form and a dependent form. We can utilize the same technique described in the previous section by exposing the calling form's synchronization routine as a Public procedure and calling it from the dependent form.

In this example, the calling form frmCust_Tab has a list box control from which to select a target order. The user can click on an order in the list and then click Edit to launch frmOrder and see more detail for that order. Figure 16.7 represents this scenario.

Fig. 16.7

The list box on the customer form and the current record on the order form here are kept synchronized with each other.

The objective is for the Edit button's event code to open frmOrder with a record source that includes all of the orders for the displayed customer, and then synchronize to the item selected in the list box.

The first step is to have the calling form open the dependent form and display on it only the related records:

Private Sub cmdEdit_Click()

' Show all orders for this customer

DoCmd.OpenForm "frmOrder", , _

, "CustomerID = '" & Me!txtCustomerID & "'", , , Me!lstOrder

Note that the called form frmOrder is modal (its saved Modal property is True), thus the code in the calling form will halt when the called form is opened and wait for the form to close. Thus, any synchronization code cannot come after the OpenForm method in the calling form. Instead, the value used for synchronization must be passed to the modal form via its OpenArgs argument (the Me!lstOrder item in the code block shown above).

When the popup form opens, it synchronizes itself to the caller by applying the passed OpenArgs value to its recordset, as shown in Listing 16.3:

Listing 16.3 AES_Frm4.Mdb-Synchronizing a Form to a Value Passed to It

Private Sub Form_Load()

Call cbfSynch(Me.OpenArgs)

End Sub

Private Sub cbfSynch(rvarID As Variant)

' Purpose: Synchronize the form to a passed value

Dim rst As Recordset

If Not IsNull(rvarID) Then

Set rst = Me.RecordsetClone

rst.FindFirst "OrderID = " & rvarID

Me.Bookmark = rst.Bookmark

End If

rst.Close

End Sub

As the user browses through orders, the dependent form's code moves the selection in the calling form's list box to match the selection on the dependent form. Thus the order form's Current event has code like this:

Forms!frmCust_Tab!lstOrder = Me!txtOrderID

Notice how this example is in sharp contrast to the standard approach of simply opening the order form with a restriction to display only the single targeted order. In that model, the user must open and close the popup each time a specific record's detail is desired. The example shown here is much more friendly and flexible.

In addition to the challenge of synchronizing the displayed records, dependent forms bring with them the additional burden of synchronizing data changes. For example, frmOrder and frmCust_Tab in Figure 16.7 both display the values for Order Date and Shipped Date. Changing one of these values on the order form will require a requery of the list box on the calling form:

Private Sub Form_AfterUpdate()

Forms!frmCust_Tab!lstOrder.Requery

End SubYou can probably envision even more complex derivatives of this example scenario, with dependent forms calling their own dependent forms, nesting the dependencies several levels deep. Regardless of the complexity of your scenario, you will still utilize the same techniques for synchronizing forms listed in this topic.

Helping Users Locate Records

When users interact with form data, they must first be able to select a specific record or sets of records on which to operate. Access provides three built-in mechanisms for finding specific records: the new Filter By Form and Filter By Selection capabilities, and the standard Find dialog. However, your applications will sometimes require functionality more powerful than, or different from, these new features.

The section "Presenting Records to Users" earlier in this chapter demonstrated the use of Combo Box and List Box controls on a form to navigate within the form's recordset. When utilizing the record navigation techniques described in that section (as well as the new Access filter features), bear in mind that such approaches are generic in nature-users must know either the record they are looking for in advance, or the criteria for finding the record.

On the other hand, if the path the users will take most commonly to find or define a data subset is known at application design time, you can provide advanced record selection capabilities as you build the application. I have found that user satisfaction with our applications has increased as we have refined our ability to provide robust interfaces for record location/selection.

In a perfect world, the Access query grid would be an object that we as developers could program. It would be great to display a query grid to users, pre-populated with specific tables and fields, and have the user determine the criteria. Your applications could then use this information as a filter string or WHERE clause in an SQL statement.

In the absence of such niceties, you must determine what ad-hoc query requirements your users will have, and how to provide them with these capabilities. The remainder of this chapter concentrates on several such data location techniques.

Implementing Query-By-Form

Every major application should have a system administrator. One of the tasks usually assigned to this person is to facilitate the users'ability to interrogate the application's data.

The simplest model employed is for people to e-mail their data extraction requests to the system administrator, who runs impromptu queries and forwards the results in a spreadsheet, document, or database table back to the requester. More complex models create read-only replicas of the application's data and let users do their own ad-hoc queries off of the non-production copy of the data.

In between these two poles is a technique called "query-by-form." Precisely as the name implies, you must create a form to allow users to build queries. There is no standard model for a query-by-form interface; you simply create the layout that works best for the specific combination of users and application.

Figure 16.8 shows an example of a form that implements query-by-form. The form has these attributes:


Most query-by-form screens do not allow the user to join tables, because creating an interface to support this task is programmatically challenging. Instead, create a set of saved queries designed specifically to join tables into datasets that will be useful when interrogating the database. Display these queries in the list of tables and queries on the query-by-form screen. See Chapter 10, "Creating Expert Tables and Queries," for information on the various types of saved joins you can create to help users explore data.


Unless users understand how SQL operators are applied, allowing the user to create multiple criteria values and apply AND/OR operators between each criteria comparison can create a confusing situation (as in "A OR B AND C OR D"). In many environments, it is safer to build a query-by-form screen that either allows the entry of only one set of criteria, or alternately that applies the AND or OR operator to every criterion uniformly, as in the figure.

Fig. 16.8

A "query-by-form" layout is designed to assist users as they build an ad-hoc search string.

Here are some of the mechanical aspects of the form shown in the previous figure:

SELECT Name FROM MSysObjects

WHERE Name Not Like "MSys*" And Name Not Like "~*" And Name Not Like "zt*" _

And Type In (1,5)

ORDER BY Name;

Private mcolQBF(1 To 5) As New Collection ' Collection of field sets

Private Const mcbytField As Byte = 1 ' Field item

Private Const mcbytComp As Byte = 2 ' Comparison item

Private Const mcbytValue As Byte = 3 ' Value item

Dim ibyt As Byte

For ibyt = 1 To 5

mcolQBF(ibyt)(mcbytField) = Null

mcolQBF(ibyt)(mcbytComp) = Null

mcolQBF(ibyt)(mcbytValue) = Null

Next ibyt

Private Sub cboTbl_AfterUpdate()

' Purpose: Reload the field lists on table/query selection

Dim ibyt As Byte

Call cbfReset(False) ' Don't reset top line

For ibyt = 1 To 5

mcolQBF(ibyt)(mcbytField).RowSource = Me!cboTbl

mcolQBF(ibyt)(mcbytField).Requery

Next ibyt

End Sub

Dim dbs As Database

Dim qdf As QueryDef

Dim strSQL As String

Set dbs = CurrentDb

strSQL = cbfSQLBuild()

If Len(strSQL) > 0 Then

On Error Resume Next

dbs.QueryDefs.Delete "ztqryQBF"

On Error GoTo cmdRun_Click_Err

Set qdf = dbs.CreateQueryDef("ztqryQBF", strSQL)

DoCmd.OpenQuery "ztqryQBF", acNormal, acReadOnly

End If


I used the acReadOnly argument on the OpenQuery statement so that users cannot edit data presented to them by this ad-hoc interrogation tool. If you need to allow system administrators to edit the results of ad-hoc browsing, set this flag based on the current user ID and do not include it for administrators. This simple but effective security strategy gives the users some of the power of the query design grid without the complexity or direct access to data.

Alternately, in a secured environment you can set the QueryDef to run with user's permissions only, which will ensure that only people with editing rights can modify the raw data presented.


Figure 16.9 shows the temporary query that was built from the selections in the previous figure.

Fig. 16.9

This query result set was built from a temporary query written by code in the "query-by-form" object.

More complex versions of query-by-form screens can include these additional features:

A query-by-form interface such as the one shown presents users with an easy-to-use device for asking simple questions of their data. In many applications, the combination of comprehensive reporting and a simple query interface such as the one shown is adequate for all user requirements.

Creating a "Get" Form

An alternative record selection approach to the query-by-form tool is to create a form for filtering a specific dataset. Its record source would be specified in the design, and the form's purpose would be to help the user locate one record within a larger record set.

For example, users enter customer orders into an application. When customers call in later to ask questions about an order, the caller may not have the order number. Users of this system would require a tool for finding an order quickly while knowing only one or two of its attributes.

Figure 16.10 shows a form that assists users with this process. We call such a form a "Get" form because with it the user can easily get to one record.

Fig. 16.10

We build many of our applications around this proprietary Get form layout designed to help users filter and locate records.

The Get form is arranged to help the user find records by applying the most convenient criteria information. The user can enter one or more criteria values and receive a filtered list of matches.


80% of the time, your users will be trying to find records by searching only 20 percent of the application's table fields. If you correctly identify these fields and provide an interface to query them, you will have provided your users with a great benefit.


Here are a few features of our standard Get form:

Here are high-level steps to create a Get form like the one shown:

  1. Add a List Box control to the form and create a row source for it if you want it to load populated, or leave the row source blank for faster loading. Size the list box columns to display the data cleanly.
  2. Create a variable in the form's module to hold the raw SQL statement that provides the rows for the list box. The simple SELECT statement should have no WHERE or ORDER BY clauses:

    Private Const mcstrSQL As String = _

    "SELECT CustomerID, CompanyName, Phone, OrderID, OrderDate, ShippedDate " _

    & " FROM qryCustOrder"

  3. Place command buttons above the list box columns that will be used to sort the data. In the ControlTipText property of each button, place a control tip that will also serve as the ORDER BY clause when sorting the list. Such tip text would look like this:

    Order By CompanyName, OrderDate


I placed the ORDER BY clause in the ControlTipText property so that it is conveyed to users when they hover over a sort button with the mouse, and is also available to my code. Alternately, you could store the ORDER BY information in the Tag property instead and create a different control tip string to show the user.

  1. Add the filter display text box, and add as many criteria controls as are required to help the user work with the listed data. In general, you will select the most important records from the underlying data set to display in the limited width of the list box, then give the user a control for each column in the list in order to apply criteria to each important field.

You do not have to squeeze all the relevant information into the width of the list box. Because list box controls can have a horizontal scroll bar, you can place more columns in the list than are displayed in its width. However, doing this removes your ability to have the sort buttons at the top of the columns because there is no way to match the button text and events to the columns as the user scrolls horizontally in the list.

  1. Add the buttons that will provide the filtration capabilities. We use text on the buttons-Apply Filter and Remove Filter-that will be familiar to most Access users.
  2. Now begin creating your code. I have not provided all of the code here, just a few listings to give you a sense of the mechanics; the remainder of the code is on the CD in AES_FRM4.MDB.
  3. The form's Load event initializes a module-level variable that points to the currently selected button; this is used to retrieve the ORDER BY statement from the control tip, see step 9. Initializing the variable in this event presets the sort order to use the first time the list is loaded. This event also places a help string in the filter text box to help new users understand how to proceed:

    Private Sub Form_Load()

    ' Purpose: Set sort to first column

    Set mcmdSort = Me!cmdCustID ' Set pointer to sort button

    mcmdSort.FontWeight = mcintBold ' Bold current sort button

    Me!txtFilter = "<Click Remove Filter to display all records>"

    End Sub

  4. Next, create the code behind each sort button's Click event. This event must reset the pointer variable to reference the current sort order, and also sets the font effect of the button representing the sort order to bold. For example, the following code is in the Click event for cmdCompany, the button to sort by company name:

    mcmdSort.FontWeight = mcintNormal ' Unbold previous sort button

    Set mcmdSort = Me!cmdCompany ' Set pointer to sort button

    mcmdSort.FontWeight = mcintBold ' Bold current sort button

    Call cbfRequery ' Rebuild the list

  5. Finally, build the query logic. This routine must traverse the criteria controls and build an SQL statement from those that are filled in. The SQL is used as the RowSource property for the list. The code (abbreviated) to build the search statement is shown in Listing 16.4.

Listing 16.4 AES_Frm4.Mdb-Building a RowSource for a Get Form List

Private Sub cbfRequery()

' Purpose: Build SQL and requery the list box

Dim strFilter As String

DoCmd.Hourglass True

If Not IsNull(Me!cboCustID) Then

strFilter = strFilter & " CustomerID = '" & Me!cboCustID & "' AND"

End If

If Not IsNull(Me!cboPhone) Then

strFilter = strFilter & " Phone = '" & Me!cboPhone & "' AND"

End If

...

' Code here continues adding other criteria to the string

...

' Cleanup the string

If Right(strFilter, 3) = "AND" Then ' Strip trailing AND

strFilter = Trim(Left(strFilter, Len(strFilter) - 3))

End If

If Len(strFilter) > 0 Then

Me!txtFilter = strFilter ' Show the filter to the user

strFilter = " WHERE " & strFilter

Else

Me!txtFilter = "<none>"

End If

strFilter = strFilter & " " & mcmdSort.ControlTipText ' Add ORDER BY clause

Me!lstOrder.RowSource = mcstrSQL & strFilter ' Put SQL in the list box

Me!lstOrder.Requery

DoCmd.Hourglass False

End Sub

  1. Notice that your code must carefully construct the SQL statement depending on the values available. The SELECT portion of the SQL never changes and thus comes from the constant mcstrSQL. The order for the list comes from the ControlTipText property of the button object (mcmdSort) that designates the current sort order. Finally, the WHERE clause is built from the criteria or is left blank if the user has entered no criteria, which causes the return of all records into the list.

While Get forms of this type are simplistic, they have proven enormously popular with our clients and are not expensive to code. These forms are most useful for unskilled data entry and edit personnel; power users will prefer direct access to the data, Access's Filter by Form screen, or a query-by-form model as shown in the previous section.

As you become adept at Get forms, you can extend their capabilities. Here are some advanced options we build into this type of form:

Set qdf = dbs.CreateQueryDef("ztqryGetOrder", Me!lstOrder.RowSource)

DoCmd.OpenQuery "ztqryGetOrder", acNormal, acReadOnly

Whether you use this form model for your users or contrive a different approach, the principle demonstrated here will prove useful to you. As databases get larger, users need to be able to work with small subsets of data or they feel overwhelmed; and the application performance suffers as well.

Writing a List Constructor

Having spent any time in Access at all, you will have seen the selection list box pairs that are common in the wizards. A listbox-based selection metaphor places two list boxes beside each other with buttons in the middle to move items back and forth, as shown in the Table Wizard form in Figure 16.11.

Fig. 16.11

The Access Table Wizard provides a list box-based layout for selecting multiple values.

We call this combination of two list boxes and the selection buttons a "list constructor," because it is used to construct a selection list. To some extent, the usefulness of this interface tool has been lessened by the appearance of multi-select list boxes in Access. Nevertheless, a list constructor can do several things that a multi-select list cannot:


I will use the terms "available items list" and "selected items list" respectively to describe the left and right lists in a list constructor.

Figure 16.12 shows a list constructor based on the sample data used throughout this chapter. The tool in the figure allows a user to select one or more products and move them to the selected items list. The available items list can be filtered to remove clutter.

Fig. 16.12

This advanced selection wizard uses a pair of list box controls, list callback code, and a filtration combo box.

It would take an entire (lengthy) chapter to explain the workings of a list constructor in detail, so in this section I will only outline the concepts involved. Once you are familiar with list callback functions and working with arrays, you can tackle a list constructor.


I have provided a library module of list constructor code and a working example in AES_FRM4.MDB on the CD-ROM for you to use as is or to reverse-engineer and improve.

The first concept that is important when building list constructors is an understanding of callback functions. (See Chapter 13, "Mastering Combo and List Boxes," to learn how these are built.) Both the available and the selected item lists make use of these handy devices.

The reason that callback functions provide value here is that both the available and selected item lists are fluid, rather than fixed. It is easy to drive a fixed list from a table or query; but in a list where the values change constantly, the performance hit of continuously writing and deleting available and selected items in work tables is not tolerable.

In contrast, a callback function can be constructed to derive its values from an array. Displaying items in the available and selected lists is actually enabled by setting flags on items in a master array and rebuilding both list boxes based on the flags.

Thus, the second important concept in a list constructor is the structure of the driving arrays. Listing 16.5 shows the Dim statements for the arrays I will use in this tutorial.

Listing 16.5 AES_Frm4.Mdb-Structures Used by the List Constructor

' Type structure for the display flags

Type typFlags

blnSel As Boolean

blnAvail As Boolean

End Type

' Type structure containing selected & available pointers

Type typPtr

intSel As Integer

intAvail As Integer

End Type

' List management arrays

Dim mastrLstWiz() As String ' Contains the actual rows in the list

Dim matypFlags() As typFlags ' Contains display flags

Dim matypPtr() As typPtr ' Contains the pointers to the master array items

The three arrays dimensioned in the code listing are used to do the majority of the work of the list constructor:

Table 16.1 How The Available List Is Displayed Based On The Two Flags In The matypFlags Array

List Type

blnAvail

blnSel

Show in Available List

Copy

True

False

Yes

Copy

True

True

Yes

Move

True

False

Yes

Move

True

True

No

Tables 16.2 through 16.4 show the first three values in the three listed arrays after a constructor loads, and its arrays and list boxes have been initialized.

Table 16.2 The mastrLstWiz Array Stores All Available List Box Values, In This Case The Primary Key and Name For Each Product

Item

Value

(1)

17;Alice Mutton

(2)

3;Aniseed Syrup

(3)

40;Boston Crab Meat

Table 16.3 The matypFlags Array Determines How The Available And Selected Lists Display Their Values; Currently No Items Are Selected

Item

blnAvail

blnSel

(1)

True

False

(2)

True

False

(3)

True

False

Table 16.4 The matypPtr Array Mirrors The Structure Of The List Boxes. Each Value Indicates An Index In The Master Array.

Item

intAvail

intSel

(1)

1

0

(2)

2

0

(3)

3

0

The list callback derives its values from these structures by using the matypPtr array to locate a string in the mastrLstWiz array. Each time the callback fires to fetch a row to display in either the available or selected lists, it runs a line of code similar to this:

strValue = mastrLstWiz(matypPtr(vintRow).intAvail)

Using this line of code and referring back to the three previous tables, you note that if vintRow is 1, the value matypPtr(vintRow).intAvail translates into this string: matypPtr(1).intAvail, which (only by coincidence) happens to return a value of 1 (see the array contents in Table 16.4). This number provides the index for mastrLstWiz, so the command mastrLstWiz(matypPtr(vintRow).intAvail) translates into mastrLstWiz(1), which is "17;Alice Mutton." Therefore, the previous code line fills the available list with "Alice Mutton" when fired in the callback function.

As you have divined by this point, if the callback makes its decisions by asking the matypPtr array for pointers to the available and selected list items, the majority of the work in moving items back and forth between lists becomes simply managing the values in this array.

Here is an outline of the processes to make a selection from the available list to the selected list:

  1. Review the available list and see which item or items the user has selected. Flag the selected items in the flag array (matypFlags). Listing 16.6 provides a code snippet from this process.

Listing 16.6 AES_Frm4.Mdb-Determining the User's Selections

For Each varItm In rlstAvail.ItemsSelected

' Get the primary key value from the list

varValue = rlstAvail.Column(0, varItm)

' Search in the master array for the selected item

For iintLoop = 1 To mintMaxRow

' Find the primary key value in the master array

If lci_DelimItemGet(mastrLstWiz(iintLoop), ";", 1) = varValue Then

matypFlags(iintLoop).blnSel = True ' Flag as selected

Exit For

End If

Next iintLoop

Next varItm

  1. Traverse the flag array (matypFlags) updated in the previous step and rebuild from it the pointer array (matypPtr), which mirrors the two lists shown to the user. Each item in the flag array is checked to see if it is available for display in the available list and, if so, is added to the pointer array dimension for that list. The process is then repeated for the selected list. Listing 16.7 provides a code snippet that helps demonstrate the process.

Listing 16.7 AES_Frm4.Mdb-Building the Selection Pointer Array

intCount = 0

' matypLst is an info array not described in the tutorial

' matypLst.intRows simply contains the number of rows in the matypFlags array

For iintLoop = 1 To matypLst.intRows

If matypFlags(iintLoop).blnAvail And Not matypFlags(iintLoop).blnSel Then

intCount = intCount + 1

matypPtr(intCount).intAvail = iintLoop

End If

Next

  1. Requery the list boxes, which runs the callback function for each row in each list. As described earlier, the callback function walks down the pointer array and fetches the corresponding strings from the master array, adding them to the list boxes.

Tables 16.5 through 16.7 show the first three values in the three arrays after a user filters the available list to display only "Condiments," and then moves the value "Aniseed Syrup" (the second value in the master array) from the available to the selected items list.

Table 16.5 The mastrLstWiz Array Stores All Available ListBox Values, This List Is Unchanged After A Move Operation

Item

Value

(1)

17;Alice Mutton

(2)

3;Aniseed Syrup

(3)

40;Boston Crab Meat

Table 16.6 The matypFlags Array Indicates That Only Items Matching The Filter Are Available, and That Item 2 In The Array Has Been Moved To The Selected List

Item

blnAvail

blnSel

Comment

(1)

False

False

"Alice Mutton" is not in the "Condiment" filter

(2)

True

True

"Aniseed Syrup" is a "Condiment" and is selected

(3)

False

False

"Boston Crab Meat" is not in the "Condiment" filter

Table 16.7 The matypPtr Array Mirrors The Structure Of The List Boxes. Compared to Table 16.4, The Available List Has Been Filtered And The Selected List Now Has One Item.

Item

intAvail

intSel

(1)

9

2

(2)

10

0

(3)

17

0

The code to facilitate a list constructor can be fairly non-intimidating once you understand the flow described here. The routines shown in this tutorial, and on the CD in AES_FRM4.MDB, are more complex than in a simple list constructor because they take two factors into account:

While building your first set of list constructors can take some effort, once you have created a reusable methodology and code library for this interface device, you will find yourself using it in most of your applications. The usability advantages of this interface metaphor-users are familiar with it, and it is visually uncomplicated yet powerful-make it an appealing choice to solve many data management problems.

From Here...

An application's interface is enhanced by the accurate and simple presentation of information to its users and by assisting the users when they need to find specific information. The foundation for the ideas in this chapter was laid in these related chapters:


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