Microsoft KB Archive/109710

= ACC: How to Use Seek Method with Multiple-Field Index =

Article ID: 109710

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q109710





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

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 will fail.

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the "Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0



MORE INFORMATION
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.

In Microsoft Access 1.x, multiple-field indexes are named Index1, Index2, Index3, Index4, or Index5, corresponding to the two-field index created in the table. In Microsoft Access 2.0 and later, multiple-field index names can be customized. All versions of Microsoft Access refer to a multiple- field primary key index as PrimaryKey.

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

 Open the sample database Northwind.mdb (or NWIND.MDB in version 1.x or 2.0).  Create a new module and type the following procedure:

In Microsoft Access 2.0 and later: '********************************************************     ' Declarations section of the module '********************************************************

Option Compare Database Option Explicit

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

Function SeekOnMultiFields Dim db As 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 In Microsoft Access 1.x:      '******************************************************** ' Declarations section of the module '********************************************************

Option Compare Database Option Explicit

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

Function SeekOnMultiFields Dim db As Database, tbl As Table Set db = CurrentDB Set tbl = db.OpenTable("Order Details")

tbl.Index = "PrimaryKey" ' Or "Indexn" for other multi-field index

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 Debug Window (or Immediate window in version 1.x or 2.0) and then press ENTER:

? SeekOnMultiFields

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

