Finding Records

Another common task with a recordset is to find one or more records that meet specified criteria. Access offers several approaches to this task. With earlier versions of Access, many developers used one or more variations of the Find method. Access 2000 introduced a single Find method that consolidates the functionality of the earlier Find methods. If your applications used the earlier Find methods, you can achieve the same results with the consolidated method that Access 2000 introduced. Access 2002 adds a new capability to the Find method initially made available to Access developers with Access 2000.

The following code shows a simple application of the Find method that searches for a record with a customer ID that begins with the letter D. When it finds a record matching its criteria, the method relocates the current record to that location. The code prints the CustomerID, ContactName, and Phone fields to confirm exactly which record matches the criterion.

Sub FindAMatch()
Dim rst1 As Recordset
 
'Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , adOpenKeyset, adLockPessimistic, _
 adCmdTable
 
'Find the first row with a CustomerID beginning with D,
'and print the result
rst1.Find ("CustomerID Like 'D*'")
Debug.Print rst1("CustomerID"), rst1("ContactName"), rst1("Phone")
 
'Clean up objects
rst1.Close
Set rst1 = Nothing
 
End Sub

One drawback to the FindAMatch procedure is that it searches for a single match to the criteria and then stops immediately after finding it. The code that follows, which shows the FindAMatch2 procedure, discovers all the records that match the criterion statement. This simple application reveals more of the flexibility of the Find method:

Sub FindAMatch2()
Dim rst1 As Recordset
 
'Instantiate and open recordset
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.Open "Customers", , adOpenKeyset, _
 adLockPessimistic, adCmdTable
 
'Open an infinite loop for all records matching
'the criterion
Do
 rst1.Find ("CustomerID Like 'D*'")
 If rst1.EOF Then
'Exit the procedure when no more matches exist
 Exit Do
 End If
 Debug.Print rst1.Fields("CustomerID"), rst1("ContactName"), _
 rst1("Phone")
 rst1.MoveNext
Loop
 
'Clean up objects
rst1.Close
Set rst1 = Nothing
 
End Sub

The trick to finding all the records that match the search criterion is to embed the Find method in an infinite Do loop. When the Find method sets the recordset's EOF property to True, there are no additional matching records. In this case, the code executes an Exit Do statement to exit the loop. As long as Find keeps discovering new matches, the procedure prints CustomerID, ContactName, and Phone fields in the Immediate window. After printing a matching record, the procedure advances the current record by one. Without this, the Find method would repeatedly return the same record.

One innovation available to those using Access 2002 is the dynamic index capability of the Cursor Service. Access 2002 ships natively with an ADO version that supports dynamic indexes. To speed the operation of the Find method, developers can create dynamic indexes on the fly for record sources that do not have an index on a search field. Creating a dynamic index is a two-step process. First, assign the adUseClient intrinsic constant to the recordset's CursorLocation property. Second, set the Optimize property to True in the Properties collection so that the field will gain the dynamic index. The index goes out of scope when you close the procedure, but you can force the index's removal by setting the field's Optimize property to False.

NOTE

You can acquire the dynamic index feature in Access 2000 by installing a more recent ADO version than the one initially shipped with Access 2000.

The following pair of procedures demonstrates how to use a dynamic index to speed the search for all the customers from a particular country. The first procedure, CallFindCustomersInACountry, assigns a search string value and passes it to the second procedure, FindCustomersInACountry. Notice that the sample code uses a pound sign (#) to delimit the country string within the criterion string. With Access 2002, you can use either # or an apostrophe (') for this purpose.

Before opening a recordset on the Customers table, the second procedure turns on the Cursor Service by setting the CursorLocation property to adUseClient. Then after the recordset is open, the procedure sets the Country field's Optimize priority to True. This causes the creation of the index. Next the procedure performs a normal search to find all the customers from the country designated by strCriterion. In the cleanup process for the procedure, there is nothing special you need to do with the index. It goes out of scope automatically.

Sub CallFindCustomersInACountry()
Dim strCriterion As String
 
'Set string for country criterion, and pass it
'to the routine to do the search
strCriterion = "Country = #USA#"
FindCustomersInACountry strCriterion
 
End Sub
 
 
Sub FindCustomersInACountry(strCriterion As String)
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
 
'Instantiate and open recordset; invoke the Cursor Service by
'setting the CursorLocation property to adUseClient
Set rst1 = New ADODB.Recordset
With rst1
 .ActiveConnection = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\PMA Samples\Northwind.mdb;"
 .CursorLocation = adUseClient
 .Open "customers", , adOpenKeyset, _
 adLockPessimistic, adCmdTable
End With
 
'Create a dynamic index on Country
rst1.Fields("Country").Properties("Optimize") = True
 
'Find the longest ContactName field, and add one to it
int1 = FindTheLongest(rst1, "ContactName") + 1
 
'Open an infinite loop for all records matching
'the criterion
rst1.MoveFirst
Do
 rst1.Find (strCriterion)
 If rst1.EOF Then
'Exit the procedure when no more matches exist
 Exit Sub
 End If
 Debug.Print rst1.Fields("CustomerID"), rst1("ContactName") & _
 String(int1 - Len(rst1("ContactName")), " ") & " " & rst1("Phone")
 rst1.MoveNext
Loop
 
'Clean up objects
rst1.Close
Set rst1 = Nothing
 
End Sub

Seeking Records

The Seek method is another recordset method that can facilitate finding records in a record source. This method is exclusively used for recordsets that specify a table as their source. In addition, you must set the Options parameter for the recordset Open method to adCmdTableDirect, instead of setting it to adCmdTable or leaving it blank. Furthermore, the table serving as a record source must have an index on the field or fields that your application searches to find matches for criterion values. ADO does not offer built-in support for creating dynamic indexes for the Seek method, but your application can programmatically create an index using the ADOX library for Jet and the SQL-DMO library for SQL Server. The Seek method also requires a server-side cursor. Using any value other than adUseServer for a recordset's CursorLocation property disables the method.

NOTE

The Seek method is not a core ADO data access feature. ADO providers can optionally offer it. Use the recordset Supports method with adSeek as an argument to return a Boolean indicating whether a provider offers the Seek method. To seek on an index, apply the method to a recordset after opening it.

The Seek method searches for a key value or values, and it optionally takes a SeekOption parameter that can guide the operation of the method. If the index for the Seek method has just one column, you can specify only a single criterion value. If the index for a recordset relies on multiple columns, use an Array function to specify values for all the columns in the index. This capability to search concurrently on multiple columns is one feature distinguishing the Seek method from the Find method. The Seek method makes available the recordset in the order of the index and positions the current record at the first record matching the criteria.

You can refine the search behavior of the Seek method with some settings for its SeekOptions parameter using one of theSeekEnum enums. The SeekOptions setting appears immediately after the key value or values for a Seek method. The syntax for the Seek method with both key values and SeekOptions is rst.Seek <Keyvalues>, <SeekOptions>. Table 2-5 lists the SeekEnum members with their values and behavior.

Table 2-5 Intrinsic Constants for the SeekEnum

Constant	Value	Behavior
adSeekFirstEQ	1	Seek the first record on the index or indexes matching the key value or values.
adSeekLastEQ	2	Seek the last record on the index or indexes matching the key value or values.
adSeekAfterEQ	4	Seek the first record (or the one after it when no match exists) on the index or indexes matching the key value or values.
adSeekAfter	8	Seek the first record after that potential match to the key value or values on the index or indexes.
adSeekBeforeEQ	16	Seek the first record (or the one before it when no match exists) on the index or indexes matching the key value or values.
adSeekBefore	32	Seek the first record before a potential match to the key value or values on the index or indexes.

The SeekingUnShippedOrders procedure that follows illustrates three ways to use the Seek method. Before actually invoking the Seek method, your application must properly prepare the recordset. First, assign the Index property so that it contains the values for which you want to search. Second, open the recordset with the adCmdTableDirect setting for the Options parameter. Third, ensure the CursorLocation property has a setting of adUseServer. This is the default setting, so you don't need to assign the property a setting unless your application has changed its default setting. The following procedure demonstrates the first two steps. The third step is not necessary in this sample.

Sub SeekingUnshippedOrders()
Dim rst1 As ADODB.Recordset
 
'Instantiate and open recordset; Seek method requires
'index assignment, adCmdTableDirection Open option, and
'adUserServer setting for CursorLoction (default)
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Index = "ShippedDate"
rst1.Open "Orders", , , , adCmdTableDirect
 
'Print first order not shipped
rst1.Seek Null, adSeekFirstEQ
Debug.Print rst1("OrderID"), rst1("OrderDate")
 
'Print last order not shipped
rst1.Seek Null, adSeekLastEQ
Debug.Print rst1("OrderID"), rst1("OrderDate")
 
'Print all orders not shipped
rst1.Seek Null
Do
 Debug.Print rst1("OrderID"), rst1("OrderDate")
 rst1.MoveNext
Loop Until IsNull(rst1("ShippedDate")) = False
 
'Clean up objects
rst1.Close
Set rst1 = Nothing
 
End Sub

The first two instances of the Seek method in SeekingUnShippedOrders both demonstrate the syntax for the Keyvalues and SeekOptions parameters. The first instance of the Seek method sets the cursor at the first record in the Orders table that is unshipped. This record has the order ID 11008. The steps for opening the rst1 recordset on the Orders table arrange the records in order on the ShippedDate index, so that the special ordering indicated by the Seek method's Keyvalues and SeekOptions parameters will work. The second instance of the Seek method in the SeekingUnShippedOrders procedure sets the cursor at the last Orders table record with a Null value for the ShippedDate field. This record has the order ID 11077. The third instance of the Seek method in the preceding procedure searches for all unshipped orders without specifying a SeekOptions setting. Its following Do loop iterates through the reordered recordset to print the OrderID field and OrderDate field for each record with a Null ShippedDate field value.

The SeekWith2IndexValues procedure demonstrates the syntax for designating a seek for two criterion values: the OrderID and ProductID fields of the primary key for the Order Details table. The procedure also demonstrates how to reuse a Recordset object. In the first use of the rst1 recordset, the procedure searches for all line items matching an OrderID input by the user. The procedure uses an InputBox function nested inside a CInt function to permit a user to input an OrderID field value. It then returns the OrderID and ProductID for the line items associated with that record. This first seek relies on the OrderID index for the Order Details table.

Sub SeekWith2IndexValues()
Dim rst1 As ADODB.Recordset
Dim int1 As Integer
Dim int2 As Integer
Dim int3 As Integer
 
'Instantiate and open recordset; Seek method requires
'index assignment, adCmdTableDirection Open option, and
'adUserServer setting for CursorLocation (default)
Set rst1 = New ADODB.Recordset
rst1.ActiveConnection = _
 "Provider=Microsoft.Jet.OLEDB.4.0;" & _
 "Data Source=C:\PMA Samples\Northwind.mdb;"
rst1.CursorType = adOpenKeyset
rst1.LockType = adLockOptimistic
rst1.Index = "OrderID"
rst1.Open "Order Details", , , , adCmdTableDirect
 
'Search for a user-specified OrderID;
'save OrderID and ProductID of order's last line item
int1 = CInt(InputBox("Input OrderID: "))
rst1.Seek int1
Do Until rst1("OrderID") <> int1
 Debug.Print rst1("OrderID"), rst1("ProductID")
 rst1.MoveNext
 If rst1.EOF Then Exit Do
Loop
rst1.MovePrevious
int2 = rst1("OrderID")
int3 = rst1("ProductID")
 
'Close and reopen Order Details to seek
'last line item in the order
rst1.Close
rst1.Index = "PrimaryKey"
rst1.Open "Order Details", , , , adCmdTableDirect
 
rst1.Seek Array(int2, int3)
Debug.Print rst1("OrderID"), rst1("ProductID"), _
 FormatCurrency(rst1("UnitPrice")), _
 rst1("Quantity"), FormatPercent(rst1("Discount"), 0)
 
'Clean up objects
rst1.Close
Set rst1 = Nothing
 
End Sub

The second instance of the Seek method in the SeekWith2IndexValues procedure seeks the last item for the OrderID that a user input. At the conclusion of the first Seek instance, the procedure saves the OrderID and ProductID field values for the last line item in the order a user referenced. Then the procedure closes the recordset to assign a new index. The index is the primary key that relies on both OrderID and ProductID. Next the procedure reopens the recordset. The new index is critical for permitting the use of a compound criterion that includes values for both the OrderID and ProductID field values. Notice the use of the Array function for indicating more than one key value. The cursor points to the record in the recordset for the line item sought. The procedure prints it while formatting the UnitPrice and Discount columns.

An excerpt from

Programming Microsoft Access Version 2002

By Rick Dobson