ACC: You Are Prompted Twice for Same Query Parameter Value

• Microsoft Access versions 7.0, 97

SYMPTOMS

Novice: Requires knowledge of the user interface on single-user computers.

When you switch a query from Design view to Datasheet view, Microsoft Access prompts you for the same parameter twice; that is, you must type the same parameter value twice in order to view the correct results in Datasheet view.

CAUSE

You have set the Filter property of the query. When you switch from Design view of the query to Datasheet view, the query runs twice--once when the query opens, and again when the filter is applied. Each time the query runs, you receive a prompt for the parameter.

RESOLUTION

To work around this behavior, use either of the following methods.

Method 1

Close the query, and open it from the Database window.

Method 2

Remove the filter from the query. To do so, follow these steps:

1. Open the query in Design view.

2. Right-click in the gray area on the top half of the QBE grid, and then click Properties on the shortcut menu that appears.

3. In the Query Properties dialog box, delete the contents of the Filter box.

MORE INFORMATION

This behavior occurs only if you already have the query open and in the filtered state when you switch to Design view. If you open the query directly from the Database window, you receive only one prompt for the parameter. Microsoft Access prompts you for the parameter again when you apply the filter by clicking the Apply Filter button on the Query Datasheet toolbar or by clicking the Apply Filter/Sort command on the Records menu.

Steps to Reproduce Behavior

1. Start Microsoft Access and open the sample database Northwind.mdb.

2. Create the following new query based on the Employees table:

         Query: qryDblPrompt
         --------------------------
         Type: Select Query
   

         Field: FirstName
            Table: Employees
         Field: LastName
            Table: Employees
         Field: Title
            Table: Employees
         Field: HireDate
            Table: Employees
            Criteria: > [Enter Date]
   
   

3. Right-click in the top half of the QBE grid, and then click Properties.

4. In the Query Properties dialog box, enter the following for the Filter property:

         [Title] = "Sales Representative"
   

5. Save the query as qryDblPrompt.

6. Run the query, and when the parameter prompt appears, enter the date 1/1/94.

7. On the Records menu, click Apply Filter/Sort.

8. When the parameter prompt appears, enter the date 1/1/94.

9. On the View menu, click Design view (or Query Design in version 7.0). Then on the View menu, click Datasheet view (or Datasheet in version 7.0).

   Note that Microsoft Access prompts you twice for the same parameter value.

REFERENCES

For more information about the Filter property, search the Help Index for "Filter property," and display the topic "Filter property (Microsoft Access)."