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

ID: Q209203

The information in this article applies to:

Microsoft Access 2000

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, including, but not limited to, the implied warranties of merchantability and/or fitness for a particular purpose. This article assumes that you are familiar with the programming language being demonstrated and the tools used to create and debug procedures. Microsoft support professionals 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 needs. If you have limited programming experience, you may want to contact a Microsoft Certified Solution Provider or the Microsoft fee-based consulting line at (800) 936-5200. For more information about Microsoft Certified Solution Providers, please see the following page on the World Wide Web:

http://www.microsoft.com/mcsp/

For more information about the support options available from Microsoft, please see the following page on the World Wide Web:

http://www.microsoft.com/support/supportnet/overview/overview.asp

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you need to reference the Microsoft DAO 3.6 Object Library.

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

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.

REFERENCES

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.

Additional query words: expected supplied prb

Keywords : kberrmsg kbprg kbdta PgmObj QryParm
Version : WINDOWS:2000
Platform : WINDOWS
Issue type : kbprb