ASCII table

Looking for Specific Records

In the last lesson, you found out how to go to a specific record by moving around in Access. But there are occasions when you do not know which record to go to, but you do know information that you want to locate. In that situation, you will find that the Seek and Find methods are more useful.
Finding Records in Table-Type Recordsets

The quickest way to find a record in a table-type recordset object is to use the Seek method.
One of the important processes involved in designing a database is to determine how the tables within the database are to be indexed. If you search on an indexed field, Access is able to find records much more quickly. Also, Access can perform operations, such as joins and sorts, much faster if the fields which are being joined or sorted are indexed. One down-side of indexes is that they add an overhead to the length of time it takes Access to update records, so they should not be overused.
As a programmer you can take advantage of the extra speed provided by indexes if you use the Seek method. This allows you to perform a fast search on an indexed field.

Using Seek is a two-step process.

Example

1. Open the Bottling table in design view and select the Price field.
2. In field properties, add a non-unique index to the field by changing it Indexed property to Yes (Duplicates OK)
3. Now switch to datasheet view, save the table design changes. Sort the records by Price. This should be fast as the Price field is now indexed and, in any case, there are not too many records in the table.
4. Scroll down to the table to the three bottles that have the price of 18.99, and make a note of the BottlingId field values for these three. Close the table saving changes.
5. Create a new procedure in the module you have been using, and type in the following code:
   
   Public Sub SeekPrice(curPrice As Currency)
   Dim db As Database
   Dim rec As Recordset
   Dim strSQL As String
   Dim strMsg As String
   
   strSQL = "Bottling"
   
   Set db = CurrentDb()
   Set rec = db.OpenRecordset(strSQL)
   
   rec.Index = "Price"
   rec.Seek "=", curPrice
   
   strMsg = "Bottling No. " & rec("BottlingID") & " costs " & Format$(rec("Price"), _
   "Currency")
   
   MsgBox strMsg
   rec.Close
   
   End Sub
   
   
6. Open the debug window and type SeekPrice(18.99) into the lower pane of the window, press Enter.
7. A message box should appear telling you the first of the bottles that sell for that price.
   

Explaining the Code

1. The line Public Sub SeekPrice(curPrice As Currency) defines the procedure name and also says that this procedure expects a value to be passed to it when it is called.
2. The line rec.Index = "Price" specifies which index to use. The name should be a valid Index name, to check all indexes, open the table in design view and click on the indexes button. If you try to use an index name that does not exist, Access will generate a run-time error.
3. The line rec.Seek "=", curPrice this uses the Seek method. The seek method requires two parameters, the first being the type of comparison. These can be any of the comparison operators enclosed in quotes. The second parameter is the value to use in the comparison.
4. The line strMsg = "Bottling No. " & rec("BottlingID") & " costs " & Format$(rec("Price"), "Currency") stores the result of the search in a string variable that is used in a message box prompt. The Seek method returns the first record that matches the criterion.
   

What happens if there are no matches?

The example above assumes that the Seek is going to find something matching the criteria. But what if the value that is searched for is not in the recordset? Well lets see…

1. Run the SeekPrice() procedure again, but put a value of 3.64 as the price. Access should give you a run time error that there is no current record. To figure out what line caused the error, press the Debug button.
2. Access displays the code window with the offending line of code highlighted. The line is the one that stores the string into strMsg. The reason for the error is not that Seek could not find a matching record. After doing the seek, it does not know which record to make the current record, so when you try to do an operation that needs the current record, Access does not know which record to use, so it returns an error.
3. So, obviously you need a mechanism to determine whether or not the Seek method found a record, and then display the result if we know it was successful
4. Change the SeekPrice procedure so that it now has the following code changes typed in bold, are included.
   
   rec.Seek "=", curPrice
   
   If rec.NoMatch = True Then
       strMsg = "No bottlings cost " & Format$(curPrice, "Currency")
   Else
   
   strMsg = "Bottling No. " & rec("BottlingID") & " costs " & Format$(rec("Price"), _
   "Currency")
   End If
   
   MsgBox strMsg
   
5. The NoMatch property of a recordset object is set to True when the Seek method (or Find method) fails to locate a record.
6. Run the procedure again using the value 3.64 again. This time, you should get the message that no bottlings cost that amount.

Finding Records in Dynasets and Snapshots

The Seek method is very fast, but it can only be used on indexed fields and on table-type recordsets.
If we want to find records in dynaset or snapshot type recordset objects, or in non-indexed table type recordset objects, we must use one of the Find methods. There are four of these and their uses are described below:

If the Find method fails to locate a record that matches it acts the same as the Seek method.

The syntax of the. Find method is somewhat different to that of the Seek method, as we need to specify the field we are searching on, as well as the value we are looking for. For example, if we wanted to use the FindFirst method to find the first record with an OrderDate after Jan. 1, 95, we would write this:

rec.FindFirst "OrderDate > #01-01-95#"

Notice that the entire statement is enclosed in quotes!

For Example

1. Insert a new procedure and add the following code (cut and paste it!):
   
   Sub FindBottleByPrice(curPrice As Currency)
   
   Dim db As Database
   Dim rec As Recordset
   Dim strSQL As String
   Dim strMatches As String
   Dim intCounter As Integer
   
   strSQL = "Bottling"
   
   Set db = CurrentDb()
   Set rec = db.OpenRecordset(strSQL, dbOpenSnapShot)
   
   rec.FindFirst "Price = " & curPrice
   Do While rec.NoMatch = False
       intCounter = intCounter +1
       strMatches = strMatches & Chr$(10) & rec ("BottlingID")
       rec.FindNext "Price = " & curPrice
   Loop
   
   Select Case intCounter
       Case 0
           MsgBox "No bottlings cost " & Format$(curPrice,"Currency")
       Case 1
           MsgBox "The following bottling cost " & Format$(curPrice,"Currency") _
               & " : " & Chr$(10) & strMatches
       Case Else
           MsgBox "The following " & intCounter & " bottlings cost " & _
               Format$(curPrice,"Currency") & " : " & Chr$(10) & strMatches
   End Select
   
   rec.Close
   
   End Sub
   
   
2. Open the debug window and run the procedure using the price 3.64
3. Run it again using the price 16.49
4. Run it again using the price 18.99