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


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q278400
Advanced: Requires expert coding, interoperability, and multiuser skills.

This article applies only to a Microsoft Access project (.adp).

SUMMARY

This article describes how to use the Forms!FormName!ControlName parameter reference in the RecordSource property for a form in a Microsoft Access project (ADP). In this method, the difficult step is integrating a server-based query with Access objects, such as a form reference.

MORE INFORMATION

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 Customer Form

  1. On the Help menu, point to Sample Databases, and then click Northwind Sample Access Project to open 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.
  2. Add the Orders Qry view to the function and click to select * (All Columns).
  3. Add the CustomerID field to the function.
  4. Add = @p1 to the criteria for the CustomerID field.
  5. Click to clear the Output property for the CustomerID field. The CustomerID field is automatically displayed because the * (All Columns) option was selected.
  6. On the View menu, point to Show Panes, and then click SQL. The following SQL statement should be displayed in the SQL pane.
    SELECT dbo.[Orders Qry].*<BR/>
FROM dbo.[Orders Qry]<BR/>
WHERE (CustomerID = @p1)
  7. 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 from the Customers form as criteria.
    Select * from fn_CustOrders(@[Forms]![Customers]![CustomerID])
    Note 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

Open the Customers form and note which customer is displayed. Click the command button to open the Orders form. Note that only those orders matching the current customer are displayed. 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.
Modification Type:MinorLast Reviewed:6/7/2004
Keywords:KbClientServer kbhowto KB278400
©2004 Microsoft Corporation. All rights reserved.