How can I create a record navigation pick list on a form

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.
[ol][li]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.[/li]
[li]Add the list or combo box and name it. (For this example, I will assume a list box named "lstPickList".)
[li]Set the list/combo box's Row Source Type to Table/Query.[/li]
[li]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 [color blue]LastName & ", " & FirstName[/color], 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.[/li]
[li]Set the list/combo box's Column Count property to the number of columns in the Row Source table or query.[/li]
[li]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.[/li]
[li]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.[/li]
[li]Switch the form to Form View and verify that the list looks the way you want it to.[/li]
[li]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.[/li]
[li]Enter the following code: [red]FldName[/red] [red]0[/red] [li]Modify the code of the FindFirst statement as follows:
[ul][li]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)[/li]
[li]For each field to be matched, replace a [red]FldName[/red] with the name of the field in the form's Record Source, and replace the following [red]0[/red] 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.[/li][/ul]
[li]Save the form and test it.[/li][/ol]

-----------------------------------------
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.
[ol][li]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.)[/li]
[li]If you don't already have one, create a Form_Current event procedure.[/li]
[li]In the Form_Current procedure, add the following code: [red]FldName[/red] [red]FldName[/red] [red]FldName[/red]
For [red]FldName[/red] substitute the name of the field in the form's Record Source.[/li][/ol]
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.