Recordset Property

See Also         Example         Applies To

You can use the Recordset property to specify or retrieve the ADO Recordset or DAO Recordset object representing a form's record source.

Note   You cannot use this property with ODBCDirect recordset types in DAO.

Setting

The Recordset property returns the recordset object that provides the data being browsed in a form. If a form is based on a query, for example, referring to the Recordset property is the equivalent of cloning a Recordset object by using the same query. However, unlike using the RecordsetClone property, currency changes made to the Form.Recordset property are automatically reflected in the current record of the form.

This property is available only by using Visual Basic.

Remarks

When a recordset is asked for in a Microsoft Access database (.mdb), a DAO recordset is returned, in a Microsoft Access project (.adp), an ADO recordset is returned.

The read/write behavior of a form whose Recordset property has been set using Visual Basic is determined by the type of recordset (ADO or DAO) and the type of data (Jet or SQL) contained in the recordset identified by the property.

(1) Note   The ADO Recordset.CursorLocation property must be set to adUseClient. The UniqueTable property is available in client/server on the property sheet, but not for Access databases (.mdb). This property must be set in Visual Basic code.

Global rstSuppliers As ADODB.Recordset
Sub MakeRW()
    DoCmd.OpenForm "Suppliers"
    Set rstSuppliers = New ADODB.Recordset
    rstSuppliers.CursorLocation = adUseClient
    rstSuppliers.Open "Select * From Suppliers", CurrentProject.Connection, adOpenKeyset, adLockOptimistic
    Set Forms("Suppliers").Recordset = rstSuppliers
    Forms("Suppliers").UniqueTable = "Suppliers"
End Sub

You use the Recordset property:

• To bind multiple forms to a common data set. This allows the synchronizing of multiple forms. For example

    Set Me.Recordset = Forms!Form1.Recordset

• To use methods on the Recordset object that aren't directly supported on forms. For example, you can use the Recordset property when you want to use the ADO Find method or DAO Find methods to use a custom dialog for finding a record.
  
  
• To wrap a transaction (which can be rolled back) around a set of edits that affect multiple forms.

When a new Recordset object is opened, its first record is the current record.

Note   If you change the form's RecordSource property, you must use the Set statement. Changing a form's Recordset property may also change the RecordSource, RecordsetType, and RecordLocks properties. Also, some data-related properties may be overridden; for example, the Filter, FilterOn, OrderBy, and OrderByOn properties.