Microsoft Access Tips for Serious Users

Provided by Allen Browne.

Using a Combo Box to Find Records

Warning: In Access 97 or earlier, this tip can trigger the Bookmark Bug.
If you receive an error in Access 2000 or later, see Solving problems with References.

It is possible to use an unbound combo box in the header of a form as a means of record navigation. The idea is to select an entry from the drop-down list, and have Access take you to that record.

Assume you have a table called "tblCustomers" with the following structure:

   CustomerID       AutoNumber (indexed as Primary Key).
   Company          Text
   ContactPerson    Text

A form displays data from this table in Single Form view. Add a combo box to the form's header, with the following properties:

   Name             cboMoveTo
   Control Source   [leave this blank]
   Row Source Type  Table/Query
   Row Source       tblCustomers
   Column Count     3
   Column Widths    0.6 in; 1.2 in; 1.2 in
   Bound Column     1
   List Width       3.2 in
   Limit to List    Yes

Now attach this code to the AfterUpdate property of the Combo Box:

Sub CboMoveTo_AfterUpdate ()
    Dim rs As DAO.Recordset

    If Not IsNull(Me.cboMoveTo) Then
        'Save before move.
        If Me.Dirty Then
            Me.Dirty = False
        End If
        'Search in the clone set.
        Set rs = Me.RecordsetClone
        rs.FindFirst "[CustomerID] = " & Me.cboMoveTo
        If rs.NoMatch Then
            MsgBox "Not found: filtered?"
        Else
            'Display the found record in the form.
            Me.Bookmark = rs.Bookmark
        End If
        Set rs = Nothing
    End If
End Sub

The steps this procedure takes are:

• Save the record. (This prevents a bug if the record is dirty and can't be saved, e.g. a required field is missing.)
• Refer to RecordsetClone - a duplicate set of pointers to the records behind this form ("Me").
• Use FindFirst to match the field to be searched with the contents of the combo box.
• Display the record, by matching the form's BookMark to the record found in the clone recordset.

Note: If CustomerID is a Text type field in your table, you need extra quotes, i.e.:
    rs.FindFirst "[CustomerID] = """ & Me.cboMoveTo & """"
For an explanation, see Quotation marks within quotes.

If you want to create a search form, not merely a navigation combo, see the Search Criteria database.