Query in VBA

S

Steve

Hi,

Using code, I am attempting to run an Update query.

The query links my main 'tracking' table to a query that
uses a parameter to reference a form.

I get a 3061 too few parameters error, so I have attempted
to set the parameter using the QueryDef for the linked
query. I still get the error.

Can anyone help?

Cheers,
Steve.

Here is my code;

Dim qdef As QueryDef
Set qdef = db.QueryDefs(strFilter)

qdef.Parameters("forms!frmWorkflow!cmbQueue_List") =
strSelected_Queue

' User wants to update the status.
db.Execute "UPDATE " & strFilter & " INNER JOIN
tblMPR_Tracker ON " & strFilter & ".MRUN_Ref =
tblMPR_Tracker.MRUN_Ref " & _
"SET tblMPR_Tracker.Action = 'Exported to Excel',
tblMPR_Tracker.Last_Action_By = '" & Environ("UserName")
& "', " & _
"tblMPR_Tracker.Last_Action_Date = Date(), " & _
"tblMPR_Tracker.Last_Action_Time = Time();"
 
T

Tom Ellison

Dear Steve:

Parameters are things you reference in the query that do not exist as
columns in the tables and queries referenced by the SQL. Without
knowing what is in strFilter and what column names you have, I cannot
tell what this might be.

You can make it easy to tell that yourself. Dimension (Dim) a
variable strSQL. Replace the code "dbExecute" with "strSQL = "
then add a line "dbExecute strSQL". Put a breakpoint on this last
line. Run the program so it stops at the breakpoint and examine
strSQL in the immediate pane. If necessary, copy and paste the SQL
into a scratch query and see how it works there. This allows you to
find and fix little errors you might have made.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts
 
S

Steve

Hello again Tom!

I have tried putting the generated SQL into a query and it
runs from there.

strFilter is the name of the query that contains the
parameter. The SQL for the parameter query is below.

Thanks,
Steve.

SELECT tblMPR_Tracker.MRUN_Ref, tblMPR_Tracker.Queue_Type,
tblMPR_Tracker.MPR, tblMPR_Tracker.MPR_Date,
tblMPR_Tracker.Batch_UserID, tblMPR_Tracker.MRA_Returned,
tblMPR_Tracker.Log_Number
FROM tblMPR_Tracker
WHERE (((tblMPR_Tracker.Queue_Type)=[forms]![frmWorkflow]!
[cmbQueue_List]) AND ((tblMPR_Tracker.Batch_UserID) Is Not
Null) AND ((tblMPR_Tracker.MRA_Returned) Is Not Null) AND
((tblMPR_Tracker.Complete)=False))
ORDER BY tblMPR_Tracker.Batch_UserID,
tblMPR_Tracker.MRA_Returned, tblMPR_Tracker.MPR;
 
T

Tom Ellison

Dear Steve:

You've confirmed the SQL is good and runs as a query, but doesn't run
from code. Does the query ask for parameters when it runs as a query?
If so, you must fill in the parameter values in code when you run it
in code. I cannot run in code and prompt for parameters. That would
give give the error you see.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello again Tom!

I have tried putting the generated SQL into a query and it
runs from there.

strFilter is the name of the query that contains the
parameter. The SQL for the parameter query is below.

Thanks,
Steve.

SELECT tblMPR_Tracker.MRUN_Ref, tblMPR_Tracker.Queue_Type,
tblMPR_Tracker.MPR, tblMPR_Tracker.MPR_Date,
tblMPR_Tracker.Batch_UserID, tblMPR_Tracker.MRA_Returned,
tblMPR_Tracker.Log_Number
FROM tblMPR_Tracker
WHERE (((tblMPR_Tracker.Queue_Type)=[forms]![frmWorkflow]!
[cmbQueue_List]) AND ((tblMPR_Tracker.Batch_UserID) Is Not
Null) AND ((tblMPR_Tracker.MRA_Returned) Is Not Null) AND
((tblMPR_Tracker.Complete)=False))
ORDER BY tblMPR_Tracker.Batch_UserID,
tblMPR_Tracker.MRA_Returned, tblMPR_Tracker.MPR;

-----Original Message-----
Dear Steve:

Parameters are things you reference in the query that do not exist as
columns in the tables and queries referenced by the SQL. Without
knowing what is in strFilter and what column names you have, I cannot
tell what this might be.

You can make it easy to tell that yourself. Dimension (Dim) a
variable strSQL. Replace the code "dbExecute" with "strSQL = "
then add a line "dbExecute strSQL". Put a breakpoint on this last
line. Run the program so it stops at the breakpoint and examine
strSQL in the immediate pane. If necessary, copy and paste the SQL
into a scratch query and see how it works there. This allows you to
find and fix little errors you might have made.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts




.
Click to expand...
Click to expand...
 
S

Steve

It runs fine as a query, not prompting for a parameter.

It references a Combo box on a form.

Oh well, the weekend begins here, ill look at it again
monday :)

Thanks,
Steve.
-----Original Message-----
Dear Steve:

You've confirmed the SQL is good and runs as a query, but doesn't run
from code. Does the query ask for parameters when it runs as a query?
If so, you must fill in the parameter values in code when you run it
in code. I cannot run in code and prompt for parameters. That would
give give the error you see.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


Hello again Tom!

I have tried putting the generated SQL into a query and it
runs from there.

strFilter is the name of the query that contains the
parameter. The SQL for the parameter query is below.

Thanks,
Steve.

SELECT tblMPR_Tracker.MRUN_Ref, tblMPR_Tracker.Queue_Type,
tblMPR_Tracker.MPR, tblMPR_Tracker.MPR_Date,
tblMPR_Tracker.Batch_UserID, tblMPR_Tracker.MRA_Returned,
tblMPR_Tracker.Log_Number
FROM tblMPR_Tracker
WHERE (((tblMPR_Tracker.Queue_Type)=[forms]! [frmWorkflow]!
[cmbQueue_List]) AND ((tblMPR_Tracker.Batch_UserID) Is Not
Null) AND ((tblMPR_Tracker.MRA_Returned) Is Not Null) AND
((tblMPR_Tracker.Complete)=False))
ORDER BY tblMPR_Tracker.Batch_UserID,
tblMPR_Tracker.MRA_Returned, tblMPR_Tracker.MPR;

-----Original Message-----
Dear Steve:

Parameters are things you reference in the query that
Click to expand...
do
not exist as
columns in the tables and queries referenced by the
Click to expand...
SQL.
Without
knowing what is in strFilter and what column names you have, I cannot
tell what this might be.

You can make it easy to tell that yourself. Dimension (Dim) a
variable strSQL. Replace the code "dbExecute" with "strSQL = "
then add a line "dbExecute strSQL". Put a breakpoint
Click to expand...
on
this last
line. Run the program so it stops at the breakpoint
Click to expand...
and
examine
strSQL in the immediate pane. If necessary, copy and paste the SQL
into a scratch query and see how it works there. This allows you to
find and fix little errors you might have made.

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts


On Fri, 17 Sep 2004 03:57:45 -0700, "Steve"

Hi,

Using code, I am attempting to run an Update query.

The query links my main 'tracking' table to a query that
uses a parameter to reference a form.

I get a 3061 too few parameters error, so I have attempted
to set the parameter using the QueryDef for the linked
query. I still get the error.

Can anyone help?

Cheers,
Steve.

Here is my code;

Dim qdef As QueryDef
Set qdef = db.QueryDefs(strFilter)

qdef.Parameters("forms!frmWorkflow!cmbQueue_List") =
strSelected_Queue

' User wants to update the status.
db.Execute "UPDATE " & strFilter & " INNER JOIN
tblMPR_Tracker ON " & strFilter & ".MRUN_Ref =
tblMPR_Tracker.MRUN_Ref " & _
"SET tblMPR_Tracker.Action = 'Exported to Excel',
tblMPR_Tracker.Last_Action_By = '" & Environ ("UserName")
& "', " & _
"tblMPR_Tracker.Last_Action_Date = Date(), " & _
"tblMPR_Tracker.Last_Action_Time = Time();"

.
Click to expand...
Click to expand...

.
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Query error, too few parameters 0
Updating a query def 1
Running query from VBA with parameter 1
Query within a Query - SQL in VBA 7
SQL In Code help! 2
Running Update Query in VBA fails 5
Parameter for Database in a make table querie 1
Delete Query syntax in VBA querydef 9

Top