ACC2000: "Too Few Parameters. Expected 1" Error Message

When you run a parameter query in Visual Basic for Applications, you may receive the following error message.

You receive the error message if you do not set the values of all the parameters in the parameter query in Visual Basic for Applications.

Microsoft provides programming examples for illustration only, without warranty either expressed or implied. This includes, but is not limited to, the implied warranties of merchantability or fitness for a particular purpose. This article assumes that you are familiar with the programming language that is being demonstrated and with the tools that are used to create and to debug procedures. Microsoft support engineers can help explain the functionality of a particular procedure, but they will not modify these examples to provide added functionality or construct procedures to meet your specific requirements. NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

This section contains an example of the syntax you use to set the values of a parameter, the sample code to create a query to set the values of a parameter, and the sample code to create a function to set the values of a parameter in parameter queries.

NOTE: You have to explicitly assign the parameter in DAO; you do not have to explicitly assign the parameter with the DoCmd.OpenQuery. The reason for this is that DAO uses low-level operations that give you more flexibility (that is, you can assign a variable to a parameter rather than a form's reference) but you have to do the housekeeping that Access does behind the scenes with DoCmd actions. On the other hand, the DoCmd actions operate at a higher level than DAO. When executing a DoCmd action, Microsoft Access makes some assumptions about what to do with parameters--you don't have any flexibility in making them accept a different value.

Syntax to Set the Value of a Parameter

To set the value of a parameter that references a form, use the following syntax. In the example, the definition variable, the exclamation point (!), and the parameter, which is enclosed in brackets (( )), are to the left of the equal sign (=). Note that if the form name or control name in a form reference contains spaces, it is usually enclosed in brackets. Do not include the brackets if you are setting the value of the form reference parameter. However, do include the brackets if you are referencing the form listed to the right of the equal sign.

Code to Create a Query to Set the Value of a Parameter

To create a query that prompts you to enter the date when you run the query, create a module and enter the following code: NOTE: If you have more than one parameter in the query, add a line similar to the last line in the code for each parameter.

Sample Function to Set the Value of a Parameter

The following example uses the Orders table from the sample database Northwind.mdb.

To create a function that sets the value of a parameter in a parameter query, follow these steps:

1. Open the sample database Northwind.mdb and create the following query:

      Query: qryCustomerOrdersParameter
      ------------------------------------------------------
      Type: Select Query
      Field: CustomerID
      Table: Orders
      Criteria: [Forms]![frmSearch]![txtCustomerToFind]
   
      Field: OrderID)
      Table: Orders
   
      Field: OrderDate
      Table: Orders
   					

2. Create the following form based on the Orders table:

      Form: Search Form
      --------------------------------
      Text box:
      ControlName: txtCustomerToFind
      ControlSource: CustomerID
      
      Command button:
      ControlName: cmdParamQD
      Caption: Parameter Query
      OnClick: =ParamQD()
   					

3. Create a module and type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

4. Type the following procedure:

   '****************************************************************
   ' Function ParamQD()
   '
   ' Purpose: To demonstrate how to set the value of a parameter that
   '          references a form.
   '****************************************************************
   
   Function ParamQD()
   
       Dim dbSample As Database
       Dim qdfMyQuery As QueryDef
       Dim rstCountOrders As Recordset
       Dim strSearchName As String
   
       Set dbSample = CurrentDb()
   
       Set qdfMyQuery = dbSample.QueryDefs("qryCustomerOrdersParameter")
       
       ' Test for a value in the textbox
       If Not IsNull(Forms![frmSearch]![txtCustomerToFind]) Then
       
           ' Set the value of the parameter.
           strSearchName = Forms![frmSearch]![txtCustomerToFind]
           qdfMyQuery![Forms!frmSearch!txtCustomerToFind] = strSearchName
   
           ' Create the recordset (or dynaset).
           Set rstCountOrders = qdfMyQuery.OpenRecordset()
           If rstCountOrders.RecordCount = 0 Then
               MsgBox "No records for " & strSearchName
           Else
               rstCountOrders.MoveLast
               MsgBox rstCountOrders.RecordCount
           End If
           rstCountOrders.Close
       Else
           MsgBox "Please enter a CustomerId in the textbox"
       End If
       
       qdfMyQuery.Close
       dbSample.Close
       
   End Function
   					

When you enter a CustomerID on the Orders form and click the Parameter Query button, the ParamQD() function runs, and a message box is displayed indicating how many orders that customer has.

For more information about setting the values of a parameter, click Microsoft Access Help on the Help menu, type parameter queries, creating in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.