Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

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

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

Moderate: Requires basic macro, coding, and interoperability skills.


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


↑ Back to the top

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.

↑ Back to the top

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.

↑ Back to the top

Status

This behavior is by design.

↑ Back to the top

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.

↑ Back to the top

Keywords: kbprb, kbnofix, kbclientserver, kbaccess10ready, accrtmpublic, kboffice2003yes, kbprogramming, kbbillprodsweep, kb, kbsweptsoltax, kbvba, kboffice12yes, kbfreshness2007, kbswept

↑ Back to the top

Article Info
Article ID : 275089
Revision : 3
Created on : 4/23/2018
Published on : 4/23/2018
Exists online : False
Views : 202