ACC2002: Parameter Dialog Box Appears When You Run a Pasted Query (298877)


The information in this article applies to:

  • Microsoft Access 2002
This article was previously published under Q298877
Novice: Requires knowledge of the user interface on single-user computers.

This article applies only to a Microsoft Access database (.mdb).

SYMPTOMS

When you run a query that was created by pasting a copy of another query, you receive an Enter Parameter Value dialog box for QueryName.FieldName.

CAUSE

You sorted the original query by using the Sort Ascending or Sort Descending commands in Datasheet view.

RESOLUTION

Open the newly pasted query in Design view, and then change the OrderBy property to reference the new query's name. Or, simply clear the OrderBy property.

To prevent the problem from occurring in the future, apply sorting in Design view of the query by using the query design grid. This causes the SQL statement of the query to include the proper ORDER BY clause that will work correctly if the query is copied and pasted.

STATUS

Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

MORE INFORMATION

When you sort in Datasheet view, Microsoft Access sets the OrderBy property of the query to the fully qualified field name using the QueryName.FieldName syntax. For example, if you sorted the Alphabetical List of Products query by the ProductName field, the OrderBy property of the query would be set to [Alphabetical List of Products].ProductName.

If you then copy and paste this query to a new query with a different name, Microsoft Access does not update the OrderBy property of the new query to reference itself. Because the OrderBy property still references the original query, it causes Microsoft Access to prompt you for a parameter value when you run the query.

Steps to Reproduce the Behavior

CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

  1. Open the sample database Northwind.mdb.
  2. On the View menu, point to Database Objects, and then click Queries.
  3. Click the Alphabetical List of Products query in the Database window, and then click Open.
  4. Click in the ProductName field, and then on the Records menu, point to Sort, and then click Sort Ascending. Note that the query is sorted in ascending order by ProductName.
  5. On the File menu, click Close. Click Yes when Microsoft Access prompts you to save changes.
  6. Click the Alphabetical List of Products query in the Database window, and then on the Edit menu, click Copy.
  7. On the Edit menu, click Paste.
  8. In the Paste As dialog box, type Alpha2, and then click OK.
  9. Click the Alpha2 query in the Database Window, and then click Open.
Note that you receive an Enter Parameter Value dialog box for Alphabetical List of Products.ProductName.
Modification Type:MajorLast Reviewed:9/27/2003
Keywords:kbbug kbnofix KB298877
©2003 Microsoft Corporation. All rights reserved.