Moving Through a Recordset

After you retrieve data using an ADO Recordset object, you'll want to manipulate that data in some way, even if all you want to do is view the data. A lot of what you learn in this section you can also use to find, add, delete, and edit existing data.

Moving through a Recordset actually requires setting the current record pointer within the cursor (the retrieved data). In other words, you point to a specific record making it the current record pointer. To move about the Recordset, use these five methods:

• MoveFirst Moves the record pointer to the first record in the Recordset.

• MoveNext Moves the record pointer forward to the next record.

• MovePrevious Moves the record pointer backward to the previous record.

• MoveLast Moves the record pointer to the last record in the Recordset.

• Move Moves the record pointer a specific number of records from the current record.

The last method needs a bit more explanation because it supports two arguments

 Move x[, start] 

where x is a Long value that specifies the number of records to move and start is a String or Variant value that evaluates to a bookmark using one of the enumerate values listed in Table 17.1.

Table 17.1. BookmarkEnum

Constant	Integer Value	Explanation
adBookmarkCurrent	0	Starts at the current record
adBookmarkFirst	1	Starts at the first record
adBookmarkLast	2	Starts at the last record

When x is greater than zero, the record pointer moves forward; when x is less than zero, the record pointer moves backward. Specifying a negative x value is the only way you can move backward in a forward-only Recordset. However, this method is unpredictable because not all providers support this option. If you omit start, the move is relative to the current record.

You can't get anywhere in a Recordset if you get stuck in the spot before the first record or after the last record. When looping through records, you need to check for these two placeholders using the BOF and EOF Boolean properties. The BOF (beginning of file) property equals True when the record pointer moves to the spot just before the first record. Similarly, the EOF (end of file) equals True when the record pointer moves to the spot just after the last record.

To illustrate how important these two properties are, take a look at what happens when you ignore them. The following procedure retrieves data from the Clients table and then prints the client name for each record to the Immediate window:

 Sub PrintClients()   'Print client name to Immediate window   Dim rst As ADODB.Recordset   Set rst = New ADODB.Recordset   rst.Open "Clients", CurrentProject.Connection   rst.MoveFirst   Do     Debug.Print rst(1)     rst.MoveNext   Loop   rst.Close   Set rst = Nothing End Sub 

You can enter this procedure into a standard module or use Chapter 17's example module. With the cursor anywhere inside the procedure, press F5.

The loop prints the client names in the Immediate window, but when the cursor finally moves beyond the last record, the code returns the error shown in Figure 17.1 because there's nothing beyond the placeholder to print. Click End to clear the error message.

Figure 17.1. Avoid errors by knowing where the cursor is.

In this case, the solution is simple. Replace the Do statement with the following:

 Do Until rst.EOF 

The new Do Until statement checks for the EOF property before executing the loop code. The rst.EOF is True when the cursor is at the end of the file placeholder; that's when the loop stops.

Just how you check for either or both properties depends on what you're doing. You can wrap your task code in a Do Until loop as in this example or you can use an If statement. In addition, you might need to check for both the BOF and the EOF properties. This time you didn't because the loop stops after the loop's condition equals True.

TIP

Use the EOF and BOF properties to check for an empty Recordset. Both properties return True at the same time when the Recordset contains no records. For instance, the combined condition rst.EOF AND rst.BOF returns True when rst is empty.

Also notice that this example uses two of the methods reviewed earlier: MoveFirst and MoveNext. The MoveFirst method just before the Do Until loop ensures that you begin the loop at the first record, so you don't need to check the BOF property. After executing the loop's code task, the MoveNext method moves the record pointer forward to the next record, before the loop repeats its task code.

CAUTION

If you write a loop and your code seems to hang forever, check to make sure that you remembered to put a MoveNext call into the loop.

CAUTION

Keep the cursor type in mind when trying to move through a Recordset object because the cursor type determines the direction you can move. For instance, in a forward-only cursor, you can't move backward using the MovePrevious method. For more information about cursor types, read "Understanding the Different Types of Recordsets" in Chapter 16.

Referencing Recordset Fields

A Recordset object contains a Fields collection and there's one Field object for each column of data. There are a couple of ways to interact with the actual data. First, you use the Field object using the form

 recordset.Fields(fieldname).Value 

where recordset represents the Recordset object and fieldname is the column name. For instance, the following statement returns the data in the Client field for the current record:

 rst.Fields("Client").Value 

Alternatively, you can also use the Fields collection index value. If you know the Client column is the second column in the Recordset, the following statement gets the same results:

 rst(1) 

When using the index value, remember that the left-most column's index value equals 0. The second column from the left is 1, the third column from the left is 2, and so on.

TIP

There's nothing wrong with using the index value to interact with data in a Recordset, but keep two issues in mind. First, that index value can change if the structure of the underlying data source changes. For instance, you might add or delete a column. In addition, although the Fields collection method is longer, it is more readable, making it easier to maintain.