VBA/Excel/Access/Word/Access/Recordset Find
Содержание
- 1 Change column data case
- 2 Finding a Record Based on Multiple Criteria
- 3 Finding a Specific Record in a Recordset
- 4 Finding Records Using the Find Method
- 5 Finding the Record Position
- 6 Find record by using Recordset.FindFirst
- 7 NoMatch property in Recordset
- 8 Select specific column in select statement
- 9 Simple Select statement
- 10 SQL with where clause
Change column data case
<source lang="vb">
Sub exaRecordsetEdit()
Dim db As Database Dim rs As Recordset Set db = CurrentDb Set rs = db.OpenRecordset("Employees") rs.MoveFirst Do While Not rs.EOF rs.Edit rs!Title = UCase$(rs!Title) rs.Update rs.MoveNext Loop rs.Close
End Sub
</source>
Finding a Record Based on Multiple Criteria
<source lang="vb">
Sub Find_WithFilter()
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 & _ "\mydb.mdb" Set rst = New ADODB.Recordset rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic rst.Filter = "TitleOfCourtesy ="Ms." and Country ="USA"" Do Until rst.EOF Debug.Print rst.Fields("LastName").Value rst.MoveNext Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Finding a Specific Record in a Recordset
<source lang="vb">
Sub FindProject()
Dim strSQL As String Dim rst As ADODB.Recordset Set rst = New ADODB.Recordset rst.ActiveConnection = CurrentProject.Connection rst.CursorType = adOpenStatic rst.Open "Select * from Employees" "Attempt to find a specific project strSQL = "[EmployeeID] = " & 1 rst.Find strSQL "Determine if the specified project was found If rst.EOF Then msgBox lngValue & " Not Found" Else msgBox lngValue & " Found" End If rst.Close Set rst = Nothing
End Sub
</source>
Finding Records Using the Find Method
<source lang="vb">
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 & "\mydb.mdb" Set rst = New ADODB.Recordset rst.Open "Employees", conn, adOpenKeyset, adLockOptimistic rst.Find "TitleOfCourtesy ="Ms."" Do Until rst.EOF Debug.Print rst.Fields("LastName").Value rst.Find "TitleOfCourtesy ="Ms."", SkipRecords:=1, _ SearchDirection:=adSearchForward Loop rst.Close Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Finding the Record Position
<source lang="vb">
Sub FindRecordPosition()
Dim conn As ADODB.Connection Dim rst As ADODB.Recordset Dim strConn As String strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & CurrentProject.Path & _ "\mydb.mdb" Set conn = New ADODB.Connection conn.Open strConn Set rst = New ADODB.Recordset With rst .Open "Select * from Employees", conn, adOpenKeyset, _ adLockOptimistic, adCmdText Debug.Print .AbsolutePosition .Move 3 " move forward 3 records Debug.Print .AbsolutePosition .MoveLast " move to the last record Debug.Print .AbsolutePosition Debug.Print .RecordCount .Close End With Set rst = Nothing conn.Close Set conn = Nothing
End Sub
</source>
Find record by using Recordset.FindFirst
<source lang="vb">
Sub findrecorder()
Dim dbNorthwind As DAO.Database Dim dbPath As String DbPath = CurrentProject.Path & "mydb.mdb" Set dbNorthwind = OpenDatabase(dbPath) Dim rsEmployees As DAO.Recordset Dim rsCustomers As DAO.Recordset Set rsEmployees = dbNorthwind.OpenRecordset("Employees", dbOpenTable) Set rsCustomers = dbNorthwind.OpenRecordset("Customers", dbOpenTable) rsCustomers.MoveLast numCustomers = rsCustomers.RecordCount With rsEmployees .FindFirst "City = "Seattle"" If .NoMatch Then MsgBox ("No Records Found!") .MoveFirst Else MsgBox ("Found "& .Fields(2).Value & " "& .Fields(1).Value & _ "in Seattle") End If End With
End Sub
</source>
NoMatch property in Recordset
<source lang="vb">
Sub SeekByPrice(curPrice As Currency)
Dim db As Database Dim rec As Recordset Dim strSQL As String strSQL = "tblSales" Set db = CurrentDb() Set rec = db.OpenRecordset(strSQL) rec.Index = "AmountPaid" rec.Seek "=", curPrice If rec.NoMatch = True Then Debug.Print "No orders cost " & FormatCurrency(curPrice) Else Debug.Print "Order No. " & rec("SalesID") & " placed on " & _ FormatDateTime(rec("DateOrdered"), vbLongDate) & _ " cost " & FormatCurrency(rec("AmountPaid")) End If rec.Close
End Sub
</source>
Select specific column in select statement
<source lang="vb">
Sub MyFirstConnection()
Dim myConnection As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strSQL As String strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer" Set myConnection = CurrentProject.Connection Set myRecordset = New ADODB.Recordset myRecordset.Open strSQL, myConnection Do Until myRecordset.EOF Debug.Print myRecordset.Fields("txtCustFirstName") & " " & _ myRecordset.Fields("txtCustLastName") myRecordset.MoveNext Loop myRecordset.Close myConnection.Close Set myConnection = Nothing Set myRecordset = Nothing
End Sub
</source>
Simple Select statement
<source lang="vb">
Sub MyFirstConnection()
Dim myConnection As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strSQL As String strSQL = "SELECT * FROM tblCustomer ORDER BY txtCustLastName" Set myConnection = CurrentProject.Connection Set myRecordset = New ADODB.Recordset myRecordset.Open strSQL, myConnection Do Until myRecordset.EOF Debug.Print myRecordset.Fields("txtCustFirstName"), _ myRecordset.Fields("txtCustLastName") myRecordset.MoveNext Loop myRecordset.Close myConnection.Close Set myConnection = Nothing Set myRecordset = Nothing
End Sub
</source>
SQL with where clause
<source lang="vb">
Sub MyFirstConnection()
Dim myConnection As ADODB.Connection Dim myRecordset As ADODB.Recordset Dim strSQL As String Dim strSearch As String strSearch = "Joe" strSQL = "SELECT txtCustFirstName, txtCustLastName FROM tblCustomer" & _ " WHERE txtCustLastName = " & " "" & strSearch & """ Set myConnection = CurrentProject.Connection Set myRecordset = New ADODB.Recordset myRecordset.Open strSQL, myConnection Do Until myRecordset.EOF Debug.Print myRecordset.Fields("txtCustFirstName"), _ myRecordset.Fields("txtCustLastName") myRecordset.MoveNext Loop myRecordset.Close myConnection.Close Set myConnection = Nothing Set myRecordset = Nothing
End Sub
</source>