RecordSource Property

Applies To

Form, Report.

Description

You can use the RecordSource property to specify the source of the data for a form or report. You can display data from a table, query, or SQL statement. For example, to display and edit data from the Employees table in a form, you set the form’s RecordSource property to Employees. Then you can bind controls on the form or report to specific fields in the Employees table by setting the control’s ControlSource property to the name of a field in the table.

Setting

The RecordSource property setting can be a table name, a query name, or an SQL statement. For example, you can use the following settings.

TR>

Sample Setting

Description

A table or query name

You can bind a control on the form or report to the LastName field in the Employees table by setting the control’s ControlSource property to LastName.

An SQL statement

You can bind a control on the form or report to the OrderDate field in the Orders table by setting the control’s ControlSource property to OrderDate, as in the following example.

SELECT DISTINCTROW Orders.[OrderDate] Orders;


You can set the RecordSource property using the form or report’s property sheet, a macro, or Visual Basic.

In Visual Basic, use a string expression to set this property.

Note Changing the record source of an open form or report causes an automatic requery of the underlying data.

Remarks

After you have created a form or report, you can change its source of data by changing the RecordSource property. The RecordSource property is also useful if you want to create a reusable form or report. For example, you could create a form that incorporates a standard design, then copy the form and change the RecordSource property to display data from a different table, query, or SQL statement.

Limiting the number of records contained in a form’s record source can enhance performance, especially when your application is running on a network. For example, you can set a form’s RecordSource property to a single record and change the form’s record source depending on criteria selected by the user.

You can also achieve improved performance by setting the RecordSource property to the name of a table or saved query instead of an SQL statement.

See Also

ControlSource Property; RowSourceType, RowSource Properties.

Example

The following example sets the RecordSource property for a form to the Customers table.


Forms![frmCustomers].RecordSource = "Customers"

The next example changes a form’s record source to a single record in the Customers table, depending on the company name selected in the cmboCompanyName combo box control. The combo box is filled by an SQL statement that returns the customer ID in the bound column and the company name.


Sub cmboCompanyName_AfterUpdate()
    Dim strNewRecord As String
    strNewRecord = "SELECT DISTINCTROW * FROM Customers "
    strNewRecord = strNewRecord & "WHERE [CustomerID] = '" _
        & Me!cmboCompanyName.Value & "'"
    Me.RecordSource = strNewRecordSub