Seek Method Example (MDB)

The following example creates a new Index object on an Employees table. The new index consists of two fields, LastName and FirstName. The procedure then uses the Seek method to find a specified record.

Sub NewIndex()
    Dim dbs As Database, tdf As TableDef, idx As Index
    Dim fldLastName As Field, fldFirstName As Field, _
        rst As Recordset
    
    ' Return reference to current database.
    Set dbs = CurrentDb
    ' Return reference to Employees table.
    Set tdf = dbs.TableDefs!Employees
    ' Return Index object that points to new index.
    Set idx = tdf.CreateIndex("FullName")
    ' Create and append index fields.
    Set fldLastName = idx.CreateField("LastName", dbText)
    Set fldFirstName = idx.CreateField("FirstName", dbText)
    idx.Fields.Append fldLastName
    idx.Fields.Append fldFirstName
    ' Append Index object.
    tdf.Indexes.Append idx
    tdf.Indexes.Refresh
    ' Open table-type Recordset object.
    Set rst = dbs.OpenRecordset("Employees")
    ' Set current index to new index.
    rst.Index = idx.Name
    ' Specify record to find.
    rst.Seek "=", "Fuller", "Andrew"
    If rst.NoMatch Then
        Debug.Print "Seek unsuccessful!"
    Else
        Debug.Print "Seek successful."
    End If
    rst.Close
    Set dbs = Nothing
End Sub