You are currently offline, waiting for your internet to reconnect

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

Support for Office 2003 has ended

Microsoft ended support for Office 2003 on April 8, 2014. This change has affected your software updates and security options. Learn what this means for you and how to stay protected.

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.

    Note If you use Access 2007, click to highlight the Categories table in the database window, and then click Form in the Forms group on the Create tab.
  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, acWindowNormalEnd 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.

    Note In the Quick Access toolbar, click Save.
  9. In the Database window, click Queries, and then click New.

    Note If you use Access 2007, click Query Wizard in the Other group on the Create tab.
  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 intAS	SELECT * FROM Products WHERE CategoryID = @CatIDRETURN 					
  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 in Acess 2003

  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, acWindowNormalEnd 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 spProductsAS	SELECT * FROM ProductsRETURN 					
  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.
pra prb ACC2002 ACC2003 ACC2007
Properties

Article ID: 275089 - Last Review: 03/29/2007 23:15:02 - Revision: 6.1

Microsoft Office Access 2007, Microsoft Office Access 2003, Microsoft Access 2002 Standard Edition

  • kbvba kbprogramming kbclientserver kbnofix kbprb KB275089
Feedback
var varAutoFirePV = 1; var varClickTracking = 1; var varCustomerTracking = 1; var Route = "76500"; var Ctrl = ""; document.write("