You can use the RecordSource property to specify the source of the data for a form or report. Read/write String.
expression Required. An expression that returns one of the objects in the Applies To list.
The RecordSource property setting can be a table name, a query name, or an SQL statement. For example, you can use the following settings.
A table name specifying the Employees table as the source of data.
SELECT Orders!OrderDate FROM Orders;
An SQL statement specifying the OrderDate field on the Orders table as the source of data. 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.
Note: You can set the RecordSource property by using the form's or report's property sheet, a macro, or Visual Basic for Applications (VBA) code.
In VBA, 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. If a form's Recordset property is set at runtime, the form's RecordSource property is updated.
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 an SQL statement that returns a single record and change the form's record source depending on criteria selected by the user.
The following example sets a form's RecordSource property 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. The CustomerID has a Text data type.
Dim strNewRecord As String
strNewRecord = "SELECT * FROM Customers " _
& " WHERE CustomerID = '" _
& Me!cmboCompanyName.Value & "'"
Me.RecordSource = strNewRecord