| The examples you've seen so far haven't done very much with the Recordset objects they've opened. To do something useful with the records you need to wield the Recordset object's properties and methods. You learn the most useful of these properties and methods in the next few sections. Getting at the Recordset Data In most cases, the point of opening a recordset is to get your hands on the data that's in a certain record. More specifically , you'll most often want to get whatever data is in a certain field within a record. You do that by invoking the Recordset object's Fields property: Recordset .Fields( FieldName ) | Recordset | The Recordset object you want to work with. | | FieldName | A string or String variable containing the name of the field that contains the data you want. | For example, if you're working with Northwind's Customers table, the following statement stores the data from the current record's ContactName field in a variable named currentContact : currentContact = rs.Fields("ContactName") Note, however, that Fields is the default property for a Recordset object, so you can save some typing by leaving out the .Fields part. In other words, the following two values are equivalent: rs.Fields("ContactName") rs("ContactName") Listing 10.4 shows another example. Listing 10.4. Getting Recordset Data Sub RecordsetData() Dim rs As Recordset Dim strSELECT As String ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' strSELECT = "SELECT * FROM Customers WHERE Country='Canada'" & _ "ORDER BY CompanyName;" rs.Open strSELECT, "Northwind", adOpenKeyset ' ' Display the Contact Name and Company Name ' MsgBox rs("ContactName") & ", " & rs("CompanyName") ' ' Close it ' rs.Close Set rs = Nothing End Sub caution  | When using a SELECT statement with a WHERE clause, there's always the possibility that the resulting recordset may contain no records. In that case, if your code attempts to access the data in the "current" record, an error will result. To avoid this, open the recordset using either the adOpenKeyset or adOpenStatic cursor types, and then check the Recordset object's RecordCount property. If this is greater than 0, it means the recordset has at least one record, and so it's safe to proceed. Here's a snippet that modifies part of Listing 10.4 to check for at least one record before displaying the data: rs.Open strSELECT, "Northwind", adOpenKeyset If rs.RecordCount > 0 Then MsgBox rs("ContactName") & ", " & rs("CompanyName") End If |
In this procedure, a recordset is opened using a SELECT statement that restricts the customers to just those where the Country field equals "Canada." Then a MsgBox function (see Chapter 12, "Interacting with the User ") displays the data from the ContactName and CompanyName fields. caution | | Note that the MoveLast and MovePrevious methods don't work if you open the recordset using the adOpenForwardOnly cursor. |
Navigating Records As I mentioned in the previous section, the Fields property returns the data from a field in the current record. When you first open a recordset, the current record is the first record. To get to another record, you need to navigate to it. There are a number of ways to do this, but the following four methods are the ones you'll probably use most often: Recordset .MoveFirst Moves to the first record in the specified Recordset object. Recordset .MoveLast Moves to the last record in the specified Recordset object. Recordset .MoveNext Moves to the next record in the specified Recordset object. Recordset .MovePrevious Moves to the previous record in the specified Recordset object. With these methods you're changing a value that points to the current record. This is all straightforward except for two situations: VBA will let you do these things, but in a sense they enable you to move "outside" of the recordset. If you try to access the data, you'll get an error message that begins Either BOF or EOF is True. Here, BOF means beginning of file and EOF means end of file . These are properties of the Recordset object: Recordset .BOF Returns True if the cursor is before the first record in the specified Recordset object. Recordset .EOF Returns True if the cursor is after the first record in the specified Recordset object. To avoid the error, you should use test properties in your code. For example, the following snippet runs the MoveNext method and then checks the EOF property. If it's True, then the cursor is moved to the last record: rs.MoveNext If rs.EOF Then rs.MoveLast End If Another way to move is to use the Recordset object's Move method, which moves the cursor a set number of records from the current record: Recordset .Move NumRecords, Start | Recordset | The Recordset object you want to work with. | | NumRecords | The number of records you want to move. Use a positive number to move toward the end of the recordset; use a negative number to move toward the beginning of the recordset. | | Start | Use this optional parameter to specify a starting record from which to perform the move. | The Start parameter should be the name of a Variant variable that contains a bookmark , which is a saved location in a recordset. You set and read bookmarks using the Recordset object's Bookmark property. Listing 10.5 provides an example. Listing 10.5. Navigating a Recordset Using a Bookmark Sub RecordsetBookmarkNavigation() Dim rs As Recordset Dim strSELECT As String Dim savedRecord As Variant ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' strSELECT = "SELECT * FROM Customers WHERE Country='USA'" & _ "ORDER BY CompanyName;" rs.Open strSELECT, "Northwind", adOpenKeyset ' ' Move, save the current record as a Bookmark, and display the data ' rs.Move 3 savedRecord = rs.Bookmark MsgBox rs("ContactName") & ", " & rs("CompanyName") ' ' Move the current record ' rs.Move -2 MsgBox rs("ContactName") & ", " & rs("CompanyName") ' ' Move relative to the Bookmark ' rs.Move 5, savedRecord MsgBox rs("ContactName") & ", " & rs("CompanyName") ' ' Move to the bookmark ' rs.Bookmark = savedRecord MsgBox rs("ContactName") & ", " & rs("CompanyName") ' ' Close it ' rs.Close Set rs = Nothing End Sub After the recordset is opened, the Move method is used to move forward three records, and then the current record is saved to the savedRecord variable using the Bookmark property. The code moves back two records and then moves forward five records from the bookmark. Finally the cursor is returned to the saved record by setting it as the value of the Bookmark property. Note that the following two statements do the same thing: rs.Bookmark = savedRecord rs.Move 0, savedRecord Finding a Record Another way to navigate a recordset is to search for a specific record using one or more criteria. ADO gives you two methods to use Find and Seek . However, you only need to learn how to use the Find method since it's simpler than Seek and works well on all but the largest recordsets. Here's the syntax for the Find method: Recordset .Find Criteria, SkipRows, SearchDirection, Start | Recordset | The Recordset object you want to work with. | | Criteria | An expression that specifies the criteria you want to use to find the record. | | SkipRows | An optional value that specifies the number of rows from the current record (or the record specified by the Start parameter) where the search should begin. The default value is 0. | | SearchDirection | An optional constant that specifies which direction the search should take. Use adSearchForward (the default value) to search forward through the records; use adSearchBackward to search backward. | | Start | An optional bookmark that specifies the starting record from which to perform the search. | When you run this method, one of two things will happen: -
A record is found that matches the criteria In this case, the cursor is moved to that record. -
No record is found that matches the criteria If the SearchDirection parameter is adSearchForward , the search stops at the end of the recordset (the EOF property returns True); if the SearchDirection parameter is adSearchBackward , the search stops at the beginning of the recordset (the BOF property returns True). This tells you that you can determine whether or not the search was successful by testing the EOF or BOF property (depending on the search direction) after running the Find method. Listing 10.6 gives an example. Listing 10.6. Using the Find Method Sub SearchRecordsWithFind() Dim rs As Recordset Dim strCriteria As String ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Employees" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .Open End With ' ' Run the Find method ' strCriteria = "City='London'" rs.Find strCriteria ' ' Loop to find other records that meet the criteria ' Do While Not rs. EOF ' ' Display the data ' MsgBox rs("FirstName") & " " & rs("LastName") ' ' Search again, but skip a row ' rs.Find strCriteria, 1 Loop ' ' Close the recordset ' rs.Close Set rs = Nothing End Sub After opening the Employees table as the recordset, this code uses the strCriteria variable to hold the criteria string "City='London'" . Then the Find method locates the first record that meets this criteria. A Do While...Loop is set up to loop as long as rs.EOF is False. Inside the loop, the employee's name is displayed and then the Find method is run again, although with the SkipRows parameter set to 1 to avoid finding the same record over and over again. Editing a Record Once you've navigated to or found the record you want, you may want to do more than just display the data or store the data in a variable or two. Instead, you may want to edit the data by making changes to one or more fields. Editing the current record is a two-step process: -
Change the data in one more fields. Changing the data is straightforward because you treat each field just like a variable: rs("Title") = "Account Manager" rs("UnitPrice") = 19.95 -
Update the record to write the new data to the table. You do this by running the Recordset object's Update method. Listing 10.7 puts these steps to work. Listing 10.7. Editing Recordset Data Sub EditingARecord() Dim rs As Recordset Dim strCriteria As String ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Employees" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .LockType = adLockPessimistic .Open End With ' ' Run the Find method ' strCriteria = "Title='Sales Representative'" rs.Find strCriteria ' ' Loop to find other records that meet the criteria ' Do While Not rs.EOF ' ' Display the data ' rs("Title") = "Account Manager" rs.Update MsgBox rs("FirstName") &" " & rs("LastName") & ", " & rs("Title") ' ' Search again, but skip a row ' rs.Find strCriteria, 1 Loop ' ' Close the recordset ' rs.Close Set rs = Nothing End Sub After opening the Employees table, the Find method is used to locate the first record where the Title field equals "Sales Representative." A Do While...Loop checks the EOF property. Inside the loop, the Title field is changed to "Account Manager" and the Update method finalizes the changes for the current record. The Find method is run again to continue the process. Adding a New Record If you have new information to insert into a table, ADO enables you to add a new record and populate its fields with the new data. This is accomplished with the Recordset object's AddNew method. caution | | To successfully add a new record to a table, you need to open the recordset with the LockType parameter or property set to either adLockOptimistic or adLockPessimistic . |
There are two ways to use AddNew . In the simplest case, you follow a three-step procedure: -
Run the AddNew method. -
Add the data to the new record's fields. -
Call the Update method to write the new record and data to the table. Listing 10.8 takes you through an example. Listing 10.8. Adding a New Record Sub AddingARecord() Dim rs As Recordset ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Customers" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open End With ' ' Create the new record ' rs.AddNew ' ' Enter the data for the new record ' rs("CustomerID") = "AYRSH" rs("CompanyName") = "Ayrshire Haggis" rs("ContactName") = "Angus Dunlop" rs("ContactTitle") = "Owner" rs("Address") = "123 Cathcart St." rs("City") = "Ayr" rs("Region") = "Ayrshire" rs("PostalCode") = "KA18 4PN" rs("Country") = "Scotland" rs("Phone") = "01290 555555" rs("Fax") = "01290 666666" ' ' Write the new record to the table ' rs.Update ' ' Close the recordset ' rs.Close Set rs = Nothing End Sub This code opens the Customers table (notice that LockType is set to adLockOptimistic ). Then the AddNew method is run and the various fields in the new record are populated with data. Finally, the Update method writes the new record to the table. The second way to use the AddNew method combines the first two steps into a single statement: Recordset .AddNew FieldList, Values | Recordset | The Recordset object you want to work with. | | FieldList | A field name or an array of field names . | | Values | A single value or an array of values for the fields in the new record. | Here's a statement that creates a new record and populates a single field: rs.AddNew "CustomerID", "AYRSH" Here's another that uses the Array function to populate an entire record in a single statement: rs.AddNew Array("CustomerID", "CompanyName", "ContactName", _ "ContactTitle", "Address", "City", "Region", _ "PostalCode", "Country", "Phone", "Fax"), _ Array("AYRSH", "Ayrshire Haggis", "Angus Dunlop", _ "Owner", "123 Cathcart St.", "Ayr", "Ayrshire", _ "KA18 4PN", "Scotland", "01290 555555", "01290 666666") Deleting a Record If a record is obsolete or simply no longer needed for some reason, you should delete it from the table to reduce clutter and keep the table up-to-date. This is handled easily by the Recordset object's Delete method, which marks the current record for deletion. You then run the Update method to confirm the deletion. tip  | If you run the Delete method and then decide against the deletion, you can back out of it by running the CancelUpdate method before running the Update method. |
Listing 10.9 puts the Delete method through its paces. Listing 10.9. Deleting a Record Sub DeletingARecord() Dim rs As Recordset Dim strCriteria As String ' ' Create the Recordset object ' Set rs = CreateObject("ADODB.Recordset") ' ' Open it ' With rs .Source = "Customers" .ActiveConnection = "Northwind" .CursorType = adOpenKeyset .LockType = adLockOptimistic .Open End With ' ' Run the Find method ' strCriteria = "CustomerID='AYRSH'" rs.Find strCriteria ' ' Loop to find other records that meet the criteria ' If Not rs.EOF Then rs.Delete rs.Update MsgBox "The customer with " & strCriteria & " has been deleted." Else MsgBox "The customer with " & strCriteria & " was not found!" End If ' ' Close the recordset ' rs.Close Set rs = Nothing End Sub After opening the recordset (again, notice that you need to set LockType to either adLockOptimistic or adLockPessimistic ), the Find method is used to locate the record to be deleted. If the record is found (that is, the recordset's EOF property is False), the code runs the Delete method followed by the Update method. A message tells the user that the record has been deleted. | 