HandsOn Finding Records Using the Find Method

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

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

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

Sub Find_WithFind()

Dim conn As ADODB.Connection Dim rst As ADODB.Recordset

Set conn = New ADODB.Connection conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\Northwind.mdb"

Creating and Manipulating Databases with ADO

Set rst = New ADODB.Recordset rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic

' find the first record matching the criteria rst.Find "TitleOfCourtesy ='Ms.'" Do Until rst.EOF

Debug.Print rst.Fields("LastName").Value ' search forward starting from the next record rst.Find "TitleOfCourtesy ='Ms.'", SkipRecords:=1, _ SearchDirection:=adSearchForward

Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing End Sub

To find the last record, call the MoveLast method before using Find. If none of the records meets the criteria, the current record is positioned before the beginning of the recordset (if searching forward) or after the end of the recordset (if searching backward). You can use the EOF or BOF properties of the Recordset object to determine whether a matching record was found.

The ADO Find method does not support the Is operator. To locate the record that has a Null value, use the equals (=) operator. For example:

' find records that do not have an entry in the ReportsTo field rst.Find "ReportsTo = Null"

' find records that have data in the ReportsTo field rst.Find " ReportsTo <> Null"

To find records based on more than one condition, use the Filter property of the Recordset object, as demonstrated in Hands-On 13-12 later in this chapter.

0 0

Post a comment