Form or report that is bound to a stored procedure or a function does not apply the WHERE condition (275089)


The information in this article applies to:

  • Microsoft Office Access 2003
  • Microsoft Access 2002
This article was previously published under Q275089
Moderate: Requires basic macro, coding, and interoperability skills.

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

SYMPTOMS

When you try to filter an Access form or report by using a value that is displayed on another object, such as another Access form, the filter is not applied.

RESOLUTION

There are several methods that one could use to resolve the inability to filter a form or a report that is bound to a stored procedure or function. Most of these resolutions involve replacing the stored procedure or the function with some other type of record source, such as a table, a view, or an SQL statement.

However, the following resolution shows you how you can continue to use a stored procedure as the record source for a form while successfully implementing a WHERE condition or filter.

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.

  1. Open the sample project NorthwindCS.adp.
  2. In the Database window, click to highlight the Categories table, and then click AutoForm on the Insert menu.
  3. Open the form in Design view.
  4. Delete the subform object that is labeled Table.Products from the main form, and then add a command button to the form. If the wizard starts, click Cancel.
  5. Set the following properties for the command button:
            Name: cmdFilterProducts
     Caption: Filter Products
    On Click: [Event Procedure]
  6. Set the OnClick property of the command button to the following event procedure:
    Private Sub cmdFilterProducts_Click()
    DoCmd.OpenForm "Products", acNormal, , , acFormEdit, acWindowNormal
End Sub
  7. On the File menu, click Close and Return to Microsoft Access.
  8. On the File menu, click Save, and save the form as Categories1.
  9. In the Database window, click Queries, and then click New.
  10. In the New Query dialog box, click Create Text Stored Procedure, and then click OK.
  11. Type or paste the following Transact-SQL statement, and then close and save the procedure with the default name of spProducts:
    CREATE PROCEDURE spProducts
	@CatID int
AS
	SELECT * FROM Products WHERE CategoryID = @CatID
RETURN
  12. Open the Products form in Design view, and then change the Record Source property from Products to spProducts.
  13. Scroll down to the InputParameters property of the form, and then assign the following value to this property:

    @CatID int = Forms![Categories1]![CategoryID]

  14. Close and save the Products form, and then open the Categories1 form.
  15. Browse to the CategoryName Confections, and then click the Filter Products button. Note that the Products form opens and displays only those products where Category is equal to Confections.

STATUS

This behavior is by design.

MORE INFORMATION

Often, an Access user will want to limit the number of records that appear on an Access form or report. One method for doing this is to filter records on the form (or report) with a WHERE condition and to use some other object to supply the conditional value. For example, one form can display a conditional value that will be used by another form in order to limit the number of records to be displayed. However, if the form that you are trying to filter has a stored procedure or function as its record source, the filter is ignored. The following steps illustrate this behavior.

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.

Steps to Reproduce Behavior

  1. Follow steps 1 through 5 of the "Resolution" section earlier in this article.
  2. Set the OnClick property of the command button to the following event procedure:
    Private Sub cmdFilterProducts_Click()
    DoCmd.OpenForm "Products", acNormal, , "[CategoryID] = " & Me!CategoryID, acFormEdit, acWindowNormal
End Sub
  3. On the File menu, click Close and Return to Microsoft Access.
  4. On the File menu, click Save, and then save the form as Categories1.
  5. Open the form in Form view.
  6. Browse to the CategoryName Confections, and then click the Filter Products button. Note that the Products form opens and displays only those products where the Category is equal to Confections.
  7. Close both the Products and Categories1 forms.
  8. In the Database window, click Queries, and then click New. Click Create Text Stored Procedure, and then click OK.
  9. Type or paste the following Transact-SQL statement, and then close and save the procedure with the default name of spProducts:
    CREATE PROCEDURE spProducts
AS
	SELECT * FROM Products
RETURN
  10. Open the Products form in Design view, and then change the RecordSource property from Products to spProducts.
  11. Close and save the Products form, and then open the Categories1 form.
  12. Browse to the CategoryName Confections, and then click the Filter Products button. Note that the Products form opens and displays all products instead of just those products in the Confections category.
Modification Type:MinorLast Reviewed:6/7/2004
Keywords:KbVBA kbProgramming KbClientServer kbnofix kbprb KB275089
©2004 Microsoft Corporation. All rights reserved.