How to use form parameters in the RecordSource property in an Access project

CAUTION: If you follow the steps in this example, you modify the sample Access project NorthwindCS.adp. You may want to back up the NorthwindCS.adp file and follow these steps on a copy of the project.

The following steps create a command button on the Customers form that opens the Orders form and shows only the matching records for the current customer.

Add a command button for the Customers form

1. In Access, open the sample database NorthwindCS.adp.
2. Open the Customers form in Design view.
3. Using the wizard, add a command button to the Customers form that opens the Orders form and returns all records.
4. Close and save the form.

Create a function to return matching records

1. In the Queries object group, double-click Create function in designer.
   
   Note In Access 2007, click Query Wizard in the Other group on the Create tab. In the New Query dialog box, click Design In-Line Function, and then click OK.
2. In the Add Table dialog box, click the Views tab, click Orders Qry, and then click OK.
3. In the Orders Qry object, click to select the * (All Columns) check box.
4. Add the CustomerID field to the function.
5. Add = @p1 to the criteria for the CustomerID field.
6. Click to clear the Output property for the CustomerID field.
   
   Notice that theCustomerID field is automatically displayed because the * (All Columns) option was selected.
7. On the View menu, point to Show Panes, and then click SQL.
   
   Note In Access 2007, click SQL in the Tools group on the Design tab.
   
   Notice that the following SQL statement should be displayed in the SQL pane.

   SELECT dbo.[Orders Qry].*<BR/>
   FROM dbo.[Orders Qry]<BR/>
   WHERE (CustomerID = @p1)

8. Close and save this function as fn_CustOrders.

Modify Orders form to show matching records

1. Open the Orders form in Design view.
2. Modify the RecordSource property to set the parameter from the function to use the CustomerID field from the Customers form as criteria.

   Select * from fn_CustOrders(@[Forms]![Customers]![CustomerID])

   Notice that the Input Parameter property has been automatically changed to:
   

   ? = [Forms]![Customers]![CustomerID]

3. Close and save the Orders form.

Test the Customers and the Orders forms

1. Open the Customers form.
   
   Notice which customer appear.
2. Click the command button to open the Orders form.
   
   Notice that only those orders matching the current customer appear.

Using this approach allows the records to be filtered on the server side, based upon which customer is displayed, returning only the matching records for the current customer.