QueryDef vs. Recordset Errors

G

Guest

I keep getting a "Too few parameters error" for the "Set rst line =" when
using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset but then
the "Cost = rst.fields" line gives me the error "Items not found in this
collection".
 
R

Rick Brandt

NChris said:
I keep getting a "Too few parameters error" for the "Set rst line ="
when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset
but then the "Cost = rst.fields" line gives me the error "Items not
found in this collection".

Post the SQL of qryTotalCurrentCosts.
 
G

Guest

Here is the SQL for qryTotalCurrentCosts:

SELECT qryCurrentDetail.CostingID, Sum(qryCurrentDetail.[Current Cost]) AS
TotalCurrentCost
FROM qryCurrentDetail
GROUP BY qryCurrentDetail.CostingID
HAVING (((qryCurrentDetail.CostingID)=[forms]![frmCostingMain].[CostingID]));

Any insight is appreciated, thanks!

Rick Brandt said:
NChris said:
I keep getting a "Too few parameters error" for the "Set rst line ="
when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset
but then the "Cost = rst.fields" line gives me the error "Items not
found in this collection".

Post the SQL of qryTotalCurrentCosts.
 
S

SusanV

Your SQL has no spaces in TotalCurrentCost but your VBA does (Cost =
rst.Fields("Total Current Cost"))
--
hth,
SusanV

NChris said:
Here is the SQL for qryTotalCurrentCosts:

SELECT qryCurrentDetail.CostingID, Sum(qryCurrentDetail.[Current Cost]) AS
TotalCurrentCost
FROM qryCurrentDetail
GROUP BY qryCurrentDetail.CostingID
HAVING
(((qryCurrentDetail.CostingID)=[forms]![frmCostingMain].[CostingID]));

Any insight is appreciated, thanks!

Rick Brandt said:
NChris said:
I keep getting a "Too few parameters error" for the "Set rst line ="
when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset
but then the "Cost = rst.fields" line gives me the error "Items not
found in this collection".

Post the SQL of qryTotalCurrentCosts.
 
G

Guest

Sorry, I tried removing the spaces to see if that is what was causing the
issue. When I posted this I just hadn't changed them both back. Whether I
have spaces or no spaces in both the SQL and the code I still get the errors.

SusanV said:
Your SQL has no spaces in TotalCurrentCost but your VBA does (Cost =
rst.Fields("Total Current Cost"))
--
hth,
SusanV

NChris said:
Here is the SQL for qryTotalCurrentCosts:

SELECT qryCurrentDetail.CostingID, Sum(qryCurrentDetail.[Current Cost]) AS
TotalCurrentCost
FROM qryCurrentDetail
GROUP BY qryCurrentDetail.CostingID
HAVING
(((qryCurrentDetail.CostingID)=[forms]![frmCostingMain].[CostingID]));

Any insight is appreciated, thanks!

Rick Brandt said:
NChris wrote:
I keep getting a "Too few parameters error" for the "Set rst line ="
when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset
but then the "Cost = rst.fields" line gives me the error "Items not
found in this collection".

Post the SQL of qryTotalCurrentCosts.
 
R

Roger Carlson

In fact, you could probably get rid of the qryTotalCurrentCosts query and
use the DSum() function:

Cost = DSum("Current Cost", "qryCurrentDetail", "CostingID= " &
[forms]![frmCostingMain].[CostingID]


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger Carlson said:
Actually, the DLookup domain aggregate function would work better for you:

Cost = DLookup("Total Current Cost", "qryTotalCurrentCosts")

No need to create a recordset or anything.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

NChris said:
I keep getting a "Too few parameters error" for the "Set rst line =" when
using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset but
then
the "Cost = rst.fields" line gives me the error "Items not found in this
collection".
 
G

Guest

I always forget about DLookup thanks. However, when I use this now I get the
error "Syntax Error (missing operator) in query expression 'Total Current
Cost'. I checked the query and made sure that the spaces matched the spaces
in the code but I'm not sure what this error is about. Any ideas?

Roger Carlson said:
Actually, the DLookup domain aggregate function would work better for you:

Cost = DLookup("Total Current Cost", "qryTotalCurrentCosts")

No need to create a recordset or anything.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

NChris said:
I keep getting a "Too few parameters error" for the "Set rst line =" when
using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset but
then
the "Cost = rst.fields" line gives me the error "Items not found in this
collection".
 
G

Guest

Awesome, the DSUM function works great. Thanks!!!!!

Roger Carlson said:
In fact, you could probably get rid of the qryTotalCurrentCosts query and
use the DSum() function:

Cost = DSum("Current Cost", "qryCurrentDetail", "CostingID= " &
[forms]![frmCostingMain].[CostingID]


--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

Roger Carlson said:
Actually, the DLookup domain aggregate function would work better for you:

Cost = DLookup("Total Current Cost", "qryTotalCurrentCosts")

No need to create a recordset or anything.

--
--Roger Carlson
MS Access MVP
Access Database Samples: www.rogersaccesslibrary.com
Want answers to your Access questions in your Email?
Free subscription:
http://peach.ease.lsoft.com/scripts/wa.exe?SUBED1=ACCESS-L

NChris said:
I keep getting a "Too few parameters error" for the "Set rst line =" when
using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset but
then
the "Cost = rst.fields" line gives me the error "Items not found in this
collection".
 
M

Marshall Barton

First, the query is probably not quite correct. The HAVING
should be WHERE.

You can open a recordset based on the QueryDef after you
resolve the parameter:

Dim db As Database
Dim qdf As QueryDef
Dim orm As Parameter

Set db = CurrentDb()
Set qdf - db.QueryDefs!qryTotalCurrentCosts
For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm
Set rs = qdf.OpenRecordset()
. . .

But, I think Roger's DSum is way more direct without using a
saved query or recordset.
--
Marsh
MVP [MS Access]

Here is the SQL for qryTotalCurrentCosts:

SELECT qryCurrentDetail.CostingID, Sum(qryCurrentDetail.[Current Cost]) AS
TotalCurrentCost
FROM qryCurrentDetail
GROUP BY qryCurrentDetail.CostingID
HAVING (((qryCurrentDetail.CostingID)=[forms]![frmCostingMain].[CostingID]));


Rick Brandt said:
NChris said:
I keep getting a "Too few parameters error" for the "Set rst line ="
when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As Currency
Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost] from
qryTotalCurrentCosts")
Cost = rst.Fields("Total Current Cost")
rst.Close
End If

End Function

Any ideas how I can fix this? I am referencing the Microsoft DAO 3.6
library. I've also tried the datatype QueryDef in place of recordset
but then the "Cost = rst.fields" line gives me the error "Items not
found in this collection".
 
R

Rick Brandt

NChris said:
Here is the SQL for qryTotalCurrentCosts:

SELECT qryCurrentDetail.CostingID, Sum(qryCurrentDetail.[Current
Cost]) AS TotalCurrentCost
FROM qryCurrentDetail
GROUP BY qryCurrentDetail.CostingID
HAVING
(((qryCurrentDetail.CostingID)=[forms]![frmCostingMain].[CostingID]));

Any insight is appreciated, thanks!

Your query has a form reference in it. Those do not work when a query is
used to create a RecordSet object unless your code explicitly declares them.

When you run queries in the Access GUI the "Expression Service" resolves
form references like that for you. Your VBA code does not get that
advantage so it needs additional "help".
 
D

David W. Fenton

I keep getting a "Too few parameters error" for the "Set rst line
=" when using the following code:

Option Compare Database

Public Function Cost(strType As String, CostingID As Long) As
Currency Dim rst As DAO.Recordset

If strType = "CURRENT" Then
Set rst = CurrentDb.OpenRecordset("Select [Total Current Cost]
from
qryTotalCurrentCosts")

You cannot have unresolved parameters in a recordset.
 

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

Access 2003 and SQL Server 2005 and DAO recordset 1
no duplicate record 6
Error '3061' 2
Orderby doesnt go away 4
findfirst problem 11
Sum in Querydef 10
Type mismatch error 2
Word Merge and 3

Top