ACC: "Too few parameters" Using AddAllToList() Function

• Microsoft Access versions 7.0, 97

SYMPTOMS

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

When you attempt to open a parameter query from code, such as is done in the AddAllToList() function provided in the Developer Solutions sample application (Solutions.mdb), you may receive the following error message:

   Too few parameters. Expected 1.

CAUSE

The combo box or list box's RowSource property is set to an SQL statement or query object name that is a parameter query. The parameter query may be used to limit the selection of items in the list based on a selection in a different list or control on the form. When opening a recordset on a query in Visual Basic, which the AddAllToList() function attempts to do, the parameters must be filled in explicitly.

RESOLUTION

Modify the AddAllToList() function to explicitly fill in the parameters for the recordset being used.

The steps to reproduce the behavior are provided below using the SelectProduct combo box on the EditProducts form in the Developer Solutions sample application. Further information is provided demonstrating how you can work around this behavior by creating a copy of the AddAllToList() function and modifying it to explicitly fill the parameters to the LimitProductList query used to populate the SelectProduct combo box.

MORE INFORMATION

Steps to Reproduce Behavior

1. Open the Developer Solutions sample application (Solutions.mdb), and open the EditProducts form in Design view.

2. Change the RowSourceType property for the SelectProduct combo box in the header section from Table/Query to AddAllToList by typing "AddAllToList" (without the quotation marks) in the RowSourceType property box.

3. View the form in Form view. Note that you receive the following error message:

         Too few parameters. Expected 1.
   

   Close all forms. Open the module modSolutionsUtilities (AddAllToListModule in Microsoft Access 7.0) in Design view, and select the AddAllToList procedure.

4. Highlight the entire function and press CTRL+C to copy the contents to the Clipboard.

5. Press CTRL+END to go to the bottom of the module and press CTRL+V to paste the Clipboard contents into the module to create a duplicate of the function.

6. On the Edit menu, click Replace and type the following information in the Replace dialog box

         Find What: AddAllToList
         Replace With: FillSelectProduct
         Search: Current Procedure
   

   and then Click the Replace All button.

7. Click OK to the message "The specified region has been searched. 17 replacements were made," and then close the Replace dialog box.

8. Modify the section of code that reads

         ' Open the recordset defined in the RowSource property.
         Set dbs = CurrentDb()
         Set rst = dbs.OpenRecordset(ctl.RowSource, dbOpenSnapshot)
   

   to read:

         ' Open the recordset defined in the RowSource property.
         Set dbs = CurrentDb()
         Dim qdf As QueryDef
         Set qdf = dbs.QueryDefs(ctl.RowSource)
         qdf.Parameters("Forms!EditProducts!SelectCategory") = _
                         Forms!EditProducts!SelectCategory
         Set rst = qdf.OpenRecordset(dbOpenSnapshot)
   
         NOTE: If your RowSource property uses an SQL statement instead of a
         saved query object, you can replace the line above that reads
   
            Set qdf = dbs.QueryDefs(ctl.RowSource)
   
         to read:
   
            Set qdf = dbs.CreateQueryDef("", ctl.RowSource)
   
   

9. Open the EditProducts form and change the RowSourceType property of the SelectProduct combo box to "FillSelectProduct."

REFERENCES