When to use a form’s recordset and when to use recordsetclone

Recordsets are an essential tool of any Access programmer, there are many types and are used in a wide range of situations, today we’re going to talk about form recordsets and provide some examples of their use.

What are Form Recordsets?
If your form has a data source, either a table or query, it has a recordset property you can use in your code to get direct access to the data. When you open a recordset with the form it gains access to all of the data the form has. You can then use the recordset to find records, modify data or other uses. The only way to use them is through code and a DAO or ADODB recordset object.

Why would you use a Form Recordset?
When the form opens and loads the data it will make a round trip to disk, why not take advantage of that trip and read it off the form using a recordset? A regular recordset makes the round trip but a form recordset reads it off the form. Any filter or sort order applied to the form would also be applied to the recordset.

Recordset Clone Example
This kind of recordset is used when you don’t want the data on the form to change. as illustrated below:

Let’s assume you have a field called txtFindCustomer on a form called frmCustomers that allows users to type in any characters they wish to find a customer record. On the AfterUpdate event you can place the following code:

Private Sub txtFindCustomer_AfterUpdate()

1     Dim rst AS DAO.Recordset

2     Set rst = Me.RecordSetClone
3     rst.MoveFirst
4     rst.FindFirst “CustomerName Like ‘*” & txtFindCustomer & “*’”
5     If Not rst.EOF Then
6           Me.BookMark = rst.BookMark
7     Else
8           MsgBox “Could not find Customer with a name that includes ” & _                     9                    txtFindCustomer
10    End If

11    Set rst = Nothing

End Sub

Notice on line 2 I use the Me equivalent of the form, I’m a big fan of Me, it allows me to copy my code to other projects with ease. As you can see, it only takes one line to open the recordset, then I move it to the first record and start my search. I finally use the bookmark property to sync the form with my search so that the user can see the record located. You will need a reference to DAO in your project in order to use the code above.

If you used a regular form recordset then the user would have seen the first record and then any record matching the criteria, if there were no hits they would have ended up at the end of the table. Instead, the recordsetclone will only move the form’s focus if there is a match, or leave them on their existing record if there is not.

SQL Server Issue
SQL Server tables can hold millions of records, so searching for a partial name as shown above using a recordset can take a long time or not work at all. When searching a large volume of records consider using DoCmd.FindRecord instead or another alternative.