Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Use the Seek Method with a Multiple-Field Index

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

View products that this article applies to.

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).

↑ Back to the top

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.

↑ Back to the top

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.
  1. Start Microsoft Access and open the sample database Northwind.mdb.
  2. 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
  3. 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."

↑ Back to the top

References

For additional information about using the Seek method in an ADO recordset based on a table in a Microsoft Jet database, click the article number below to view the article in the Microsoft Knowledge Base:
243465� ACC2000: How to Use the Seek Method with ActiveX Data Objects (ADO) Against a Jet Recordset

↑ Back to the top

Keywords: KB210445, kbprogramming, kbhowto

↑ Back to the top

Article Info
Article ID : 210445
Revision : 4
Created on : 7/13/2004
Published on : 7/13/2004
Exists online : False
Views : 315