Bookmark, Bookmarkable Properties Example

This example prompts the user for a two-letter abbreviation for a state. The example uses this value to find matching records in the Customer recordset in the Nwindex.mdb database. It then marks each record with a bookmark and copies the values of the first and third fields to Sheet1.

To create the Nwindex.mdb database, run the Microsoft Excel example for the CreateDatabase method.

Dim Found(100)
i = 0
Set db = Workspaces(0).OpenDatabase(Application _
    .DefaultFilePath & "\NWINDEX.MDB")
Set rs = db.OpenRecordset("Customer")
Set theSheet = Sheets("Sheet1")
regionWanted = Application.InputBox( _
    "What state do you want data from?", _
    "Specify two letters (e.g. 'WA')", Type:=2)
If regionWanted = False Then    ' user cancelled InputBox
    Exit Sub
End If
criteria = "[REGION] = '" & regionWanted & "'"
rs.FindFirst criteria
If rs.NoMatch Or rs.Bookmarkable = False Then
    MsgBox "No records for this state"
    Exit Sub
Else
    Do Until rs.NoMatch = True
        i = i + 1
        Found(i) = rs.Bookmark
        rs.FindNext criteria
    Loop
End If
For n = 1 To i
    rs.Bookmark = Found(n)
    theSheet.Cells(n + 1, 1).Value = rs.Fields(0).Value
    theSheet.Cells(n + 1, 2).Value = rs.Fields(2).Value
Next
MsgBox "There are " & i & " records from this region"
rs.Close
db.Close