Query and SQL

[Guest]

I'm writing an SQL query to insert data from a query (qrySolQtyShipped)
that's already created into a table Forecast. The query qrySolQtyShipped
looks up parameters from a form. qrySolQtyShipped runs fine by itself. But
when I run the query with sql, I'm getting an error: "Run-time error '3061':
Too few parametrs. Expect 2."
My sql on a button click:
sql = "INSERT INTO Forecast (PartNumber)"
sql = sql & " SELECT [qrySolQtyShipped].[PN] FROM [qrySolQtyShipped];"
db.Execute sql, dbFailOnError

I can't see why it's not working except maybe because qrySolQtyShipped needs
to look up info from the form, and the sql is expect the parameter to be
passed along?
Any help is very much appreciated.

 

[Marshall Barton]

I'm writing an SQL query to insert data from a query (qrySolQtyShipped)
that's already created into a table Forecast. The query qrySolQtyShipped
looks up parameters from a form. qrySolQtyShipped runs fine by itself. But
when I run the query with sql, I'm getting an error: "Run-time error '3061':
Too few parametrs. Expect 2."
My sql on a button click:
sql = "INSERT INTO Forecast (PartNumber)"
sql = sql & " SELECT [qrySolQtyShipped].[PN] FROM [qrySolQtyShipped];"
db.Execute sql, dbFailOnError

I can't see why it's not working except maybe because qrySolQtyShipped needs
to look up info from the form, and the sql is expect the parameter to be
passed along?

It's really Access that tries to resolve the query's
parameters. The Execute method bypasses Access to go
directly to the Jet database engine to run the query, so
parameter resolution never takes place. Qhile the Execute
method is the preferred method of running queries, you would
have to rearrange your query structure and VBA to utilize
it's adavantages.

In this case, I think you shoud be able to do what you want
by using RunSQL (which utilizes Access to manage running the
query).