INSERT INTO from query

[Lee]

Hello

The following statement works:

CurrentDb.Execute ("INSERT INTO tblPayments (salesconsultantid) SELECT
salesconsultantid FROM tblContracts"), dbFailOnError

But when I change tblContracts to qryWeeklystatementsbase:

CurrentDb.Execute ("INSERT INTO tblPayments (salesconsultantid) SELECT
salesconsultantid FROM qryWeeklystatementsbase"), dbFailOnError

I get a too-few parameters error.

The following statement also works:

DoCmd.OpenQuery "qryWeeklystatementsbase"

indicating that query qryWeeklystatementsbase exists and I typed the name
in correctly.

There is definitely a field in qryWeeklystatementsbase called
salesconsultantid. Does this mean you can't insert from a query? I can
always copy the query itself into the code and try that but I was hoping to
avoid that. Or am I possibly doing something else wrong?

 

[Marshall Barton]

The following statement works:

CurrentDb.Execute ("INSERT INTO tblPayments (salesconsultantid) SELECT
salesconsultantid FROM tblContracts"), dbFailOnError

But when I change tblContracts to qryWeeklystatementsbase:

CurrentDb.Execute ("INSERT INTO tblPayments (salesconsultantid) SELECT
salesconsultantid FROM qryWeeklystatementsbase"), dbFailOnError

I get a too-few parameters error.

The following statement also works:

DoCmd.OpenQuery "qryWeeklystatementsbase"

indicating that query qryWeeklystatementsbase exists and I typed the name
in correctly.

There is definitely a field in qryWeeklystatementsbase called
salesconsultantid. Does this mean you can't insert from a query? I can
always copy the query itself into the code and try that but I was hoping to
avoid that. Or am I possibly doing something else wrong?

It sounds like the query has a form reference in some
field's criteria. When Access processes the OpenQuery (or
RunSQL) method, it somehow figures out the query parameters
and arranges things so the query can run with the values for
the parameters.

The Execute method bypasses Access and sends the query as is
straight to Jet. That means you have to use one of two ways
to resolve the parameters befor executing the query. One
way is to construct the SQL statment using code that
concatenates the values or the parameters into the SQL.

The other way is to use code to set the value of each item
in the QueryDef's Parameters collection and use the Execute
method on the QueryDef object.

If I'm right about the form reference criteria and you are
trying to Execute an SQL statement based on a parameter
query, neither of those approaches will work. I think you
will have to reconstruct the qryWeeklystatementsbase query,
including concatenating the parameter values, as part of the
INSERT INTO statement and Execute the resolved SQL string.