Looking to take your VBA skills further?...

Discover twinBASIC — a powerful new development platform that expands on VBA and VB6 with advanced features, modern tools, and enhanced compatibility. Perfect for those ready to elevate their projects or transition from VBA, twinBASIC lets you build on what you already know and take your applications to the next level!

Try out twinBASIC Community Edition - it's free!

Using a Combo Box to Locate Records

        15 votes: *****     31,617 views      1 comment
by Allen Browne, 20 April 2005    (for 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: PoorYour rating: Not so goodYour rating: AverageYour rating: GoodYour 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 Oct 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


Comments require approval before being displayed on this page (allow 24 hours).