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

Article translations Article translations
Article ID: 275089 - View products that this article applies to.
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).

Expand all | Collapse all

On This Page

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, 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.

    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 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 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, 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.

Properties

Article ID: 275089 - Last Review: March 29, 2007 - Revision: 6.1
APPLIES TO
  • Microsoft Office Access 2007
  • Microsoft Office Access 2003
  • Microsoft Access 2002 Standard Edition
Keywords: 
kbvba kbprogramming kbclientserver kbnofix kbprb KB275089

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com