Too few parameters when executing query

  • Thread starter Thread starter graeme34 via AccessMonster.com
  • Start date Start date
G

graeme34 via AccessMonster.com

Hi could any body tell me why a query would run when manually opened, yet
when called from a procedure it fails saying it is expecting 1 parameter..
Here is the calling code..
DoCmd.DeleteObject acTable, "tblTempGoodsSent"
CurrentDb.QueryDefs("qryMakeDespatchTable").Execute (dbFailOnError)
Here is the qryMakeDespatchQuery..
SELECT qryDespatchSubLine.SalesOrderNumber, qryDespatchSubLine.[Product Code],
qryDespatchSubLine.Description, qryDespatchSubLine.Price, qryDespatchSubLine.
QuantityOrdered, qryDespatchSubLine.Outstanding, qryDespatchSubLine.[Quantity
Sent], qryDespatchSubLine.VATRate INTO tblTempGoodsSent
FROM qryDespatchSubLine
WHERE (((qryDespatchSubLine.SalesOrderNumber)=Forms!frmChooseOrderNumber!
txtOrderNumberChoice) And ((qryDespatchSubLine.Outstanding)>0));

The query works fine when opened by double clicking the object name from the
query objects list but not from within the code??
 
graeme34 via AccessMonster.com said:
Hi could any body tell me why a query would run when manually opened, yet
when called from a procedure it fails saying it is expecting 1 parameter..
Here is the calling code..
DoCmd.DeleteObject acTable, "tblTempGoodsSent"
CurrentDb.QueryDefs("qryMakeDespatchTable").Execute (dbFailOnError)
Here is the qryMakeDespatchQuery..
SELECT qryDespatchSubLine.SalesOrderNumber, qryDespatchSubLine.[Product
Code],
qryDespatchSubLine.Description, qryDespatchSubLine.Price,
qryDespatchSubLine.
QuantityOrdered, qryDespatchSubLine.Outstanding,
qryDespatchSubLine.[Quantity
Sent], qryDespatchSubLine.VATRate INTO tblTempGoodsSent
FROM qryDespatchSubLine
WHERE (((qryDespatchSubLine.SalesOrderNumber)=Forms!frmChooseOrderNumber!
txtOrderNumberChoice) And ((qryDespatchSubLine.Outstanding)>0));

The query works fine when opened by double clicking the object name from
the
query objects list but not from within the code??
Click to expand...


You cannot use this:
Forms!frmChooseOrderNumber!txtOrderNumberChoice
Although it will work when you run the query with the Access GUI. You will
have to calculate this variable in your vba code to build up an appropriate
SQL string,then execute with CurrentDb.Execute strSQL, dbFailOnError

I would also note that in general, you shouldn't be deleting the table then
use a 'make-table query' since it does not copy an of the indexes and you
are left with a table with no primary key.
 
hi Brian

Just after I'd posted the message I had realised what I had done wrong. You
are right the query does not need the Forms!frmChoose........... parameter.
This query is used to hold information for the despatch note and for the
confirmation of delivery details, it does not really need any indexes or
primary key as it is only a temporary storage point to allow the updating of
the underlying tables as the original query was rather complex and ended up a
crosstab query (eventually, I think ! ) and therefore not updatable, so I had
to hold the data in the temp table.


Brian said:
Hi could any body tell me why a query would run when manually opened, yet
when called from a procedure it fails saying it is expecting 1 parameter..
Click to expand...
[quoted text clipped - 16 lines]
the
query objects list but not from within the code??
Click to expand...

You cannot use this:
Forms!frmChooseOrderNumber!txtOrderNumberChoice
Although it will work when you run the query with the Access GUI. You will
have to calculate this variable in your vba code to build up an appropriate
SQL string,then execute with CurrentDb.Execute strSQL, dbFailOnError

I would also note that in general, you shouldn't be deleting the table then
use a 'make-table query' since it does not copy an of the indexes and you
are left with a table with no primary key.
Click to expand...
 
Back
Top