Navigating Recordsets with VBA

Recordsets are the foundation for all Access data-related operations. The VCR-style navigation buttons of datasheets and bound forms manipulate the record pointer of the underlying Recordset. The following sections show how you emulate Access's navigation buttons with VBA code and use loops to automate processing of an entire Recordset.

Generating the Temporary Recordset

You often use temporary Recordsets for specific purposes, such as populating a list or combo box. When the Recordset operation completes, you close it, releasing its connection to the underlying table or query and freeing its memory resources. It's a common practice to create temporary Recordsets when opening a form that contains the unbound controls that rely on Recordsets for their data.

Perform the following steps to a create a temporary Recordset with VBA:

1.	Create a working copy of Northwind.mdb by compacting it to another file, such as Nwind.mdb, if you haven't done so already. (Using a working copy prevents permanent changes to Northwind.mdb when you update Recordset objects later in this chapter.) ◂◂ See Compacting Databases
2.	Create a new unbound form in Design view. (Don't specify a Record Source for the form.)
3.	Click the Code button to display the Class Module for the form and add the following code to the Declarations section to create three module-level variables, immediately below Option Explicit: Private dbCurrent As Database Private rsTemp As Recordset Private strSQL As String
4.	Select Form in the Object list to create the Form_Load event-handling stub.
5.	Add this code to the Form_Load event-handler to create a Recordset based on the Record Source property value that loaded the cboProducts combo box in the preceding chapter: Set dbCurrent = CurrentDb strSQL = "SELECT ProductID, ProductName FROM Products;" Set rsTemp = dbCurrent.OpenRecordset(strSQL) Stop ◂◂ See Adding the Query Combo Boxes to the Form The Stop statement is temporary; without the Stop statement, the variable values go out of scope when End Sub executes.
6.	Click the Form view button to execute the code you added to the Form_Load stub. The code-editing window appears with a yellow arrow in the left margin pointing to the breakpoint generated by the Stop statement (see Figure 30.1). Figure 30.1. Creating a code breakpoint with the Stop statement.
7.	Press Ctrl+G to open the Debug Window and enter ? rsTemp.RecordCount to verify that your code returned rows. At this point, any value other than 0 indicates rows returned. You also can test other Recordset property values, such as Fields.Count and Type. The default Recordset Type is a Dynaset (see Figure 30.2). Figure 30.2. Testing properties of the rsTemp Recordset object.

Applying Move… Methods

The five Move… methods—Move, MoveFirst, MoveLast, MoveNext, and MovePrevious—are the basic positional commands for the record pointer of Recordsets. Introduced in DAO 3.5, only Move takes an argument—the number of rows to move relative to the current row, as in Move 10.

You can't obtain an accurate RecordCount value (other than 0) for a Recordset until you apply the MoveLast method. To get the right value of RecordCount and try the Move… methods, do the following:

1.	Minimize the Debug Window and add the following lines before the Stop command: rsTemp.MoveLast rsTemp.MoveFirst
2.	Press F5 to continue execution past the Stop breakpoint.
3.	Click the form to give it the focus; then click the View button twice (to change to Design view and then Form view) to rerun the Form_Load event-handler.
4.	Press Ctrl+G, clear the prior statements in the Debug Window, and type ? rsTemp.Recordcount to return the correct value, 77.
5.	The value of the ProductID column conveniently returns the position of the current row. Type ? rsTemp.Fields(0) to verify that MoveFirst returned the record pointer to the first record.
6.	Type Move 10 and then ? rsTemp.Fields(0) to test the Move method.

If you attempt to move by an increment greater than the number of remaining rows, you don't receive an error message. If you try to read the record, however, you receive a No Current Record error.

Using the EOF and BOF Properties in Loops

The EOF (end of file) and BOF (beginning of file) Recordset properties have their roots in dBASE and other file-oriented RDBMSs. The EOF property returns True when you attempt to move beyond the last row of the Recordset; an example is applying a Move 100 instruction to a Recordset with 77 rows. Similarly, BOF returns True if you try a Move -100 operation on the Recordset (see Figure 30.3). EOF and BOF both return True for an empty Recordset (RowCount = 0).

The most common use of the EOF and BOF property values is traversing an entire Recordset within loop structures, such as the following:

rsName.MoveFirst
Do Until rsName.EOF
   'Recordset operation code
   rsName.MoveNext
Loop
						

or

rsName.MoveLast
Do While Not rsName.BOF
   'Recordset operation code
   rsName.MovePrevious
Loop
						

To test a loop with the Debug.Print method, do the following:

1.	Press F5, delete the rsTemp.MoveLast line, and add the following code after the rsTemp.MoveFirst statement: Do Until rsTemp.EOF Debug.Print rsTemp.Fields(0), rsTemp.Fields(1) rsTemp.MoveNext Loop
2.	Click the form to give it the focus, and then click the View button twice to rerun the Form_Load event-handler.
3.	Press Ctrl+G to display the last few rows of the Recordset in the Debug Window (see Figure 30.4). Figure 30.4. The Debug Window displaying the last few rows of a Recordset iterated by a loop structure.

Using the AbsolutePosition and PercentPostion Properties

The AbsolutePosition property of a Recordset is similar to, but not identical to, the record count (RECNO()) function and GOTO command of xBase. AbsolutePosition returns a zero-based Long integer indicating the current position of the record pointer. You can set the value of AbsolutePostion to move the record pointer to a specific position in the Recordset. These operations are reliable for a static Recordset of the Snapshot type. In a multiuser environment, however, uses may delete or add rows to dynamic-type Recordsets, so repeated execution of rsName.AbsolutePosition = 50 might not return the same row. To make sure that you return to a specific record, use the Bookmark property (one of the subjects of the next section).

The PercentPosition property sets or returns the location of the record pointer based on a value of 0 representing the first and 100 representing the last record of the Recordset. Figure 30.5 shows typical Debug Window operations using AbsolutePosition and PercentPosition.

Using the Find… Methods and Bookmarks

The four Find… methods—FindFirst, FindLast, FindNext, and FindPrevious—execute a query on the Recordset and position the record pointer at the designated row, if such a row exists. The Find… methods don't work with Table-type Recordsets, which require the Seek method. If the sought row exists, the NoMatch property returns False; otherwise, NoMatch returns True.

◂◂ See "Moving to a Specific Record in a Recordset Object,"p.1003

The argument of the find method must be a valid SQL WHERE clause without the WHERE reserved word. For example, to find all rows that begin with Queso, execute rsTemp.FindFirst "ProductName Like 'Queso*'". Then repeatedly execute rsTemp.FindNext "ProductName Like 'Queso*'" until rsTemp. NoMatch returns True (see Figure 30.6).

Jet uses the Bookmark property to identify a specific row in a Recordset. A Jet Bookmark is a binary value, which you access with a String variable. To mark a row to which you want to return later, use a strName = rsName.Bookmark instruction. To reposition the record point to the row, execute rsName.Bookmark = strName (see Figure 30.7). If you Bookmark a row in a Recordset and another user deletes the row, you receive a runtime error.