Too few parameters. Expected 1 error.

  • 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
 
Access can figure out what the parameter is when the query is run in the
grid. Then Access will inform the Jet Engine what it is.

However, when the same query is run in code, you must tell the Jet engine
what the
parameter is yourself.

This is the slickest way to do it:

With queries that contain parameters that are all references to
controls on open forms, you can simulate the expression service that Access
provides when the queries are run through the user interface, as follows:

Set db = CurrentDb
Set qdf = db.QueryDefs("MyQuery")
Dim prm As Parameter

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rs = qdf.OpenRecordset(dbOpenDynaset)
' or qdf.Execute dbFailOnError
 
Back
Top