ACC: "Too few parameters. Expected 1" Error Message

This article was previously published under Q105522
This article has been archived. It is offered "as is" and will no longer be updated.
SYMPTOMS
Advanced: Requires expert coding, interoperability, and multiuser skills.

When you run a parameter query in Visual Basic (or Access Basic), you mayreceive one of the following error messages.

In Microsoft Access 7.0 and 97


Too few parameters. Expected 1

In Microsoft Access 1.x and 2.0


# parameters were expected, but only 0 were supplied.

This article assumes that you are familiar with Visual Basic forApplications and with creating Microsoft Access applications using theprogramming tools provided with Microsoft Access. For more informationabout Visual Basic for Applications, please refer to your version of the"Building Applications with Microsoft Access" manual.

NOTE: Visual Basic for Applications is called Access Basic in MicrosoftAccess versions 1.x and 2.0. For more information about Access Basic,please refer to the "Introduction to Programming" manual in MicrosoftAccess version 1.x or the "Building Applications" manual in MicrosoftAccess version 2.0.
CAUSE
You receive one of these error messages if you do not set the values of allthe parameters in the parameter query in Visual Basic (or Access Basic).
STATUS
This behavior is by design.
MORE INFORMATION
This section contains an example of the syntax you use to set the values ofa parameter, the sample code to create a query to set the values of aparameter, and the sample code to create a function to set the values of aparameter in parameter queries.

NOTE: You have to explicitly assign the parameter in DAO; you do not haveto explicitly assign the parameter with the DoCmd.OpenQuery (orDoCmdOpenQuery in Microsoft Access 1.x and 2.0). The reason for this isthat DAO uses low-level operations that give you more flexibility (that is,you can assign a variable to a parameter rather than a forms reference) butyou have to do the housekeeping that Microsoft Access does behind thescenes with DoCmd actions. On the other hand, the DoCmd actions operate ata higher level than DAO. When executing a DoCmd action, Microsoft Accessmakes some assumptions about what to do with parameters--you don't have anyflexibility 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 followingsyntax.

In Microsoft Access 7.0 and 97

   Dim MyDB As Database   Dim MyQDef As QueryDef				

   Set MyDB = CurrentDB()   Set MyQDef = MyDB.QueryDefs("Parameter Query")   MyQDef![Forms!Form Name!ControlName] = Forms![Form Name]![ControlName]				

In Microsoft Access 1.x and 2.0

NOTE: In the following sample code, an underscore (_) is used as aline-continuation character. Remove the underscore from the end of theline when re-creating this code.
   Dim MyDB As Database   Dim MyQDef As QueryDef				

   Set MyDB = CurrentDB()   Set MyQDef = MyDB.OpenQueryDef("Parameter Query")   MyQDef![Forms!Form Name!ControlName] = Forms![Form _      Name]![ControlName]				

In the examples, the definition variable, the exclamation point, and theparameter, which is enclosed in brackets, are to the left of the equalsign. Note that if the form name or control name in a form referencecontains spaces, it is usually enclosed in brackets. Do not include thebrackets if you are setting the value of the form reference parameter.However, do include the brackets if you are referencing the form listed tothe 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 thequery, create a module and enter the following code:

In Microsoft Access 7.0 and 97

   Dim MyDB As Database, MyQDef As QueryDef   Set MyDB = CurrentDb()   Set MyQDef = MyDB.QueryDefs("Parameter Query")   MyQDef![Please enter date:] = #8/8/94#				

In Microsoft Access 1.x and 2.0

   Dim MyDB As Database   Dim MyQDef As QueryDef   Set MyDB = CurrentDB()   Set MyQDef = MyDB.OpenQueryDef("Parameter Query")   MyQDef![Please enter date:] = "#12/12/93#"				

NOTE: If you have more than one parameter in the query, add a linesimilar 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 databaseNorthwind.mdb (or NWIND.MDB in Microsoft Access 1.x or 2.0).

To create a function that sets the value of a parameter in a parameterquery, follow these steps:
  1. Open the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x or 2.0) and create the following query:
          Query: Customer Orders Parameter Query      -----------------------------------------------------------------      Type: Select Query      Field: CustomerID  (or Customer ID in Microsoft Access 1.x or 2.0)         Table: Orders         Criteria: [Forms]![Search Form]![Customer To Find]      Field: OrderID     (or Order ID in Microsoft Access 1.x or 2.0)         Table: Orders      Field: OrderDate  (or Order Date in Microsoft Access 1.x or 2.0)         Table: Orders						
  2. Create the following form based on the Orders table:
          Form: Search Form      --------------------------------      Text box:         ControlName: Customer To Find         ControlSource: CustomerID      Command button:         ControlName: Button0         Caption: ParamQD         OnClick: =ParamQD()						

    NOTE: The OnClick property is called the OnPush property in Microsoft Access version 1.x.
  3. Create a module and add the following code.

    NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.
          '****************************************************************      ' Declarations Section      '****************************************************************      Option Compare Database      Option Explicit      '****************************************************************      ' Function ParamQD()      '      ' Purpose: To demonstrate how to set the value of a parameter that      '          references a form.      '****************************************************************      Function ParamQD()         Dim MyDB As Database         Dim MyQDef As QueryDef         Dim MyDyna As Recordset    '(in Microsoft Access 7.0 and 97 only)         'Dim MyDyna As Dynaset     '(in Microsoft Access 1.x and 2.0 only)         Set MyDB = CurrentDB()         Set MyQDef = MyDB.QueryDefs("Customer Orders Parameter Query")         '(above line in Microsoft Access 7.0 and 97 only)         'Set MyQDef = MyDB.OpenQueryDef("Customer Orders Parameter_         ' Query")         '(above line in Microsoft Access 1.x and 2.0 only)         ' Set the value of the parameter.         MyQDef![Forms!Search Form!Customer To Find] = Forms![Search _            Form]![Customer To Find]         ' Create the recordset (or dynaset).         Set MyDyna = MyQDef.OpenRecordset()  '(in Microsoft Access 7.0 and                                              ' 97 only)         ' Set MyDyna = MyQDef.CreateDynaset()'(in Microsoft Access 1.x                                              ' and 2.0 only)         MyDyna.MoveLast         MsgBox MyDyna.RecordCount         MyDyna.Close         MyQDef.Close      End Function						
When you enter a CustomerID on the Orders form and choose the ParamQDbutton, the ParamQD() function runs and a message box is displayedindicating how many orders that customer has.
REFERENCES
For more information about setting the values of a parameter, search theHelp Index for "parameter queries, creating" and then view the availabletopics.

too few parameters expected 1 2 3 4 5 6 7 8 9 0
Properties

Article ID: 105522 - Last Review: 12/04/2015 09:46:57 - Revision: 2.1

Microsoft Access 1.0 Standard Edition, Microsoft Access 1.1 Standard Edition, Microsoft Access 2.0 Standard Edition, Microsoft Access 95 Standard Edition, Microsoft Access 97 Standard Edition

  • kbnosurvey kbarchive kberrmsg kbprb kbprogramming KB105522
Feedback
ERROR: at System.Diagnostics.Process.Kill() at Microsoft.Support.SEOInfrastructureService.PhantomJS.PhantomJSRunner.WaitForExit(Process process, Int32 waitTime, StringBuilder dataBuilder, Boolean isTotalProcessTimeout)