Question About Recordsets and Queries

G

Guest

I have written a fairly complex query that includes a subquery, and also
bases its data off of a value given in an Access form:

SELECT tblUserProjects.UProj_ID, tblBudgetDetail.BD_TASK_ID,
tblActualCost.ActualCost_Date, tblActualCost.ActualCost_Amount,
MSP_RESOURCES.RES_ID, tblBudgets.Budget_UProj_ID

FROM ((tblBudgetDetail INNER JOIN tblActualCost ON
tblBudgetDetail.BD_CCNumber = tblActualCost.ActualCost_CostCodeNum) INNER
JOIN (MSP_RESOURCES INNER JOIN tblUserProjects ON MSP_RESOURCES.PROJ_ID =
tblUserProjects.UProj_PROJ_ID) ON tblActualCost.ActualCost_UProj_Number =
tblUserProjects.UProj_Num) INNER JOIN tblBudgets ON
tblBudgetDetail.Budget_Num = tblBudgets.Budget_Num

WHERE (((MSP_RESOURCES.RES_ID)=(SELECT RES_ID FROM MSP_RESOURCES WHERE
RES_NAME = "Actual Cost" AND PROJ_ID =
[forms]![frmProjectMain]![cboUProj_Proj_ID] )) AND
((tblBudgets.Budget_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

This query runs fine when I open it in normal design view and run it. When
I try to extract data from it with a recordset, however, there is an error:

rsAssignmentsNeeded.Open "SELECT * FROM qryExportActualData2Project", conn,
adOpenStatic, adLockBatchOptimistic

The error states:

"No value given for one or more required parameters."

No matter what combination I try, it errors. Is this because I have
references to objects in a form? Can VBA recordsets simply not handle
queries that retrieve data from forms?

Thanks!

Dustin
 
M

Marshall Barton

Dustin said:
I have written a fairly complex query that includes a subquery, and also
bases its data off of a value given in an Access form:

SELECT tblUserProjects.UProj_ID, tblBudgetDetail.BD_TASK_ID,
tblActualCost.ActualCost_Date, tblActualCost.ActualCost_Amount,
MSP_RESOURCES.RES_ID, tblBudgets.Budget_UProj_ID

FROM ((tblBudgetDetail INNER JOIN tblActualCost ON
tblBudgetDetail.BD_CCNumber = tblActualCost.ActualCost_CostCodeNum) INNER
JOIN (MSP_RESOURCES INNER JOIN tblUserProjects ON MSP_RESOURCES.PROJ_ID =
tblUserProjects.UProj_PROJ_ID) ON tblActualCost.ActualCost_UProj_Number =
tblUserProjects.UProj_Num) INNER JOIN tblBudgets ON
tblBudgetDetail.Budget_Num = tblBudgets.Budget_Num

WHERE (((MSP_RESOURCES.RES_ID)=(SELECT RES_ID FROM MSP_RESOURCES WHERE
RES_NAME = "Actual Cost" AND PROJ_ID =
[forms]![frmProjectMain]![cboUProj_Proj_ID] )) AND
((tblBudgets.Budget_UProj_ID)=[forms]![frmProjectMain]![txtUProj_ID]));

This query runs fine when I open it in normal design view and run it. When
I try to extract data from it with a recordset, however, there is an error:

rsAssignmentsNeeded.Open "SELECT * FROM qryExportActualData2Project", conn,
adOpenStatic, adLockBatchOptimistic

The error states:

"No value given for one or more required parameters."

No matter what combination I try, it errors. Is this because I have
references to objects in a form? Can VBA recordsets simply not handle
queries that retrieve data from forms?
Click to expand...


When you run the query by using an Access mechanism, it
takes cacre of resolving query parameters. When you are
running the query in code, you are responsible for resolving
the parameters. I don't use ADO so I don't know how that
is done, but check VBA Help for how to pass parameters to a
query.
 
O

onedaywhen

Dustin said:
This query runs fine when I open it in normal design view and run it. When
I try to extract data from it with a recordset, however, there is an error:

rsAssignmentsNeeded.Open "SELECT * FROM qryExportActualData2Project", conn,
adOpenStatic, adLockBatchOptimistic

The error states:

"No value given for one or more required parameters."
Click to expand...

What you have is a 'procedure' rather than a 'view', so you need to
execute it while supplying values for its parameters e.g.

rsAssignmentsNeeded.Open("EXECUTE qryExportActualData2Project <<literal
value for cboUProj_Proj_ID parameter here>>, <<literal value for
txtUProj_ID parameter here>>"), conn,adOpenStatic,
adLockBatchOptimistic

As an alternative to literal values in SQL text, use a Command object
creating Parameter objects of explicit types etc. There are plenty of
examples if you google.

Jamie.

--
 
Top