Working with Recordsets | Beginning Access 2002 VBA (Programmer to Programmer)

So far, we have only really looked at how to create Recordset objects and ensure that the data in them is up to date. But, of course, what we normally want to do is to look at the data itself.

To refer to individual fields within a Recordset object, you can use a variety of different methods . We'll create a subprocedure that opens a dynaset-type Recordset object based on the tblIceCream table.

Try It Out-Looking at Values in a Recordset

In IceCream.mdb , in the same module you created earlier in this chapter, insert a procedure called OpenIceCreamRecordset :

   Sub OpenIceCreamRecordset()     Dim db As Database     Dim rec As Recordset     Set db = CurrentDb()     Set rec = db.OpenRecordset("tblIceCream")     rec.Close     End Sub

Now place a Stop command after the OpenRecordset command. This has the effect of suspending execution of the code (see Chapter 12 for more on this):
```
 Set rec = db.OpenRecordset("tblIceCream")   Stop   rec.Close 
```
Run the procedure, either by hitting F5 , by selecting Run Sub/ User Form from the Run menu, hitting the Run Sub/User Form button on the toolbar, or typing the name of the procedure in the Immediate window and hitting the Enter key. When the line containing the Stop command is reached, execution of the code should pause and the line should be highlighted. We can now use the Immediate window to inspect the records in the recordset.
Make sure the Immediate window and Locals window are both visible. The Locals window should look like this. (If you have named your module differently, you will see a reference to the name you have given the module, rather than to Chapter 6 Code ).
Now type the following in the Immediate window:
```
   ? rec(0), rec(1), rec(2)   
```
Hit the Enter key and the value of the first three fields for the first record in the tblIceCream table should be displayed:
Now go to the Locals window and find the variable called rec . If you click on the plus sign to the left of the variable's name, the tree will expand to display the properties of the rec variable. There are quite a few of them!
If you expand the entry for the Fields collection by clicking the plus button to its left, you will see it has five items (because there are five fields in the Recordset represented by the variable rec ):
Expand Item 2, again by clicking the plus button to its left. You should see the properties of that particular Field object:
Note the values of the CollectionIndex , Name , Size, and Value properties. From this you should be able to determine that the index of this field in the Fields collection of the Recordset is 1 (remember, these collections are zero-based ); the name of the field is IceCream ; the maximum length of values in this field is 50 characters ; and the value of this field in the current record is Walnut Wonder .
Finally, return to the code window and hit either F5 or the Run Sub/User Form button on the toolbar. This allows the procedure to run from where it is (at the Stop statement) to the end of the procedure and close the Recordset object.

How It Works

Whenever you create a Recordset object, the first row of the recordset becomes the current record. As we created a table-type Recordset object, the records are ordered according to the primary key ( IceCreamID ) and so the current row is the record containing data for the ice cream called Walnut Wonder . We can then examine the value of any of the fields in this record. In this example, we inspected the values of the first three fields of that record, the IceCreamID , IceCream, and Description fields respectively.

At this point, you should note that the order of records in a query may not always be what you expect. If a query is based on a single table and you have not chosen to sort the records in some other way when you designed the query, then records in the query will normally be displayed in primary key order, or in their original insertion order if there is no primary key.

However, if your query contains a criterion for a field that is not the primary key, the records will usually be displayed in insertion order (that is, the order in which they were entered in the table). In fact, the rules for deciding in what order Access displays the records are even more complex, particularly when the query is based on more than one table. Suffice to say that you cannot rely on the records in a query being sorted in any particular order unless you have explicitly requested one.

Important

If you want the records in a query to be sorted, you should specify a sort criterion (or an ORDER BY clause) when you design the query.If you have not specified any sort criteria, you should not rely on the records in the result set being in any particular order.

Examining Field Values

To look at the values of individual fields within the current record, we can use any number of conventions that we could with other objects within collections. They are as follows :

General Syntax	Example
RecordsetName!FieldName	rec!IceCreamID
RecordsetName("FieldName")	rec("IceCreamID")
RecordsetName(FieldIndex)	rec(0)
RecordsetName.Fields(FieldIndex)	Rec.Fields(0)

When using the RecordsetName(FieldIndex) syntax to refer to fields in a Recordset object, you should remember that Access will always give the first field an index of rather than 1 , irrespective of any Option Base setting you may have stated. So, rec(2) refers to the third field in the Recordset object, rec , not the second.

Moving Through Recordsets

So far, however, things have been rather static. We are able to open a Recordset object and inspect all the values in the current record, but what if we want to move around the recordset? Suppose we wanted to move to the next record down and look at the values in that? Well, it's simple enough. If, after our Stop statement, we had applied the MoveNext method to the Recordset object in the last example by typing this into the Immediate window:

   rec.MoveNext

and then checked the value of the IceCream field, we would have found that it was Strawberry Surprise :

Moving around a recordset is really very simple. The methods that you can use are:

Method	Description
MoveNext	Makes the next record the current record
MovePrevious	Makes the previous record the current record
MoveFirst	Makes the first record the current record
MoveLast	Makes the last record the current record
Move n	Makes the record n records away from the current record ( rec.Move 3 or rec.Move -2 )

This is all very well, but we are still faced with the problem that we encountered back in the first couple of chapters with our "unintelligent" navigation buttons . If we wanted to print the value of the IceCream field for every record in our recordset, we could keep on using the MoveNext method and then print the value of IceCream , but at some point we'll hit the last record. Access will let us try another MoveNext , but when we try to print the value of IceCream , we'll get a warning message telling us that there is no current record:

In Chapter 2, we had the same problem. We added logic to the buttons on the frmCompany form to enable us to move to the first, next, last, or previous records or to the blank record at the end of the recordset that is used for inserting a new record, but there was nothing to stop us trying to move beyond this, in which case we also got an error message.

To make the form more user friendly, we decided to disable certain buttons, depending on where we were in the recordset. In Chapter 3, we started to implement this feature and wrote code to disable the Next button when we were on the new blank record at the end of the table. Well, it's taken a while to get here, but now we can look at the code that looks after all the buttons.

We're in a position to deal with the problem for all of the buttons, because we have a set of methods at our disposal that allow us to test where we are in a recordset and actually move to the last record or first record in one straightforward action. Once we've executed these methods, we can then take appropriate action to disable the correct buttons. Before we move on to the actual code, however, let's make sure that we understand exactly when we want the navigation buttons to be enabled or disabled.

Position	First	Previous	Next	Last	New
First Record	Disabled	Disabled	Enabled	Enabled	Enabled
Intermediate Records	Enabled	Enabled	Enabled	Enabled	Enabled
Last Record	Enabled	Enabled	Disabled	Disabled	Enabled
New (Blank) Record	Enabled	Enabled	Disabled	Enabled	Enabled

Looking at the table above, we can see that when we are on the first record of the form we want the Previous button to be disabled, while the rest of the buttons will be enabled. On the last record of the form we want the Next button to be disabled. On a blank, new record we would want to have both the Previous and Next buttons disabled.

We also need to consider what will happen if the form does not allow new records to be added. By disabling the Next button on the last record, we prevent the user from using the Next button to try to move from the last record into the blank, new record. So we are covered in that situation. However, we will also need to make sure that the New button is disabled if the form does not allow new records to be added.

Finally, we will need to make sure that all of the buttons except for the New button are disabled if there are no records at all in the form (or rather in the underlying recordset).

Now that we have sorted out the rules of engagement, we can go into battle

Try It Out-Creating Intelligent Navigation Buttons

If you haven't already done so, open up the IceCream.mdb database and switch to the VBA IDE by hitting Alt + F11 .
Double-click the Form_frmCompany class module and locate the Current event. This should contain the code we wrote in Chapter 3.

Locate the Private Sub Form_Current() subprocedure, and replace it with the code given below:

   Private Sub Form_Current()     Dim recClone As Recordset     'Make a clone of the recordset underlying the form so     'we can move around that without affecting the form's     'recordset     Set recClone = Me.RecordsetClone()     'If we are in a new record, disable the <Next> button     'and enable the rest of the buttons     If Me.NewRecord Then     cmdFirst.Enabled = True     cmdPrevious.Enabled = True     cmdNext.Enabled = False     cmdLast.Enabled = True     cmdNew.Enabled = True     Exit Sub     End If     'If we reach here, we know we are not in a new record     'so we can enable the <New> button if the form allows     'new records to be added     cmdNew.Enabled = Me.AllowAdditions     'But we need to check if there are no records. If so,     'we disable all buttons except for the <New> button     If recClone.RecordCount = 0 Then     cmdFirst.Enabled = False     cmdNext.Enabled = False     cmdPrevious.Enabled = False     cmdLast.Enabled = False     Else         'If there are records, we know that the <First> and     '<Last> buttons will always be enabled, irrespective     'of where we are in the recordset     cmdFirst.Enabled = True     cmdLast.Enabled = True         'Synchronize the current pointer in the two recordsets     recClone.Bookmark = Me.Bookmark     'Next, we must see if we are on the first record     'If so, we should disable the <Previous> button     recClone.MovePrevious     cmdPrevious.Enabled = Not (recClone.BOF)     recClone.MoveNext     'And then check whether we are on the last record     'If so, we should disable the <Next> button     recClone.MoveNext     cmdNext.Enabled = Not (recClone.EOF)     recClone.MovePrevious     End If     'And finally close the cloned recordset     recClone.Close     End Sub

Now check that the code compiles by selecting Compile IceCream from the Debug menu.
Save the changes to the module and switch back to Access by hitting Alt + F11 .
Close the frmCompany form (which should be in Design View ) and open it up in Form view. Try moving through the records - you'll see that you now have intelligent navigation buttons:

How It Works

The code is not complicated but there are a few new things, so let's have a look at it in detail.

 Set recClone = Me.RecordsetClone()

The first thing we did was to create a duplicate copy of the form's Recordset object, using RecordsetClone() . This is an alternative to using the OpenRecordset method to create a Recordset object. Using the RecordsetClone() method against the form to create a separate copy of the recordset means that we can navigate or manipulate a form's records independently of the form itself. This is desirable, as we are going to want to move around the recordset behind the scenes and don't want our maneuvers to be reflected in the form itself. Instead, we are able to use a separate, cloned, read-only Recordset object that acts just as if it had been created using the OpenRecordset method.

 If Me.NewRecord Then

The first condition that we check for is whether or not we are in a new record. The simplest way to do this is to check the NewRecord property of the form. If we are in a new record, we disable the Next and New buttons and then use the Exit Sub to exit the procedure without executing any more code.

 cmdNew.Enabled = Me.AllowAdditions

We then need to work out whether the form allows new records to be added. Again we can determine this by inspecting a property of the form - in this case, the AllowAdditions property. If this returns True we want to enable the New button; if it returns False we want to disable the New button. In other words, the Enabled property of the New button should be set to the same as the AllowAdditions property of the form.

 If recClone.RecordCount = 0 Then

The next step is to check whether there are any records behind the form. It is often easy to forget to make this check but, if we try to move around a Recordset object with no records, Access will generate an error and cause our code to break. The easiest way to determine whether there are any records is to inspect the cloned Recordset object's RecordCount property. This will tell us the number of records in the recordset. If it is equal to zero, there are no records in the recordset and the only button that should be enabled is the New button.

It's worth mentioning here that the RecordCount property of some types of Recordset object is not always immediately available. You might need to move to the last record in the recordset to update it. However, here the form is based on a single table and so the form's recordset is a table-type Recordset object that doesn't suffer from this problem. You'll see more of this in a moment.

So, by now we have determined that we are not in a new record and that there is more than one record in the recordset. We can therefore enable the First and Last buttons. Once we have done that, we need to work out where in the recordset we are - at the top, the bottom, or somewhere in the middle?

Before we can do this, we need to make sure that the current record in our cloned Recordset object is the same as the current record in the form. Whenever you create a Recordset object, the first record in that Recordset object becomes the current record. However, our procedure is called from the form's Current event (not only when the form is opened, but also whenever the user moves to a different record). When a clone is created, it doesn't have a current record. So, we need some sort of mechanism to set the current record in the cloned Recordset object to match that on the form. We can do this with a Bookmark (we'll discuss these later in the chapter):

 recClone.Bookmark = Me.Bookmark

A Bookmark is simply way of identifying each individual row in a recordset. This is what Access uses in place of record numbers . A Bookmark consists of a Byte array. We shall be looking at them in a little more detail later on in this chapter when we discuss how we can find specific records in a recordset. For the moment, however, all we are concerned with is ensuring that the cloned Recordset object and the form are in sync. By assigning to the Bookmark property of the cloned Recordset object the same value as the Bookmark property of the form, we ensure that the clone has the same current record as the one the user can see displayed on the form.

So now it is time to work out where the current record is in the recordset. If the current record is the first record, we must disable the Previous button. If the current record is the last record, we must disable the Next button. To determine whether the current record is at an extremity of the recordset, we use the BOF and EOF properties. BOF stands for B eginning O f F ile and EOF stands for E nd O f F ile.

The BOF property of a Recordset object is True if the current record pointer is placed immediately before the first record, and the EOF property is True if the current record pointer is placed immediately after the last record. Consequently, if we attempt to move to the record previous to the current one and we find that the recordset's BOF property is True , we know that the current record is the first record in the recordset:

 recClone.MovePrevious cmdPrevious.Enabled = Not (recClone.BOF) recClone.MoveNext

If this code seems a little hard to fathom at first, just remember that the BOF and EOF properties return a True or False value. If recClone.BOF returns True , we're at the beginning of a recordset and we need to disable the cmdPrevious button by setting its Enabled property to False . We use the NOT operator to simply reverse the Boolean value returned by the recClone.BOF expression. If False is returned, however, it means we're not at the beginning of a recordset, so we want the cmdPrevious button to be enabled by having a True value placed in its Enabled property.

If we had wanted to, we could also have expressed this with the following less succinct If ... Then structure.

 recClone.MovePrevious   If recClone.BOF = True Then     cmdPrevious.Enabled = False     Else     cmdPrevious.Enabled = True     End If   recClone.MoveNext

Similarly, if we attempt to move to the record after the current record and we find that the EOF property of the cloned Recordset object is True , we know that the current record is the last record in the recordset.

 recClone.MoveNext cmdNext.Enabled = Not (recClone.EOF) recClone.MovePrevious

And that's about it. We just close the cloned recordset and the code is complete.

Counting Records in a Recordset

In the last example, we used the RecordCount property of a Recordset object to determine how many records it contained. The behavior of the RecordCount property is, in fact, a little more complex than we let on, and depends on the type of recordset in question.

Table-Type Recordsets

When you open a table-type recordset, Access knows the number of records in the table, and so the RecordCount property of the recordset is instantly set to that number.

Dynasets and Snapshots

In order to increase the performance of your code when creating dynaset-type Recordset objects and snapshot-type Recordset objects, Access doesn't fully populate the recordset until navigating to the end of the table or query. Therefore, Access does not always immediately know the number of records in these types of Recordset object. In order to force Access to calculate the number of records in a dynaset-type Recordset object or in a snapshot-type Recordset object, you have to use the MoveLast method of the Recordset object.

   Set rec = db.OpenRecordset("qryTotalOrders", dbOpenDynaset)     rec.MoveLast     Debug.Print rec.RecordCount

This forces Access to fetch all the rows in the recordset before continuing, and so enables it to determine the precise number of rows in the recordset.

If you only want to know whether there are any records in the recordset, as opposed to finding out how many, you do not need to use a MoveLast method. When you use the OpenRecordset method and the recordset is not empty, Access waits until the first record has been returned before executing the next line of code. In other words, if the RecordCount property of a recordset is equal to zero, there are definitely no more rows to be returned.

If you add or delete records in a dynaset-type Recordset object, the RecordCount property of the object increases or decreases accordingly . However, if other users add or delete records in the underlying tables, these changes are not reflected until the Recordset object is requeried (using the Requery method). Again, you will need to use the MoveLast method after the Recordset object has been requeried, to ensure that the RecordCount property is accurate.

AbsolutePosition and PercentPosition

The record counting behavior has implications for two other recordset properties. The AbsolutePosition property returns the position of the current record in the recordset relative to 0. When using the AbsolutePosition property, bear these factors in mind:

If there is no current record, the AbsolutePosition property returns -1 .
It is by no means certain that records will always appear in the same order every time a recordset is opened unless a sort criterion ( ORDER BY clause) has been specified.
Remember that the AbsolutePosition of a record will change as records are inserted or deleted. For this reason, do not be tempted to use the AbsolutePosition property instead of a bookmark.

The PercentPosition property indicates the absolute position of the current record as a percentage of the total number of records that is returned by the RecordCount property. With regard to accuracy of the values returned, the same considerations apply to the PercentPosition property as to the RecordCount property. In order to ensure that the PercentPosition property returns an accurate figure, you should use the MoveLast method after opening or requerying Recordset objects, and before inspecting the PercentPosition property.

The AbsolutePosition and PercentPosition properties only apply to dynasets and snapshots. Trying to use them against table-type Recordset objects will result in a run-time error.

The following procedure can be used to display the record count and the absolute and percent positions returned by Access in the Ice Cream database.

   Sub ShowPositions()     Dim db As Database     Dim rec As Recordset     Set db = CurrentDb()     Set rec = db.OpenRecordset("tblIceCream", dbOpenDynaset)     Debug.Print "Records", "Absolute", "Percent"     Do While Not rec.EOF     Debug.Print rec.RecordCount, rec.AbsolutePosition, rec.PercentPosition     rec.MoveNext     Loop     rec.Close     End Sub

If you run this procedure in the Ice Cream database, you will see that it creates a dynaset-type Recordset object based on the tblIceCream table and then loops through it, one record at a time. For each record, it displays the RecordCount , AbsolutePosition, and PercentPosition properties of the recordset. The output it would print into the Immediate window would look like this:

There are three things in particular that are worth noting in this procedure:

Firstly, you can see that the RecordCount property returns the wrong value the first time around.
Secondly, the AbsolutePosition property is zero-based and so returns for the first record, 1 for the second record, and so on.
And finally, take a good look at the Do While...Loop structure seen here. This technique is commonly used in procedures that need to loop through every record in a recordset.

Looking for Specific Records

So far, we have only concerned ourselves with moving through a Recordset object using the various Move methods. But there may be occasions when you know exactly which record you wish to find. In that situation, you will find that the Seek and Find methods are more suited to your task.

Finding Records in Table-Type Recordsets

The quickest way to find a record in a table-type Recordset object is to use the Seek method.

One of the important processes involved in designing a database is to determine how the tables within the database are to be indexed. If you search on an indexed field, Access is able to find records much more quickly. Also, Access can perform operations, such as joins and sorts, much faster if the fields which are being joined or sorted are indexed. Bear in mind, however, that one downside of indexes is that they add an overhead to the length of time it takes Access to update records (as the index needs to be updated in addition to the data) so they should only be used where they will provide a measurable improvement in performance.

As a programmer, you can take advantage of the extra speed provided by indexes if you use the Seek method. This allows you to perform a fast search on an indexed field. Using Seek is a two-step process:

First select the indexed field that you wish to search on
Then specify the criteria for finding the record

As an example, we'll search the tblSales table for sales that cost a certain amount.

Try It Out-Using the Seek Method

Open the tblSales table in design view and select the AmountPaid field.
If there is not one already, add a non-unique index to the field by changing its Indexed property to Yes (Duplicates OK) .
Now switch to Datasheet view, save the table design (if prompted), and sort the records by AmountPaid . This should be fast as the AmountPaid field is now indexed and, in any case, there are not too many records in the table.
Look for orders that cost the same amount, for instance, orders with a SalesID of 120 and 357 both cost $210.00 . Make a note of the SalesID , DatePaid and AmountPaid values. We shall be using these in a moment.
Close down the table, saving your changes as you do so.

Now create a new procedure in the Chapter 6 module you created earlier in the chapter and type in the following code:

   Sub SeekByPrice(curPrice As Currency)     Dim db As Database     Dim rec As Recordset     Dim strSQL As String     Dim strMsg As String     strSQL = "tblSales"     Set db = CurrentDb()     Set rec = db.OpenRecordset(strSQL)     rec.Index = "AmountPaid"     rec.Seek "=", curPrice     strMsg = "Order No. " & rec("SalesID") & " placed on " & _     FormatDateTime(rec("DateOrdered"), vbLongDate) & _     " cost " & FormatCurrency(rec("AmountPaid"))     MsgBox strMsg     rec.Close     End Sub

Run the code by typing SeekByPrice 210 in the Immediate window and hitting the Enter key.
A message box appears telling you the first order it has found with the price of $210.00.

How It Works

This example makes use of the Index property and Seek method to locate the required record in the table.

 Set rec = db.OpenRecordset(strSQL)

The first thing we do is to create a table-type Recordset object. Note that we did not need to explicitly request that the Recordset object should be a table-type Recordset object as it is based on a single local Access table; table-type is the default type for Recordset objects created from local Access tables.

 rec.Index = "AmountPaid"

The next step is to specify the index that we want to use when seeking the required record. When setting the Index property of the Recordset object, you should use the name of the index as it appears in the Indexes window of the table in Design view (you can view this by pressing the Indexes button on the toolbar). If you try to set the Index property of a Recordset object to an index that does not exist, Access will generate a run-time error.

 rec.Seek "=", curPrice

Once we have chosen an index, we are ready to look for the record we require. We do this using the Seek method. When using Seek , we need to specify two arguments. The first indicates the type of comparison we want to carry out and the second indicates the value we want to compare against the index.

In our example, we want to find records for which the value of the indexed field is equal to 210 , so the type of comparison is an equality comparison and the value we are comparing against the index is 210 . The following list shows the type of comparisons that can be carried out using the Seek method:

Comparison argument	Has this effect...
"="	Finds the first record whose indexed field is equal to the value specified
" > "	Finds the first record whose indexed field is greater than the value specified
" > ="	Finds the first record whose indexed field is greater than or equal to the value specified
" < "	Finds the first record whose indexed field is less than the value specified
" < ="	Finds the first record whose indexed field is less than or equal to the value specified

Note that the comparison argument is enclosed in quotes. If you prefer, you can specify a string variable - or a variant variable of type vbString - in place of the string literal. In other words, we could have written our code like this:

 strComparison = "=" rec.Seek strComparison, curPrice

However, the important thing to remember is that the comparison argument must be a valid string expression.

 strMsg = "Order No. " & rec("SalesID") & " placed on " & _   FormatDateTime(rec("DateOrdered"), vbLongDate) & _   " cost " & FormatCurrency(rec("AmountPaid"))

Once the Seek method has found a record matching the criterion we set, we display the result in a dialog box.

Remember that there was more than one record that matched our criterion; the Seek method returns the first match it finds.

Try It Out-Allowing for No Matches

The above example assumes that Seek is going to be successful in finding a matching record. What happens, though, if this isn't the case?

Run the SeekByPrice procedure again, but this time pass it as an argument a value which you know will have no matching records, such as 3.64 . The result of this is that the code breaks and Access displays a dialog box telling you that there is no current record.

In order to work out how to solve this problem, we must first determine which line of our code caused the error to happen.
Hit the Debug button. Access displays the code window with the offending line of code highlighted:

As you can see, the line that caused the error to occur was the one in which we attempted to display the dialog box informing the user of the matching record. The code did not break simply because there was no record which matched. In fact, it broke because when the Seek method fails to find a matching record, it doesn't know which record to make the current record and leaves the current record in an indeterminate state. So, when you subsequently try to perform an operation that requires the current record to be known, Access does not know which record is the current record, and displays an error message.

What we need, therefore, is some mechanism that allows us to determine whether or not the Seek method found a record, so that we only attempt to display the result if we know it was successful.
Stop the code from executing, by either hitting the Reset button or selecting Reset from the Run menu.

Modify the SeekByPrice procedure so that it now looks like this:

 ...   If rec.NoMatch = True Then     strMsg = "No orders cost " & FormatCurrency(curPrice)     Else   strMsg = "Order No. " & rec("SalesID") & " placed on " & _        FormatDateTime(rec("DateOrdered"), vbLongDate) & _        " cost " & FormatCurrency(rec("AmountPaid"))   End If   ...

The NoMatch property of a Recordset object is set to True when the Seek method (or any of the Find methods discussed below) fails to locate a record.

Now run the procedure from the Immediate window, and pass 3.64 again. This time, you get a message box telling you what has happened :

This is a much more friendly way of doing things!

As we've explained, one problem of failed Seek or Find (see below) operations is that if no matching record is found, the current record is left in an indeterminate state. We will look at how to deal with this situation a little later in this chapter when we look at the Bookmark property.

Finding Records in Dynasets and Snapshots

Using the Seek method is a very quick way of finding records, but it has a couple of notable limitations:

It can only be used on indexed columns , which means that
It can only be used against table-type recordsets

If we want to find records in dynaset- or snapshot-type Recordset objects, or in non-indexed fields of table-type Recordset objects, we must use one of the Find methods. There are four of these and their uses are described below:

This method...	Works like this...
FindFirst	Starts at the beginning of the recordset and searches downwards until it finds a record which matches the selected criteria and makes that record the current record.
FindLast	Starts at the end of the recordset and searches upwards until it finds a record which matches the selected criteria and makes that record the current record.
FindNext	Starts at the current record and searches downwards until it finds a record which matches the selected criteria and makes that record the current record.
FindPrevious	Starts at the current record and searches upwards until it finds a record which matches the selected criteria and makes that record the current record.

As with the Seek method, if any of the Find methods fail to find a record matching the specified criterion, the current record is left in an indeterminate state. This means that if you then try to perform any operation that requires the current record to be known, Access will generate a run-time error.

The syntax of the Find methods is somewhat different from that of the Seek method, as we need to specify the field we are searching on, as well as the value we are looking for. For example, if we had opened a snapshot-type Recordset object based on the tblSales table and wanted to use the FindFirst method to find the first with a DateOrdered after 10 ^th July 2002, we would write this:

 rec.FindFirst "DateOrdered > #07/10/2002#"

The argument we supply for a Find method is just the WHERE clause of a SQL statement, but without the WHERE in front.

It's quite intuitive really - the only thing you need to remember is that the criteria must be enclosed in quotes.

As with the Seek method, we could use a string variable to specify the criteria:

   strCriterion = "DateOrdered > #07/10/2002#"     rec.FindFirst strCriterion

Try It Out-Using the Find Methods

Let's try rewriting the last example using the FindFirst and FindNext methods.

Insert a new procedure and add the following code:

   Sub FindByPrice(curPrice As Currency)     Dim db As Database     Dim rec As Recordset     Dim strSQL As String     Dim strMatches As String     Dim intCounter As Integer     strSQL = "tblSales"     Set db = CurrentDb()     Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)     rec.FindFirst "AmountPaid = " & curPrice     Do While rec.NoMatch = False     intCounter = intCounter + 1     strMatches = (strMatches & vbCrLf) & rec("SalesID")     rec.FindNext "AmountPaid = " & curPrice     Loop     Select Case intCounter     Case 0     MsgBox "No orders cost " & FormatCurrency(curPrice)     Case 1     MsgBox "The following order cost " & _     FormatCurrency(curPrice) & " : " & _     vbCrLf & strMatches     Case Else     MsgBox "The following " & intCounter & " orders cost " & _     FormatCurrency(curPrice) & " : " & _     vbCrLf & strMatches     End Select     rec.Close     End Sub

Open the Immediate window and run the procedure, using the price 3.64 . There are no matching records and the following dialog box is displayed:
Now run it again, but this time pass 77 as the argument, for which there is one match.
Finally, run the procedure again and pass a price for which there are several matches, like 210 .

How It Works

The main difference in this portion of code is the method we use to find the matching records:

 rec.FindFirst "AmountPaid = " & curPrice

We start by looking for the first record with a price matching the one entered:

 Do While rec.NoMatch = False  intCounter = intCounter + 1  strMatches = strMatches & Chr$(10) & rec("SalesID")  rec.FindNext "AmountPaid = " & curPrice Loop

If there is no order with this price, rec.NoMatch is True and so the subsequent Do...Loop structure is not entered. However, if a matching order is found, rec.NoMatch is False and we enter the loop.

Once inside the loop, three things happen. First we increment a counter to indicate how many matches have been made; then we build up a string using the linefeed character Chr$(10) (which causes a new line to be created) and the SalesID of the matching record; and finally we have a look to see if there is another record which matches our criterion.

If there is, we return to the start of the loop and, as rec.NoMatch is False , we run through the whole process again.

When there are no more matches found, rec.NoMatch is True and the loop terminates. Then, all that is left is to display the results in a message box.

   Select Case intCounter     Case 0     MsgBox "No orders cost " & FormatCurrency(curPrice)     Case 1     MsgBox "The following order cost " & _     FormatCurrency(curPrice) & " : " & _     Chr$(10) & strMatches     Case Else     MsgBox "The following " & intCounter & " orders cost " & _     FormatCurrency(curPrice) & " : " & _     Chr$(10) & strMatches     End Select

intCounter contains a count of the number of times we went through the loop, and, therefore, how many matches were found.

Notes on Formatting Dates and Currency Amounts in VBA

Among the most frequent types of data that you will search for in Recordset objects are dates and monetary amounts. For example, you may want to find orders placed on a certain day or worth a certain amount. If you do so, you need to be aware of how VBA handles date and currency formats, especially if you are working through this book somewhere other than in the United States.

VBA will format date and currency outputs according to the settings you make in the Regional Settings section of the Control Panel . No problem there.

You may, for example, have your computer set up to display dates in the British format (so that the 31 ^st of October, 2002 is displayed as 31/10/02 ). This isn't a problem for VBA when formatting date or currency output - if VBA encounters a date of 31-Oct-02 and your Short Date Style is set to dd/mm/yy , VBA will display the date as 31/10/02 . Just what you want.

However, VBA operates slightly differently when requesting date input . The locale of VBA is always English (United States) irrespective of the way that you have configured the Regional Settings in your Control Panel . As a result, if you enter a date in either a SQL statement or as in VBA, it will be interpreted as if it were in the format mm/dd/yy , that is, in US format.

To ensure that all of the dates you enter will be interpreted correctly, it is best to explicitly convert all dates to US format before using them in SQL statements or in VBA. To convert a date to US format, you would replace a statement like this:

   rec.FindFirst "OrderDate = " & dtOrder

with one like this:

   rec.FindFirst "OrderDate = #" & Format(dtOrder, "mm/dd/yy") & "#"

When entering currency values, similar considerations apply. VBA expects currency values in 9.99 format - even if the currency separator defined in Control Panel is something other than a period.

Important

The problem of non-US date and currency formats only exists when you are dealing with dates and monetary amounts in VBA. In forms and in the query designer, you can enter dates and currency amounts in your own local format, and Access will convert them for you.

When to Use Find and Seek

So far, we have looked at how to use the Seek and Find methods to locate records in a recordset. However, in all of the examples, our task could have been completed more quickly by opening a Recordset object based on an SQL string which defined our search criteria. Don't worry if you don't know much about Structured Query Language, the SQL in this example is very simple and we will be looking at SQL in more detail in the next chapter few chapters.

   strSQL = "SELECT * FROM tblSales WHERE AmountPaid = " & curPrice

It simply assigns to the string strSQL an SQL statement which selects every field (using the * symbol) for each record of the tblSales table where the AmountPaid is equal to the price held in the variable curPrice . So, if we add this to our code, the FindByPrice() procedure could be rewritten like this:

   Sub FindByPrice2(curPrice As Currency)     Dim db As Database     Dim rec As Recordset     Dim strSQL As String     Dim strMatches As String     Dim intCounter As Integer     strSQL = "SELECT * FROM tblSales WHERE AmountPaid = " & curPrice     Set db = CurrentDb()     Set rec = db.OpenRecordset(strSQL, dbOpenSnapshot)     Do Until rec.EOF     strMatches = strMatches & Chr$(10) & rec!SalesID     rec.MoveNext     Loop     intCounter = rec.RecordCount     Select Case intCounter     Case 0     MsgBox "No orders cost " & FormatCurrency(curPrice)     Case 1     MsgBox "The following order cost " & _     FormatCurrency(curPrice) & " : " & _     Chr$(10) & strMatches     Case Else     MsgBox "The following " & intCounter & " orders cost " & _     FormatCurrency(curPrice) & " : " & _     Chr$(10) & strMatches     End Select     rec.Close     End Sub

The difference in speed between executing FindByPrice() and FindByPrice2() could be particularly noticeable if you run the procedures against attached tables in a remote ODBC server such as SQL Server over a Local Area Network or particularly over a Wide Area Network.

The reason for this difference in speed is that, in the first example, we are opening a Recordset object that contains the entire contents of the tblSales table. All of these records would have to be read from disk on the remote computer, sent across the network and then read into cache locally - although, if it was a dynaset-type Recordset object, only the keys from each row are cached. Then we would have to search through all of the records for the few that meet our criteria.

In the second example, however, we are opening a Recordset object that contains only as many rows as there are matching records. This will be much more efficient and will result in considerably less network traffic, as only two or three rows will need to be retrieved.

Although the difference in speed might go unnoticed for relatively small tables, for other larger tables with many thousands of records the difference could be very great indeed.

For this reason, it is wiser to restrict the use of the Find methods to local tables, and to use SQL WHERE clauses in queries against attached tables in ODBC databases. If performance against local tables is still a problem, check whether the field you are searching on is (or can be) indexed, and use the Seek method instead.

Bookmarks

Earlier in the chapter, we used the Bookmark property to synchronize the current records in two Recordset objects that were clones of each other. The Bookmark property of a recordset is stored internally by Access as an array of bytes which uniquely identifies the current record. When you reference the Bookmark property in VBA code, however, you should always assign it to a String or Variant variable:

   Dim strBookmark As String     strBookmark = rec.Bookmark

or:

   Dim varBookmark As Variant     varBookmark = rec.Bookmark

Note that you can only use the Bookmark property to synchronize current records in Recordset objects that are clones of each other. If the Recordset objects have been created separately - even if they are based on the same query or SQL - the bookmarks of individual records may not match.

You can also use bookmarks to help you to return to records that you have already visited. This is done by storing the Bookmark property of the recordset in a variable when you are on a specific record, and then setting the Bookmark property of the recordset to that value when you want to return to that record. This is especially useful when used in conjunction with Seek or Find operations. Remember, if you are using Find or Seek and a matching record cannot be found, the current record will be left in an indeterminate state. So it makes sense to store the Bookmark property of the recordset before the operation and then reassigning this value to the Bookmark property of the recordset if the Find or Seek operation fails.

Our code would then look like this:

 Dim strBookmark As String   strBookmark = rec.Bookmark   rec.FindFirst "DateOrdered > #07/10/2002#" If rec.NoMatch = True Then   strMsg = "No orders cost " & FormatCurrency(curPrice)   rec.Bookmark = strBookmark   Else   strMsg = "Order No. " & rec("SalesID") & " placed on " & _        FormatDateTime(rec("DateOrdered"), vbLongDate) & _        " cost " & FormatCurrency(rec("AmountPaid")) End If

Comparing Bookmarks

Sometimes, you may wish to compare two Bookmark properties. For example, you may want to check whether the current record is one that you visited earlier and whose Bookmark you had saved.

Although you can store a Bookmark as a String variable, you need to remember that a Bookmark is stored internally as an array of Bytes . For this reason, you should use binary comparison when comparing two bookmarks with each other.

If the Option Compare Database statement is present in a module, which it is by default, comparisons will be made according to the sort order determined by the locale of the database. In other words, when you compare two strings together in Access, the default for US English is for the comparison to be case-insensitive. You can prove this by opening the Immediate window and evaluating the following expression:

 ?"aaa" = "AAA"

When you hit the Enter key, the result should be True , which means that string comparisons are not case-sensitive.

In contrast, when binary comparison is enabled, comparisons are made according to the internal binary representation of the characters, which is case-sensitive. Because lower case characters (for example "a" ) are represented differently internally than upper case characters ( "A" ), a binary comparison of "aaa" and "AAA" should return False .

When you compare Bookmark properties, you want to make sure that the comparison is case-sensitive, otherwise you may find that the comparison returns True when the Bookmarks are not completely identical. The safest way to do this is to compare string variables with the StrComp function, which returns if the two variables that are being compared are identical. This has an argument that allows you to choose what type of comparison you wish to perform:

If the comparison argument is set to vbBinaryCompare (0) , it forces binary comparison of the two variables.
If it is set to vbTextCompare (1) , it forces textual comparison.
And if it is set to vbDatabaseCompare (2) , or is omitted, the comparison is performed based on the sort order that was in place when the database was created.
```
   intResult=StrComp(strBkMk1, strBkMk2, 1vbTextCompare) 'Textual comparison     intResult=StrComp(strBkMk1, strBkMk2, 0vbBinaryCompare) 'Binary comparison   
```
Recordset objects based on native Access tables should all support bookmarks. However, Recordset objects based on linked tables from some databases, such as Paradox tables with no primary key, may not support bookmarks. Before you attempt to use bookmarks, you can test whether the Recordset object supports them by inspecting its Bookmarkable property. This will be True if the Recordset object supports bookmarks .

Editing Records in Recordsets

You now know how to find particular records within a Recordset object, but what if you want to edit them once you've found them? There are five main methods that can be used for manipulating data in recordsets. These are listed here:

This method...	Has this effect...
Edit	Copies the current record to the copy buffer to allow editing.
AddNew	Creates a new record in the copy buffer with default values (if any).
Update	Saves any changes made to the record in the copy buffer.
CancelUpdate	Empties the copy buffer without saving any changes.
Delete	Deletes the current record.

From the table above you should be able to see that changes to records are made in the copy buffer rather than in the recordset itself. What this means in practice is that adding or amending a record is a three-part process:

Copy the current record into the copy buffer with the Edit method, or place a new record in the copy buffer with the AddNew method
Make any required changes to the fields in that record
Save the changes from the copy buffer to disk with the Update method

Note that if you try to make changes to a record without first copying it to the copy buffer (without using the Edit method), Access will generate a run-time error. And if you move to a new record without saving the changes to the current record in the copy buffer (using the Update method) - or by canceling the change, those changes will be lost.

If you want to empty the copy buffer without moving to a new record, you can use the CancelUpdate method on the Recordset . This will undo any changes you may have made to the record in the copy buffer, but does not change the current record.

If you want to know whether any records have been copied into the copy buffer and not saved, you can inspect the EditMode property of the recordset. This can hold any of three values represented by the constants in the table below.

This constant...	Has this value	And means this...
dbEditNone		There is no record in the copy buffer.
dbEditInProgress	1	The current record is in the copy buffer (the Edit method has been invoked).
dbEditAdd	2	The record in the copy buffer is a new record that hasn't been saved (the AddNew method has been invoked).

If you use the Delete method the deletion is immediate; you do not have to follow it with an Update method to make the deletion permanent. However, although the record is deleted, it is still regarded as the current record. You need to make a different record the current record before you perform any more operations that require a valid current record. Once you have moved away from a deleted record, you cannot make it current again.

We shall be looking at how Access locks records when editing and updating Recordset objects when we consider multi-user aspects of Access in Chapter 17.

Try It Out-Editing Records in VBA

Open the tblCompany table and have a look at the place names in the Country field. They are currently in mixed case. Note the following illustration that shows these values before they are updated.

Close the table and then, in the code module you created earlier in this chapter, add the Capitalize procedure:

   Function Capitalize(strTable As String, strFld As String)     Dim db As Database     Dim rec As Recordset     Set db = CurrentDB()     Set rec = db.OpenRecordset(strTable)     'Loop through all records until we go beyond the last record     Do While Not rec.EOF         'Copy the current record to the copy buffer     rec.Edit         'Make changes to the record in the copy buffer     rec(strFld) = UCase$(rec(strFld))         'Save the contents of the copy buffer to disk     rec.Update         'Make the next record the current record     rec.MoveNext         Loop     Capitalize = True     End Function

Now make sure that the Immediate window is visible and type the following line of code.
```
   ? Capitalize ("tblCompany", "Country")   
```
When you hit the Enter key, if the Capitalize function executes correctly, it will convert all the names of the places in the Country field of the tblCompany table to upper case and then return True to indicate success.
Open the tblCompany table again and have a look at the names of the places in the Country field. They should now be in upper case.

Note that this example is simply an illustration of the sequence of events required when editing a record. In practice, using an action query would be considerably more efficient than stepping through all the records.

When a Recordset Can't be Updated

We started the chapter by pointing out the differences between the various types of recordset. One of the most obvious differences between snapshot-type Recordset objects and dynaset-type Recordset objects is that snapshots are static images of the data and are never editable. So, if you try to use the Edit , AddNew, or Delete methods against a snapshot-type Recordset object, Access will generate a run-time error. However, there are also several occasions when a dynaset-type Recordset cannot be edited, such as:

When it is based on a crosstab query.
When it is based on a union query.
When you have not been granted permission to update the records in the table on which the recordset is based.

In order to be sure that your Recordset object can be edited, you can inspect its Updatable property. This will be True if the recordset can be updated, and False otherwise.

   If rec.Updatable = True Then     rec.Edit     ...     ...     ...     rec.Update     End If