QueryDef vs. Recordset Errors

  • Thread starter Thread starter Guest
  • Start date Start date
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".
 
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".
Click to expand...

Post the SQL of qryTotalCurrentCosts.
 
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".
Click to expand...

Post the SQL of qryTotalCurrentCosts.
Click to expand...
 
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".
Click to expand...

Post the SQL of qryTotalCurrentCosts.
Click to expand...
Click to expand...
 
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.
Click to expand...
Click to expand...
Click to expand...
 
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".
Click to expand...
Click to expand...
 
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".
Click to expand...
Click to expand...
 
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".
Click to expand...
Click to expand...
Click to expand...
 
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".
Click to expand...
Click to expand...
Click to expand...
 
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!
Click to expand...

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".
 
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")
Click to expand...

You cannot have unresolved parameters in a recordset.
 
Back
Top