Somos Expertos Access. Contacto atravez de Skype Skype logo expertos_7

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.

 

 

 

 

 

Acerca de

Juan Soto es un desarrollador senior de Access en IT Impact Inc. y un MVP de Microsoft Access y se especializa en Access con bases de datos de SQL Server. Su pasión por el acceso lo ha llevado ayudar a una amplia gama de empresas a establecer un entorno seguro, estable y eficiente con SQL Server. Él es un orador frecuente en los grupos de usuarios de Access en todo el país y recientemente habló en el Orange County SQL Saturday # 73. Si desea que Juan hable en su próxima reunión de grupo, puede ponerse en contacto con él aquí.

Publicado en Access Help

Deja un comentario

Tu dirección de correo electrónico no será publicada. Los campos obligatorios están marcados con *

*

 

Quienes Somos

ExpertosMicrosoftAccess.com es un servicio de la empresa IT Impact, Inc., una compañía de programación y servicios para empresas en Latino América. Ofrecemos servicios en .Net, SQL Server y Microsoft Access. Muchos de nuestros desarrolladores han obtenido el galardón de Access MVP, un título proveído por Microsoft a aquellos que han hecho aportes a la comunidad y que han demostrado tener conocimientos superiores del producto.

Nuestro Equipo

  • Le ayudamos a "Descubrir el poder de sus datos™" con reportes y sistemas de Access excepcionales .
  • Creamos soluciones de bases de datos personalizadas utilizando Microsoft Access y / o SQL Server.
  • Nuestros consultores ganaron sus estrellas en las empresas de servicios y/o manufactura antes de convertirse en programadores.

Blogs anteriores