HandsOn Using Bookmarks to Filter Records

1. In the Visual Basic Editor window, choose Insert | Module.

2. In the module's Code window, type the Filter_WithBookmark procedure shown below.

3. Choose Run | Run Sub/UserForm to execute the procedure.

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 & _ "\Northwind.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

Creating and Manipulating Databases with ADO

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

0 0

Post a comment