ACC2002: OpenForm and OpenReport WHERE Clauses Are Ignored When You Use Server Filter (296049)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q296049
Advanced: Requires expert coding, interoperability, and multiuser skills.

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

SYMPTOMS

When you use a WHERE clause with the OpenForm and OpenReport methods and actions, the WHERE clause will be ignored if a server filter has been set on the form or report.

CAUSE

If a server filter exists, it will be used to query SQL Server instead of the WHERE clause in the OpenForm or OpenReport command.

RESOLUTION

Remove the ServerFilter property setting in Design view, and then save the form or the report. When the form or report is opened, it will reflect the records that are specified in the WHERE clause in the OpenForm or OpenReport methods or actions.

MORE INFORMATION

A common technique to synchronize two forms in order to show related data is to specify a WHERE clause in the OpenForm command. The Command Button Wizard creates an OpenForm statement when you select the option Open the form and find specific data to display.

However, if the form being opened already has a server filter specified, it indicates to Access that all query processing should be done on the server and to ignore the WHERE clause in the OpenForm command.

Steps to Reproduce the Behavior

  1. Open the sample project NorthwindCS.adp, and then open the Orders form in Design view.
  2. Add the following setting to the ServerFilter property of the Orders form, and then save the form:

    [Region] = 'WA'

  3. Open the form, and note that the server filter is applied. Only the records for the region 'WA' are displayed. Close and then save the changes to the form.
  4. Open the Customers form in Design view, and then use the Command Button Wizard to add a command button to open the Orders form.
  5. Click the option to Open the form and find specific data to display.
  6. Select to match on CustomerID, accept the default options, and then save the changes. The Command Button Wizard will automatically write the correct OpenForm command using the CustomerID as the WHERE clause to show matching records.
  7. Open the Customer form, and note which customer is being displayed; then, click the button to show that customer's matching orders.

Note that instead of showing the matching orders for CustomerID, the server filter is applied, and the orders for the specified region are displayed. Had the ServerFilter property not been set, the correct matching orders would have been displayed.
Modification Type:MajorLast Reviewed:9/25/2003
Keywords:kbprb KB296049
©2003 Microsoft Corporation. All rights reserved.