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

Once the desired record has been located by navigating the list, the user can drill down to the underlying full client record by:

Hit Enter Key

The Dummy text box control is used to highlight the active record in the list by appearing as an underline. When the desired record is underlined, the user can hit  Enter to open the client form.

This is handled by the On Key Down event of the Dummy control that calls the GoToPCForm procedure (see below).

Click on Hot Zone

A transparent command button covers the ClientNumber text box. When the user clicks in this hot zone, the GoToPCForm procedure is called.

(Click to enlarge. Opens in new window.)
Fig. 4-1 Dummy On Click Event

Drill Down

'----------------------------------------------------------
Public Sub GoToPCForm()
On Error Resume Next

Dummy.SetFocus
gLngClientNumber = ClientNumber
DoCmd.OpenForm "Client"

End Sub
'----------------------------------------------------------

The client form can be opened either by:

Open as dialog (DoCmd.OpenForm "Client",,,,,acDialog)

Open normally (DoCmd.OpenForm "Client") - Uses form's design Pop Up/Modal settings

Also, we need to consider whether to keep the list form open, or close it when opening (drilling down to) the client form. Keeping the list form open requires that your code on the client form trap for changes that affect the list form (change in name spelling, change in City, etc) so that the list form always displays current data. This does vary a bit between versions of Access, and whether you are opening forms in a dialog mode. 

The requery event for a form will read the data table(s) and the form will then display current data. There are some version issues (Access 97, 97 with one more service releases installed, Access 2000, 2003, etc) as to whether (and when) the list form will automatically reflect changes made to the data by another form. Requery works for sure, but may not be necessary in all configurations. 

This adds a level of complexity that can be eliminated by simply closing the list form, opening the client form, and - from the client form - let the user either return to the list (open the list form), or return the Main Menu.

So you might have code such as:

DoCmd.OpenForm "Client"
DoCmd.Close acForm, Me.Name

If the list form is closed before opening the client form, reopening the list form when it is needed again, should ensure that the it reflects current data values. 

Opening a form based on one record

We want the Client form to open and display only the (one) desired record.

What we want to avoid is opening a complex form (has subforms, combo boxes, list boxes, memo fields, etc) that will pull the entire recordset (all the records such as the entire client table along with related data) across the network.

One of the  reasons of using a list navigation form is to allow the user to find the target record (client), then pull only that one client's data.

Using the Where argument for OpenForm

One method is to use the Where argument of the DoCmd.Openform statement (see Access, Help, DoCmd object, methods, OpenForm method).

In our case when the user calls the GoToPCForm procedure, we know the ClientNumber because the user calls this from an active record on the screen.

ClientNumber is the PrimaryKey for the Clients table, and is a numeric data type (autonumber = long integer).

An example of an OpenForm statement that we can use is:

    DoCmd.OpenForm "Client", , , "ClientNumber = " & CStr(ClientNumber)

This presumes that the Client form is based on a table or query that returns all the client records so that Access can filter the recordset and present the desired record.

Depending on how much functionality that the developer has set for the form/application, using this technique can allow the user to remove the filter, and open the entire recordset.

Opening a form based on a query that returns one record

By the use of a global variable (gLngClientNumber) we can fashion a query that will return a single client record (and prevent the user from pulling the entire client recordset).

The steps for this approach are:

1. Declare a global variable (Fig. 4-2)

2. Build a function to return the value of the variable (Fig. 4-3)

3. Build a query uses the function as the query criteria (Fig. 4-4)

4. Set the query as the record source for the target form (Fig. 4-5)

5. Set global variable before opening the form

Fig. 4-2 Declare Global Variable

Fig. 4-3 Build a function to return the value of the variable

Fig. 4-4 Build a query uses the function as the query criteria

Fig. 4-5 Set the query as the record source for the target form

5. Set global variable before opening the form

    gLngClientNumber = ClientNumber
    DoCmd.OpenForm "Client"