Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Microsoft: Access Forms FAQ

User Interface Techniques

How can I create a record navigation pick list on a form by RickSpr
Posted: 22 Jun 03 (Edited 22 Jun 03)

Although it's easy to implement Access' Find feature on a form, often a visual index of the records is much more convenient for the users. A pick list is such a visual index. The user can scroll through the list, which is usually sorted, to find a particular record, and can then click the list entry to position the form to the full record.

You can implement a pick list using either a list box or a combo box. The list box is better if you have plenty of room, because it is available all the time, while the combo box is better if you have limited space.

How to do it
Note: This applies to an Access database (.mdb file). It might work in an Access project (.adp file) if you modify the code below to use an ADO recordset; I haven't tried it.
  1. Enlarge the form to make room for the list box or combo box. It's best to place a list box on the left side, or a combo box at the top of the form. If your form is too large to fit entirely on the screen, it may be better to place the combo box in the form header section so that it doesn't scroll out of view.

  2. Add the list or combo box and name it. (For this example, I will assume a list box named "lstPickList".)
  3. Set the list/combo box's Row Source Type to Table/Query.

  4. Set the list/combo box's Row Source property. It can be a table or query name, or an SQL statement, and will include the same rows as the form's Record Source. It must include the columns you want the user to see, which should be enough to uniquely identify a record. If your records have a single-column unique key, make sure to include that, too. Note: You can also include expressions for display, such as LastName & ", " & FirstName, in the Row Source, but there must still be one or more individual columns that uniquely identify a record.
    To be most useful, the Row Source should be ordered on some column(s) so the user will be able to find an item easily. This does not have to be the unique identifier column(s). You may need to create a new query or SQL statement to add an appropriate ORDER BY clause.

  5. Set the list/combo box's Column Count property to the number of columns in the Row Source table or query.

  6. If you want all the columns in your Row Source to be visible, leave the Column Widths property empty. Otherwise, set the column width for each invisible column to 0. See the Access Help file topic "ColumnWidth Property" for an explanation of how to code this property.

  7. If you're using a combo box, you might want to increase the value of the List Rows property so your users can see more items at a time.

  8. Switch the form to Form View and verify that the list looks the way you want it to.

  9. Set the list/combo box's After Update property to [Event Procedure] and click the builder (...) button at the right. This will display the form module with the cursor positioned in an empty event procedure.

  10. Enter the following code:
    Private Sub lstPickList_AfterUpdate()
        Dim rst As DAO.Recordset
        Set rst = Me.RecordsetClone
        rst.FindFirst "
    FldName='" & lstPickList.Column(0) & "'"
        If rst.NoMatch Then
            MsgBox "The selected record can not be displayed because it is filtered out. " _
                & "To display this record, you must first turn off record filtering.", _
            Me.Bookmark = rst.Bookmark
        End If
        Set rst = Nothing
    End Sub

  11. Modify the code of the FindFirst statement as follows:
    • If you need to match on multiple columns, duplicate the expression once for each column, and separate the duplicates with "AND". For example, to match on two columns use:
            "FldName='" & lstPickList.Column(0) & "' AND FldName='" & lstPickList.Column(0)

    • For each field to be matched, replace a FldName with the name of the field in the form's Record Source, and replace the following 0 with the number of the corresponding column in the list/combo box's Row Source. (The list/combo box's columns are numbered starting at 0.) Also, if the field is numeric, remove the apostrophes (') from the expression.

  12. Save the form and test it.

Enhancing the pick list behavior
One small problem with the pick list, as defined above, is that if the user navigates to a different record by some other means (such as using the navigation bar at the bottom of the form, or applying a filter), the pick list isn't updated to reflect the current record. This can sometimes be confusing to the user.

If your pick list has a single column (it doesn't have to be a visible column) that uniquely identifies the record, you can modify the form to keep your pick list synchronized to the current record.
  1. Set the pick list's Bound Column property to the number of the identifying column in the pick list's Row Source. (For this purpose, the positions are numbered starting with 1.)

  2. If you don't already have one, create a Form_Current event procedure.

  3. In the Form_Current procedure, add the following code:
        If IsNull(
    FldName) Then
            lstPickList = Null
        ElseIf Nz(lstPickList) <>
    FldName Then
            lstPickList =
    For FldName substitute the name of the field in the form's Record Source.

The ElseIf condition is necessary to avoid an effect caused by event recursion. If the user selects a record from the pick list, this causes record navigation, but the record navigation then causes the Current event. If this event then updates the pick list, it spoils the highlighting of the selected item, so the ElseIf test prevents this.

Back to Microsoft: Access Forms FAQ Index
Back to Microsoft: Access Forms Forum

My Archive

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close