MS-Access / Getting Started

---

Finding Records

As you saw in the preceding section, you often need a way to find a specific record when working with recordsets. DAO provides two ways to find a specific record: Seek and Find. The one you choose to use depends entirely on the type of recordset you want to use it on.

The Seek Method

The Seek method is the fastest way to find a specific record, but it can be used only on table-type recordsets because it specifically relies on the table's indexes. Naturally, the table must have at least one index for it to search on. Trying to call Seek against a non-table-type recordset will earn you a runtime error. Seek uses the following syntax:

rst.Seek comparison, key1, key2. . .key13

To use Seek, you must specify three things: the name of the index to use (you can specify only one index at a time), a comparison operator string (which can be <, <=, =, =>,or >), and one or more values that correspond to the value of the key you're looking for. You can specify up to 13 different key values.

For example, the following code shows how to search the tblCustomers table to find a customer whose CustomerNo is 123:

Set rst = dbs.OpenRecordset("tblCustomer", dbOpenTable)
rst.Index = "CustomerNo"
rst.Seek "=", 123

You might recall from the section on creating table indexes that the primary key index is called PrimaryKey by default, although you can name it anything you like. If you want to use the table's primary key index, you must know its name.

To use Seek effectively, you need to understand how it works. If you specify =, =>,or > as the comparison operator, Access starts its search at the beginning of the recordset and works its way to the end. If you use any of the other operators, Access starts at the end of the recordset, and moves toward the beginning. With that knowledge, you can see that using Seek within a loop is essentially pointless.

You must specify a key value for each column in the index, particularly if you're using the = comparison operator. The reason is that some of the key fields may default to Null, and because nothing can "equal" Null, your Seek method will usually not find what you're looking for.

The Seek method is not supported for linked tables, but all is not lost; the following code demonstrates how to use Seek on a linked table:

'Open the database that contains the table that is linked
Set dbs = OpenDatabase(strMyExternalDatabase)

'Open a table-type recordset against the external table
Set rst = dbs.OpenRecordset("tblCustomers", dbOpenTable)

'Specify which index to search on
rst.Index = "CustomerNo"

'Specify the criteria
rst.Seek "=", 123

'Check the result
If rst.NoMatch Then
    MsgBox "Record not found."
Else
    MsgBox "Customer name: " & rs.CustName
End If

What this does is open the external database that contains the table that is linked in the current database. It then creates a table-type recordset on the table in that database, so that you are operating directly on the table you want to search. The code searches the table and, finally, checks to see if the search failed. Never assume that the search is successful; instead, always use the recordset's NoMatch property to determine the result.

Even doing things this way, in most circumstances, the Seek method is still faster than the Find methods.

The Find Methods

There are four Find methods: FindFirst, FindPrevious, FindNext, and FindLast. Their purpose is self-evident, given their names, and you can use them on all recordset types.

Because the Find methods enable you to specify any field in the criteria, they may not be capable of using a table's indexes to execute a search. Compare this to the Seek method, which always uses a table's indexes to execute the search. Without an indexed field, the Find methods can just as easily use a table scan to find the right record; it just depends on the type of search, and amount of data being searched. Not surprisingly then, using the Find methods is usually far slower than using Seek.

A table scan is where the database engine must read each record as a part of a search. This often results in a query or operation that is significantly slower than methods such as Seek.

The Find methods can be used on filtered dynaset and snapshot recordsets, which minimizes the number of records that have to be searched.

In addition, because you have FindNext and FindPrevious methods at your disposal, you don't have to start at the beginning or end of the recordset to find subsequent matches; you can just keep searching until you find the record you want.

All four methods use the same syntax:

rs.[FindFirst | FindPrevious | FindNext | FindLast] criteria

The criteria argument can be any valid SQL WHERE clause, without the word WHERE. For example, the following code demonstrates how to find all instances of a customer having the word parts in his or her name.

Sub FindOrgName()
    Dim dbs As DAO.Database
    Dim rst As DAO.Recordset

    'Get the database and recordset
    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblCustomers")

    'Search for the first matching record
    rst.FindFirst "[OrgName] LIKE '*parts*'"

    'Check the result
    If rst.NoMatch Then
	MsgBox "Record not found."
	GoTo Cleanup
    Else
	Do While Not rs.NoMatch
	    MsgBox "Customer name: " & rst!CustName
	    rs.FindNext "[OrgName] LIKE '*parts*'"
	Loop

	'Search for the next matching record
	rst.FindNext "[OrgName] LIKE '*parts*'"
    End If

Cleanup:
    rst.Close
    Set rst = Nothing
    Set dbs = Nothing
End Sub

Once a matching record is found, any subsequent search begins from the current cursor position, not the start or end of the recordset like in the Seek method. Again, always follow the search with a check of the recordset's NoMatch property, to determine the result of the search.