Using a Combo Box to Locate Records

        13 votes: ****      31,225 views      1 comments
by Allen Browne, 20 April 2005    (Access 97+)

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.


Home Index of tips Top

Rate this article:   Your rating: Poor Your rating: Not so good Your rating: Average Your rating: Good Your rating: Excellent


This is a cached tutorial, reproduced with permission.

Have your say - comment on this article.

What did you think of 'Using a Combo Box to Locate Records'?


1.

Drano says...

20 October 2010

 
Thanks for the tutorial, this is one trick I have needed many times in the past. Very Much appreciated.

Have your say...

Name
E-mail (e-mail address will be kept private)
Comments
Verify Code
Verification Code


It may take 30 seconds after clicking submit - please be patient to avoid duplicate submissions.  

All comments must be approved before being displayed on this web page.  This process may take up to 24 hours.