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