Microsoft KB Archive/210445

= ACC2000: How to Use the Seek Method with a Multiple-Field Index =

Article ID: 210445

Article Last Modified on 7/13/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q210445



Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access database (.mdb).



SUMMARY
This article shows you how to use the Seek method when your table's primary key or index includes more than one field. Note that when you use the Seek method on multiple fields, the Seek fields must be in the same order as the fields in the underlying table. If they are not, the Seek method fails.



MORE INFORMATION
NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

When you use the Seek method to find a record using the primary key, you need to supply a value for each field in the primary key. If you cannot supply values for all the fields in the primary key, use ">=" instead of "=" for the Comparison argument.

NOTE: "PrimaryKey" can refer to a multiple-field primary key. Multiple-field index names can be customized.

The following example demonstrates how to use the Seek method on a table with a multiple-field primary key.

 Start Microsoft Access and open the sample database Northwind.mdb.  Create a new module and type the following procedure: '******************************************************** ' Declarations section of the module '********************************************************

Option Compare Database Option Explicit

'************************************************** ' This function uses Seek on a two-field PrimaryKey '**************************************************

Function SeekOnMultiFields Dim db As DAO.Database, tbl As Recordset Set db = CurrentDB Set tbl = db.OpenRecordset("Order Details")

tbl.Index = "PrimaryKey" tbl.Seek "=", 10300, 68 ' If you are only supplying one value, the statement above ' becomes: tbl.Seek ">=", 10300

If tbl.NoMatch Then MsgBox "Not a record. Try another" Else MsgBox "The Record is in the table" End If  tbl.Close End Function

 Type the following line in the Immediate window, and then press ENTER:

? SeekOnMultiFields

Note that you receive the message "The Record is in the table."

