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

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SYMPTOMS

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

When you run a parameter query in Visual Basic (or Access Basic), you may receive 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.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.

CAUSE

You receive one of these error messages if you do not set the values of all the 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 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 (or DoCmdOpenQuery in Microsoft Access 1.x and 2.0). 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 forms reference) but you have to do the housekeeping that Microsoft 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 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 a line-continuation character. Remove the underscore from the end of the line 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]

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:

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#"

Sample Function to Set the Value of a Parameter

The following example uses the Orders table from the sample database Northwind.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 parameter query, 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
   
   

REFERENCES

For more information about setting the values of a parameter, search the Help Index for "parameter queries, creating" and then view the available topics.