Microsoft KB Archive/141620

= ACC: "Too few parameters" Using AddAllToList Function =

Article ID: 141620

Article Last Modified on 1/19/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q141620



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

When you try to open a parameter query from code, 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.



Steps to Reproduce Behavior
 Open the Developer Solutions sample application (Solutions.mdb), and open the EditProducts form in Design view. Change the RowSourceType property for the SelectProduct combo box in the header section from Table/Query to AddAllToList by typing AddAllToList in the RowSourceType property box. 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. Highlight the entire function and press CTRL+C to copy the contents to the Clipboard. 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.  On the Edit menu, click Replace and type the following information in the Replace dialog box

<pre class="fixed_text">     Find What: AddAllToList Replace With: FillSelectProduct Search: Current Procedure

and then Click the Replace All button. </li> Click OK to the message "The specified region has been searched. 17 replacements were made," and then close the Replace dialog box.</li>  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) </li> Open the EditProducts form and change the RowSourceType property of the SelectProduct combo box to "FillSelectProduct."</li></ol>

<div class="references_section">