Returns or sets the ADO Recordset or DAO Recordset object representing the record source (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) for the specified form, report, list box control, or combo box control. Read/write.
expression Required. An expression that returns one of the objects in the Applies To list.
The Recordset property returns the recordset object that provides the data being browsed in a form, report, list box control, or combo box control. 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, changing which record is current in the recordset returned by the form's Recordset property also sets the current record of the form.
This property is available only by using Visual Basic for Applications (VBA) (Visual Basic for Applications (VBA): A macro-language version of Microsoft Visual Basic that is used to program Microsoft Windows-based applications and is included with several Microsoft programs.) code.
The read/write behavior of the Recordset property is determined by the type of recordset (ADO or DAO) and the type of data (Access or SQL) contained in the recordset identified by the property.
||Based on SQL data
||Based on Access data
The following example opens a form, opens a recordset, and then binds the form to the recordset by setting the form's Recordset property to the newly created Recordset object.
Global rstSuppliers As ADODB.Recordset
Set rstSuppliers = New ADODB.Recordset
rstSuppliers.CursorLocation = adUseClient
rstSuppliers.Open "Select * From Suppliers", _
CurrentProject.Connection, adOpenKeyset, adLockOptimistic
Set Forms("Suppliers").Recordset = rstSuppliers
Use the Recordset property:
- To bind multiple forms to a common data set. This allows synchronization of multiple forms. For example,
Set Me.Recordset = Forms!Form1.Recordset
- To use methods with the Recordset object that aren't directly supported on forms. For example, you can use the Recordset property with the ADO Find or DAO Find methods in 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.
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.
Calling the Requery
method of a form's recordset (for example,
) can cause the form to become unbound. To refresh the data in a form bound to a recordset, set the RecordSource
property of the form to itself (
Forms(0).RecordSource = Forms(0).RecordSource
When a form is bound to a recordset, an error occurs if you use the Filter by Form command.
The following example uses the Recordset property to create a new copy of the Recordset object from the current form and then prints the names of the fields in the Debug window.
Dim rst As DAO.Recordset, intI As Integer
Dim fld As Field
Set rst = Me.
For Each fld in rst.Fields
' Print field names.
The next example uses the Recordset property and the Recordset object to synchronize a recordset with the form's current record. When a company name is selected from a combo box, the FindFirst method is used to locate the record for that company, causing the form to display the found record.
Dim rst As DAO.Recordset
Dim strSearchName As String
Set rst = Me.
strSearchName = CStr(Me!SupplierID)
rst.FindFirst "SupplierID = " & strSearchName
If rst.NoMatch Then
MsgBox "Record not found"
The following code helps to determine what type of recordset is returned by the Recordset property under different conditions.
Dim rs as Object
If TypeOf rs Is DAO.Recordset Then
MsgBox "DAO Recordset"
ElseIf TypeOf rs is ADODB.Recordset Then
MsgBox "ADO Recordset"