Query error, too few parameters

[avtuvy]

Access 2003 VBA

My project includes a form with a combo box, text from the combo box is an input to a query. when I run the query in design view it prompts me for the parameter name and the results are correct, when I run it from the form using the input from the combo box I get error 3061, too few parameters. I added a watch for qdf and I do see that the parameter from the combo box is entered.
Please help

my VBA code

Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("qrySerialNumber")
    qdf.Parameters("ServerName2") = "'" & Forms![frmServers_1]![cmbServerName] & "'"
    
    Set rs = db.OpenRecordset("qrySerialNumber", dbOpenForwardOnly)

My query

SELECT dbo_TBL_ServerInformation.SerialNumber, dbo_TBL_ServerInformation.DiskSize, dbo_TBL_ServerInformation.WarrantyExpiration
FROM dbo_TBL_ServerInformation INNER JOIN dbo_TBL_Location ON dbo_TBL_ServerInformation.LocationID=dbo_TBL_Location.LocationID
WHERE (((dbo_TBL_ServerInformation.ServerName)=ServerName2));

 

[jdraw]

Access 2003 VBA

My project includes a form with a combo box, text from the combo box is an input to a query. when I run the query in design view it prompts me for the parameter name and the results are correct, when I run it from the form using the input from the combo box I get error 3061, too few parameters. I added a watch for qdf and I do see that the parameter from the combo box is entered.
Please help

my VBA code

Dim qdf As DAO.QueryDef
    Set qdf = db.QueryDefs("qrySerialNumber")
    qdf.Parameters("ServerName2") = "'" & Forms![frmServers_1]![cmbServerName] & "'"
    
    Set rs = db.OpenRecordset("qrySerialNumber", dbOpenForwardOnly)

My query

SELECT dbo_TBL_ServerInformation.SerialNumber, dbo_TBL_ServerInformation.DiskSize, dbo_TBL_ServerInformation.WarrantyExpiration
FROM dbo_TBL_ServerInformation INNER JOIN dbo_TBL_Location ON dbo_TBL_ServerInformation.LocationID=dbo_TBL_Location.LocationID
WHERE (((dbo_TBL_ServerInformation.ServerName)=ServerName2));

A couple of points:

- quite often the too few parameters message is the result of a misspelling of a name
- your code extract doesn't show how "db" was dimmed
- your query only deals with 1 table, so why do you use the Inner Join on
dbo_TBL_Location?

Your query

SELECT dbo_TBL_ServerInformation.SerialNumber
, dbo_TBL_ServerInformation.DiskSize
, dbo_TBL_ServerInformation.WarrantyExpiration
FROM dbo_TBL_ServerInformation [I][COLOR="Magenta"]INNER JOIN dbo_TBL_Location 
ON dbo_TBL_ServerInformation.LocationID=dbo_TBL_Location.LocationID[/COLOR][/I]
WHERE (((dbo_TBL_ServerInformation.ServerName)=[COLOR="red"]ServerName2[/COLOR]));

The magenta stuff could be removed in my view

I think the red stuff should look like this in the query
WHERE (((dbo_TBL_ServerInformation.ServerName)= "'" & Forms![frmServers_1]![cmbServerName] & "'"

I think you are using the querydef incorrectly and I've done some searching.

I found the following embedded in a longer text at this location:
http://msdn.microsoft.com/en-us/library/aa160564(office.11).aspx

you'll get runtime error 3061, "Too few parameters. Expected 1." on the line that tries to open the recordset. What's going on here?

The answer is that you're invoking the Jet engine in a different context here, and that makes all the difference. When you get data from a parameter query that uses a form to supply the parameter via the Access user interface, as in the earlier example, Access can evalute the expression involved and supply a value to Jet. When you get data from a parameter query that uses a form to supply the parameter via VBA, instead of through a form, the bits of Access that manage user interface matters aren't involved. Consequently, Jet is passed the string "[Forms]![frmSelectCountry]![cboCountry]" instead of the value in cboCountry. Because Jet doesn't know how to evaluate the expression, it can't open the recordset.

The solution is to supply the Jet engine with the parameter. You can do this by using a QueryDef object to open the query, then explicitly setting the parameter to the value before executing the query:

Dim db As DAO.Database
Dim qdf As DAO.QueryDef
Dim rst As DAO.Recordset

Set db = CurrentDb
Set qdf = db.QueryDefs("qryCustomersSelect")
qdf.Parameters(0) = _
Forms!frmSelectCountry!cboCountry
Set rst = qdf.OpenRecordset

Do Until rst.EOF
Debug.Print rst!CustomerID
rst.MoveNext
Loop

rst.Close
qdf.Close