Too few parameters. Expected 1

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

Hi
I am trying to execute a INSERT query via code and have this weird thing
happening.

If a category has not been entered into the criteria form, the INSERT query
works fine.

INSERT INTO Orders (CustID, OrderDate, Qty, Publication) SELECT 1,
#02/08/04#, 20, PubID from SubPub1;
The SQL for SubPub1 is:

SELECT tblPub.PubID
FROM tblPub
GROUP BY tblPub.PubID, tblPub.PubStatus
HAVING (((tblPub.PubStatus)=1));

If a category has been entered into the criteria form, the INSERT statement
is exactly the same except that is is getting PubID from SubPub4.
The SQL for SubPub4 is:

SELECT tblPub.PubID
FROM tblPub INNER JOIN tblPubDetails ON tblPub.PubID = tblPubDetails.fkPubID
WHERE (((tblPub.PubStatus)=1) AND ((tblPubDetails.Category) Like "*" &
[forms]![ClientForm]![cmbCat]));

Both queries (SubPub1 and SubPub2) produce a single column of results
containing the PubID yet when I try to execute the second one I get the Too
few parameters error.

Any help would be greatly appreciated.

Sandy
 
Hi,


If you use the query designer or DoCmd, the parameters
FORMS!FormName!ControlName are solved FOR YOU, automatically, but if you use
CurrentDb to open the recordset, these parameters are NOT solved for you,
you have to specify them (through an eval, as example):

Dim qdf As QueryDef: Set qdf=CurrentDb.QueryDefs("queryname")
DIm p as DAO.parameter
For each p in qdf.parameters
p.value = eval(p.name)
Next p


Dim rst as DAO.Recordset
Set rst=qdf.Open( ... )



see http://www.mvps.org/access/queries/qry0013.htm also.


Hoping it may help,
Vanderghast, Access MVP
 
Back
Top