Go to Top

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.

ADVERTISING
ACCESS SAFETY AND TRAINING DATABASE

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.

 

 

 

 

 

About Juan Soto

Juan Soto is a Senior Access Developer at IT Impact Inc. and a Microsoft Access MVP. He specializes in Access with SQL Server databases. His passion for Access has led him to helping a wide range of businesses in helping them establish a secure, stable and efficient environment with SQL Server. He's a frequent speaker at Access user groups nationwide and recently spoke at the Orange County SQL Saturday # 73. If you wish to have Juan speak at your next group meeting you can contact him here.

4 Responses to "When to use a form's recordset and when to use recordsetclone"

  • Chuck
    February 4, 2012 - 2:13 am Reply

    I am new to access, can you provide an actual working example? I need to see this live to comprehend it. Is this possible?

    • Juan Soto
      February 4, 2012 - 2:20 am Reply

      Hi Chuck!

      If you’re new to Access this post may not be relevant to you since it’s a topic only for those who are programming in Access. If you do program in Access you would only encounter this situation when using the form’s recordset.

      Cheers,
      Juan

      • Chuck
        February 4, 2012 - 2:57 am Reply

        Hi Juan,
        I have done some vba coding but I do it on the fly taking code snippets here and there and try to understand the usage and apply to a working project. Just by reading your post, I do better visualizing a working example to see how it may fit in my database project, I like to learn and adapt my vba coding.

        Thanks.

  • Anonymous
    November 3, 2011 - 12:39 am Reply

    Second paragraph should be “If your form…”

Leave a Reply

Your email address will not be published. Required fields are marked *

 

Contact Us
  • This field is for validation purposes and should be left unchanged.