VBA: Finding the record(s) in RecordSet

Test

Finding a Record Based on Multiple Criteria

1. Sub Find_WithFilter()
   
2.    Dim conn As ADODB.Connection
   
3.    Dim rst As ADODB.Recordset
   
4. 
   
5.    Set conn = New ADODB.Connection
   
6.    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & _
   
7.       "\mydb.mdb"
   
8. 
   
9.    Set rst = New ADODB.Recordset
   
10.    rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic
    
11.    rst.Filter = "TitleOfCourtesy ='Ms.' and Country ='USA'"
    
12.    Do Until rst.EOF
    
13.       Debug.Print rst.Fields("LastName").Value
    
14.       rst.MoveNext
    
15.    Loop
    
16. 
    
17.    rst.Close
    
18.    Set rst = Nothing
    
19.    conn.Close
    
20.    Set conn = Nothing
    
21. End Sub
    

复制代码

Finding Records Using the Find Method

1. Sub Find_WithFind()
   
2.    Dim conn As ADODB.Connection
   
3.    Dim rst As ADODB.Recordset
   
4. 
   
5.    Set conn = New ADODB.Connection
   
6.    conn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & CurrentProject.Path & "\mydb.mdb"
   
7.    Set rst = New ADODB.Recordset
   
8.    rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic
   
9. 
   
10.    rst.Find "TitleOfCourtesy ='Ms.'"
    
11.    Do Until rst.EOF
    
12.       Debug.Print rst.Fields("LastName").Value
    
13.       rst.Find "TitleOfCourtesy ='Ms.'", SkipRecords:=1, _
    
14.           SearchDirection:=adSearchForward
    
15.    Loop
    
16. 
    
17.    rst.Close
    
18.    Set rst = Nothing
    
19.    conn.Close
    
20.    Set conn = Nothing
    
21. End Sub

复制代码

Finding a Specific Record in a Recordset

1. Sub FindProject()
   
2.     Dim strSQL As String
   
3.     Dim rst As ADODB.Recordset
   
4.     Set rst = New ADODB.Recordset
   
5. 
   
6.     rst.ActiveConnection = CurrentProject.Connection
   
7.     rst.CursorType = adOpenStatic
   
8.     rst.Open "Select * from Employees"
   
9. 
   
10.     'Attempt to find a specific project
    
11.     strSQL = "[EmployeeID] = " & 1
    
12.     rst.Find strSQL
    
13. 
    
14.     'Determine if the specified project was found
    
15.     If rst.EOF Then
    
16.         msgBox lngValue & " Not Found"
    
17.     Else
    
18.         msgBox lngValue & " Found"
    
19.     End If
    
20.     rst.Close
    
21.     Set rst = Nothing
    
22. End Sub

复制代码

Finding the Record Position

1. Sub FindRecordPosition()
   
2.    Dim conn As ADODB.Connection
   
3.    Dim rst As ADODB.Recordset
   
4.    Dim strConn As String
   
5. 
   
6.    strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
   
7.       "Data Source=" & CurrentProject.Path & _
   
8.       "\mydb.mdb"
   
9. 
   
10.    Set conn = New ADODB.Connection
    
11.    conn.Open strConn
    
12. 
    
13.    Set rst = New ADODB.Recordset
    
14.    With rst
    
15.       .Open "Select * from Employees", conn, adOpenKeyset, _
    
16.           adLockOptimistic, adCmdText
    
17.    Debug.Print .AbsolutePosition
    
18.       .Move 3 ' move forward 3 records
    
19.       Debug.Print .AbsolutePosition
    
20.       .MoveLast ' move to the last record
    
21.       Debug.Print .AbsolutePosition
    
22.       Debug.Print .RecordCount
    
23.       .Close
    
24.    End With
    
25. 
    
26.    Set rst = Nothing
    
27.    conn.Close
    
28.    Set conn = Nothing
    
29. End Sub

复制代码

Change column data case

1. Sub exaRecordsetEdit()
   
2. 
   
3.     Dim db As Database
   
4.     Dim rs As Recordset
   
5.    
   
6.     Set db = CurrentDb
   
7.     Set rs = db.OpenRecordset("Employees")
   
8.    
   
9.     rs.MoveFirst
   
10.     Do While Not rs.EOF
    
11.        rs.Edit
    
12.        rs!Title = UCase$(rs!Title)
    
13.        rs.Update
    
14.        rs.MoveNext
    
15.     Loop
    
16.    
    
17.     rs.Close
    
18.    
    
19. End Sub

复制代码

SQL with where clause

1. Sub MyFirstConnection()
   
2.     Dim myConnection As ADODB.Connection
   
3.     Dim myRecordset As ADODB.Recordset
   
4.     Dim strSQL As String
   
5.     Dim strSearch As String
   
6.    
   
7.     strSearch = "Joe"
   
8.    
   
9.     strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer" & _
   
10.               " WHERE txtCustLastName = " & " '" & strSearch & "'"
    
11.    
    
12.     Set myConnection = CurrentProject.Connection
    
13.    
    
14.     Set myRecordset = New ADODB.Recordset
    
15.     myRecordset.Open strSQL, myConnection
    
16.    
    
17.     Do Until myRecordset.EOF
    
18.        Debug.Print myRecordset.Fields("txtCustFirstName"), _
    
19.                    myRecordset.Fields("txtCustLastName")
    
20.        myRecordset.MoveNext
    
21.     Loop
    
22.     myRecordset.Close
    
23.     myConnection.Close
    
24.     Set myConnection = Nothing
    
25.     Set myRecordset = Nothing
    
26. End Sub

复制代码

NoMatch property in Recordset

1. Sub SeekByPrice(curPrice As Currency)
   
2.   Dim db As Database
   
3.   Dim rec As Recordset
   
4.   Dim strSQL As String
   
5.   strSQL = "tblSales"
   
6.   Set db = CurrentDb()
   
7.   Set rec = db.OpenRecordset(strSQL)
   
8.   rec.Index = "AmountPaid"
   
9.   rec.Seek "=", curPrice
   
10.   
    
11.   If rec.NoMatch = True Then
    
12.     Debug.Print "No orders cost " & FormatCurrency(curPrice)
    
13.   Else
    
14.     Debug.Print "Order No. " & rec("SalesID") & " placed on " & _
    
15.          FormatDateTime(rec("DateOrdered"), vbLongDate) & _
    
16.          " cost " & FormatCurrency(rec("AmountPaid"))
    
17.   End If
    
18.   rec.Close
    
19. End Sub

复制代码