Using Bookmarks to Filter Records : Recordset Bookmark « Access « VBA / Excel / Access / Word

1. VBA / Excel / Access / Word
2. Access
3. Recordset Bookmark

Using Bookmarks to Filter Records

 
Sub Filter_WithBookmark()
   Dim rst As ADODB.Recordset
   Dim varMyBkmrk() As Variant
   Dim strConn As String
   Dim i As Integer
   Dim strCountry As String
   Dim strCity As String

   i = 0
   strCountry = "France"
   strCity = "Paris"

   strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"

   Set rst = New ADODB.Recordset
   rst.Open "Customers", strConn, adOpenKeyset

   If Not rst.Supports(adBookmark) Then
      MsgBox "This recordset does not support bookmarks!"
      Exit Sub
   End If

   Do While Not rst.EOF
      If rst.Fields("Country") = strCountry And _
         rst.Fields("City") = strCity Then
         ReDim Preserve varMyBkmrk(i)
         varMyBkmrk(i) = rst.Bookmark
         i = i + 1
      End If
      rst.MoveNext

   Loop

   rst.Filter = varMyBkmrk()

   rst.MoveFirst
   Do While Not rst.EOF
      Debug.Print rst("CustomerId") & _
         " - " & rst("CompanyName")
      rst.MoveNext
   Loop
   rst.Close
   Set rst = Nothing
End Sub

 

Related examples in the same category