Microsoft KB Archive/209203

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

Article ID: 209203

Article Last Modified on 10/11/2006

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209203



Advanced: Requires expert coding, interoperability, and multiuser skills.



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

Too few parameters. Expected 1



CAUSE
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.



MORE INFORMATION
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. Dim dbSample As Database Dim qdfSample As QueryDef Set dbSample = CurrentDB Set qdfSample = dbSample .QueryDefs("qryParameterQuery") qdfSample![Forms!FormName!ControlName] = _ Forms![FormName]![ControlName] 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: Dim dbSample As Database, qdfSample As QueryDef Set dbSample = CurrentDb Set qdfSample = dbSample.QueryDefs("qryParameterQuery") qdfSample![Please enter date:] = #8/8/94# 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:   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   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   Create a module and type the following line in the Declarations section if it is not already there: Option Explicit   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 </ol>

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.

<div class="references_section">