Seek Method Example

This example demonstrates the Seek method by allowing the user to search for a product based on an ID number.

Sub SeekX()

   Dim dbsNorthwind As Database
   Dim rstProducts As Recordset
   Dim intFirst As Integer
   Dim intLast As Integer
   Dim strMessage As String
   Dim strSeek As String
   Dim varBookmark As Variant

   Set dbsNorthwind = OpenDatabase("Northwind.mdb")
   ' You must open a table-type Recordset to use an index, 
   ' and hence the Seek method.
   Set rstProducts = _
      dbsNorthwind.OpenRecordset("Products", dbOpenTable)

   With rstProducts
      ' Set the index.
      .Index = "PrimaryKey"

      ' Get the lowest and highest product IDs.
      .MoveLast
      intLast = !ProductID
      .MoveFirst
      intFirst = !ProductID

      Do While True
         ' Display current record information and ask user 
         ' for ID number.
         strMessage = "Product ID: " & !ProductID & vbCr & _
            "Name: " & !ProductName & vbCr & vbCr & _
            "Enter a product ID between " & intFirst & _
            " and " & intLast & "."
         strSeek = InputBox(strMessage)

         If strSeek = "" Then Exit Do

         ' Store current bookmark in case the Seek fails.
         varBookmark = .Bookmark

         .Seek "=", Val(strSeek)

         ' Return to the current record if the Seek fails.
         If .NoMatch Then
            MsgBox "ID not found!"
            .Bookmark = varBookmark
         End If
      Loop

      .Close
   End With

   dbsNorthwind.Close

End Sub