ACC2000: Prompted Again for Parameter on Refresh of Client-Server Query

ID: Q202199


The information in this article applies to:

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

This article applies to a Microsoft Access database (.mdb) and a Microsoft Access project (.adp).


SYMPTOMS

If you refresh a parameter query in a Microsoft Access database, you are not prompted again for the parameter. However, if you refresh a parameter query in a Microsoft Access project, you do receive another prompt for the parameter.


MORE INFORMATION

Steps to Reproduce Behavior

  1. Open the sample database Northwind.mdb.


  2. Create a new query in Design view and add the Orders table.


  3. Drag the OrderDate field to the QBE grid and set Sort to Ascending.


  4. Enter the following criteria in the OrderDate column:
    Between [Start Date] And [End Date]
    NOTE: You must define the data type of both [Start Date] and [End Date]. To do so, click Parameters on the Query menu.


  5. Click Run on the Query menu and enter the following values when prompted for the Start Date and End Date:
    08/01/96

    08/31/96
    Note that you receive a list of the orders for August 1996.


  6. Press SHIFT+F9 to refresh. Note that you are not prompted for the parameter again.


  7. Close the Northwind database.


  8. Open a Microsoft Access project based on a Microsoft SQL Server database.


  9. On the File menu, point to Get External Data, and then click Import.


  10. In the Import dialog box, browse to the Northwind database, select it, and click Import.


  11. In the Database window, click Tables under Objects, select the Orders table, and then click OK. The Orders table is imported into your project.


  12. In the Database window, click Stored Procedures under Objects, and then click New.


  13. In the new stored prodedure window, delete all the existing text and type the following:


  14. 
       Create Procedure ParameterTest
    
          (
             @startdate datetime,
             @enddate datetime
          )
    
       As
       SELECT IDENTITYCOL, CustomerID, EmployeeID, OrderDate
       FROM Orders
       WHERE (OrderDate BETWEEN @startdate and @enddate)
    
          return 
  15. Save the new ParameterTest procedure and close it.


  16. In the Database window, click Stored Procedures under Objects, double-click ParameterTest, and then enter the following values when prompted for the start date and end date:
    08/01/96

    08/31/96
    Note that you receive a list of the orders for August 1996.


  17. Press Shift+F9 to refresh.

    Note that you are asked for the start and end dates again.


Additional query words: Input prb


Keywords          : kbdta 
Version           : WINDOWS:2000
Platform          : WINDOWS 
Issue type        : kbprb 

Last Reviewed: May 13, 1999