Building the List Form

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 2 - Underline Data Rows

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)

Add text box to underline the active record.

At Step 4 (Section 1) we set the text box properties for the data on the form as Enabled = No and Locked = Yes. None of these data text boxes can receive focus (have the cursor in the text box).

We will add a text box that can have the focus, and it will serve to underline the active record.

Create a text box, Name = Dummy, Enabled = Yes, Locked = Yes. Delete the label for the text box, leaving only the text box.

Size the text box to fit the form left-to-right, and one or two screen grid markers high.

(Click to enlarge. Opens in new window.)
Fig. 2-1 Create Dummy text box

To set the color that will appear when the form is used for the underline text box, first set the back color as desired (red in our case), then set the back color to transparent.

When the control has the focus, it will "remember" the last back color set before the control property is set to transparent, and that color will be displayed.

Fig. 2-2 Set color for underline

Move the Dummy text box up so that it touches the row of data text boxes, close up the extra space in the Detail Section of the form, and set the Detail Section back color as desired.

Set tab order for detail (data) section of form.

Finally, set Dummy as the first control in tab order.

The order of the other controls is not important since they cannot accept focus anyway.

Fig. 2-3 Set form tab order

Use VBA in the On Key Down event to move the underline.

The underline can be moved from record-to-record using the following keys:

	Up Arrow / Down Arrow (Move one record up/down)
	Page Up / Page / Down (Move a fixed number of rows up/down)
	Ctrl+End / Ctrl+Home (Go to last / first record)

When the underline control (Dummy) has the focus, we can use the OnKeyDown event to respond to directional keys pressed by the user.

Set On Key Down Event
Fig. 2-4 Dummy On Key Down Event

Private Sub Dummy_KeyDown(KeyCode As Integer, Shift As Integer)
On Error Resume Next ' suppresses error message when trying to move before first record, or after last record

Dim c As Control
Const mMoveRows = 10
Set c = City 'text box
c.Enabled = True ' must enable to receive focus
c.SetFocus ' move focus temporarily to this control

Select Case KeyCode

    Case vbKeyReturn ' Enter key - Use to call drill down procedure
        KeyCode = 0 ' Absorbs the keystroke
        GoToPCForm ' Calls the procedure that opens the full client information form

    Case vbKeyDown ' Down Arrow
        KeyCode = 0
        DoCmd.GoToRecord , , acNext

    Case vbKeyUp ' Up Arrow
        KeyCode = 0
        DoCmd.GoToRecord , , acPrevious

    Case vbKeyPageDown ' Page Down
        KeyCode = 0
        DoCmd.GoToRecord , , acNext, mMoveRows

    Case vbKeyPageUp ' Page Up
        DoCmd.GoToRecord , , acPrevious, mMoveRows

    Case vbKeyHome ' Ctrl+Home
        If (Shift And acCtrlMask) Then
            c.SetFocus
            KeyCode = 0
            DoCmd.GoToRecord , , acFirst
            Dummy.SetFocus

            Else
            KeyCode = 0
        End If

    Case vbKeyEnd ' Ctrl+End
        If (Shift And acCtrlMask) Then
            KeyCode = 0
            DoCmd.GoToRecord , , acLast

            Else
            KeyCode = 0
        End If

End Select

Dummy.SetFocus
c.Enabled = False ' disable control so that it cannot receive focus
Set c = Nothing

End Sub

While users normally will use one of the search features to find a record, directional keys can also be used to navigate the data list.