Microsoft KB Archive/275089

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

Article ID: 275089

Article Last Modified on 3/29/2007

-

APPLIES TO


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

-



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.

 Open the sample project NorthwindCS.adp. 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. Open the form in Design view. 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.  Set the following properties for the command button:        Name: cmdFilterProducts Caption: Filter Products On Click: [Event Procedure]   Set the OnClick property of the command button to the following event procedure: Private Sub cmdFilterProducts_Click DoCmd.OpenForm &quot;Products&quot;, acNormal,, , acFormEdit, acWindowNormal End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save, and save the form as Categories1.

Note In the Quick Access toolbar, click Save.</li> 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.</li> In the New Query dialog box, click Create Text Stored Procedure, and then click OK.</li>  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 </li> Open the Products form in Design view, and then change the Record Source property from Products to spProducts.</li> Scroll down to the InputParameters property of the form, and then assign the following value to this property:

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

</li> Close and save the Products form, and then open the Categories1 form.</li> 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.</li></ol>

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

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
<ol> Follow steps 1 through 5 of the &quot;Resolution&quot; section earlier in this article.</li>  Set the OnClick property of the command button to the following event procedure: Private Sub cmdFilterProducts_Click DoCmd.OpenForm &quot;Products&quot;, acNormal,, &quot;[CategoryID] = &quot; & Me!CategoryID, acFormEdit, acWindowNormal End Sub </li> On the File menu, click Close and Return to Microsoft Access.</li> On the File menu, click Save, and then save the form as Categories1.</li> Open the form in Form view.</li> 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.</li> <li>Close both the Products and Categories1 forms.</li> <li>In the Database window, click Queries, and then click New. Click Create Text Stored Procedure, and then click OK.</li> <li> 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 </li> <li>Open the Products form in Design view, and then change the RecordSource property from Products to spProducts.</li> <li>Close and save the Products form, and then open the Categories1 form.</li> <li>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.</li></ol>

Additional query words: pra prb ACC2002 ACC2003 ACC2007

Keywords: kbvba kbprogramming kbclientserver kbnofix kbprb KB275089

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.