Home Page     Contact Dave Thompson Consulting   Site Map 

Dave Thompson Consulting
408 Rose Drive
    Allen, Texas 75002   
(Dallas/Ft Worth area)
(972) 727-5670

A List Navigation and Drill Down User Interface for MS Access

Building the List Form

Section 1 - Data Elements and Form Controls

Introduction

Section 1 - Data/Form

Section 2 - Underline Data

Section 3 - Find Commands

Section 4 - Drill Down

Client database example is described in more detail on this site (See Client Information System - Introduction)

Step 1. Data Elements

In daily use, the application is primarily used to lookup and edit client information. Our first step is to decide on the data elements needed to find a client so that we can display the client record and related information.

bullet

Client Number

bullet

Last Name

bullet

Client City

bullet

Client State

bullet

Client Zip

bullet

Planner Name (financial planner assigned to client)

The data elements listed above are sufficient to locate a specific client. Client Number always uniquely identifies a client. 

However, sometimes we need to search without the client number. When searching on the other fields (Section 3 - Find Commands), we will sort the form by more than one field to assist in locating a record.

For example, a search for a specific Zip Code will first sort the form by Zip, then by LastName.

The idea is have enough fields to locate a client not only by client number, but also when we have only partial information such as "Mr. Banks in Zip Code 80201".

Step 2. Build query that returns only the needed data elements.

Query design for list form (Click to enlarge) (Click to enlarge. Opens in new window.)

We use a query that returns only data needed for our search (navigation) form. A major reason for this is performance.

 When we open a form based on more than one record (in this case a continuous form), Access will populate (pull all the data for all the rows/records) the entire form. For the typical MS Access application (Access used as front-end program and back-end data), the primary determining factor for performance in a multi-user, network environment is network traffic. 

While applications may seem fast during the development stage (using only a few test records), when put into production (everyday use by user community), the number of records in the database can grow quite large. Additionally, the user community may include - in addition to local users - remote users accessing the system in a variety of configurations, from dial-up to high-speed, remote network connections. 

If our goal is to locate one client's information, we should avoid the overhead of pulling all the data for all the client records in the database, 

Step 3. Build list form

Navigation List Form Layout (Click to enlarge)

Fortunately, all the fields (data elements from Step 1) will fit on one line. This will display 20 or more (depending on the user's screen resolution) records at a time.

We add some color to assist the user with visual clues as to the contents of the data (for example, last name on light blue background). 

And in this application, a yellow background is used to indicate a hot zone for drill down (Section 4 - Drill Down and Step 5a below).

Step 4. Properties of data text boxes

Data Text Boxes (Click to enlarge)

The list navigation form is not used to edit data. It is used to find a specific record, then open another form (drill down) for data review or editing.

Also, we are using an underline technique (Section 2 - Underline Data) that employs a text box (not bound to any data) to highlight the active record/row. We keep the cursor focus there (and not on any of the actual data text boxes).

To achieve this, we set the text box properties for the data on the form to Enabled = No and Locked = Yes.

Step 5a. Add transparent command button.

The command button enables the user to click on a Client ID to select the client and open the full client data form. Set the properties as shown (we will describe the OnClick event later).

Command button properties (Click to enlarge)

In Access, command buttons can be set as transparent, sized, and placed (really anywhere on a form) over a text area, or graphic, so that we can process command button events when a user clicks on the command button's location (hot zone).

 

Step 5b. Place the command button over the ClientNumber text box.

Use the format menu to make sure that the command button is on top of the ClientNumber text box.

Command Button - Bring to Front (Click to enlarge)