Using a QueryDef in a RecordSet

  • Thread starter Thread starter Bob B.
  • Start date Start date
B

Bob B.

I am trying to set up a querydef to allow me to pull the top 5 worst scrap
producers from a table based on user entered starting and ending dates and an
upper and lower range for total pounds produced. Once the query is created, I
will use additional code to pull the top 5 products from a recordset of the
query results.

The querydef appears to work fine as the query is created and the data is
flawless. However, when I try to issue the Set rst = qdf.OpenRecordset(), I
get the following error:

Run-time error ‘3061’:
Too few parameters. Expected 4.

I do this in other applications I have created and there is no issue. I
have even copied the code from them and replaced it with the specifics for
this application only to yield the same results.

Below is my exact code. Any help that can be provided as to what I am
doing wrong and that may help me correct this issue would be greatly
appreciated. Thank you in advance for your help.



Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set db = CurrentDb()

'Build a query called ScrapNumbers and select the all fields from
Physical Property Data.
'Order the list by ScrapRate and attach that query to the Currrent
Database.

Set qdf = db.CreateQueryDef("ScrapNumbers", "SELECT [PHYSICAL PROPERTY
DATA].[PRODUCT NAME], [PHYSICAL PROPERTY DATA].[LOT NUMBER], [PHYSICAL
PROPERTY DATA].[DATE PRODUCED], [PHYSICAL PROPERTY DATA].CUSTOMER, [PHYSICAL
PROPERTY DATA].[POUNDS PROD], [PHYSICAL PROPERTY DATA].[RM LBS USED],
[PHYSICAL PROPERTY DATA].EXTRUDER_NUMBER, [POUNDS PROD]/[RM LBS USED] AS
ScrapRate FROM [PHYSICAL PROPERTY DATA]WHERE ((([PHYSICAL PROPERTY
DATA].[DATE PRODUCED]) Is Not Null And ([PHYSICAL PROPERTY DATA].[DATE
PRODUCED]) Between [Forms]![frmTopScrapProducersReport]![fldStartingDate] And
[Forms]![frmTopScrapProducersReport]![fldEndingDate]) AND (([PHYSICAL
PROPERTY DATA].[POUNDS PROD])>[Forms]![frmTopScrapProducersReport]![fldFrom]
And ([PHYSICAL PROPERTY DATA].[POUNDS
PROD])<[Forms]![frmTopScrapProducersReport]![fldTo]) AND (([PHYSICAL PROPERTY
DATA].[RM LBS USED])<>0))ORDER BY [POUNDS PROD]/[RM LBS USED] DESC;")

Set rst = qdf.OpenRecordset()

rst.Close
db.QueryDefs.Delete qdf.Name
db.Close
 
On Mon, 7 Apr 2008 07:19:01 -0700, Bob B.

There is a standard way to debug this: break into the code and in the
Immediate window (Ctrl+G) write:
?qdf.Parameters(0).Name
and change the index to 1, 2, and 3 for the other parameters.

More than likely they are form references, which indeed in this case
you have to specify in code. For example:
qdf.parameters(0).value = Forms!SomeForm!SomeControl

-Tom.
 
Bob B. said:
I am trying to set up a querydef to allow me to pull the top 5 worst
scrap
producers from a table based on user entered starting and ending dates and
an
upper and lower range for total pounds produced. Once the query is
created, I
will use additional code to pull the top 5 products from a recordset of
the
query results.

The querydef appears to work fine as the query is created and the data is
flawless. However, when I try to issue the Set rst = qdf.OpenRecordset(),
I
get the following error:

Run-time error ‘3061’:
Too few parameters. Expected 4.

I do this in other applications I have created and there is no issue. I
have even copied the code from them and replaced it with the specifics for
this application only to yield the same results.

Below is my exact code. Any help that can be provided as to what I am
doing wrong and that may help me correct this issue would be greatly
appreciated. Thank you in advance for your help.



Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set db = CurrentDb()

'Build a query called ScrapNumbers and select the all fields from
Physical Property Data.
'Order the list by ScrapRate and attach that query to the Currrent
Database.

Set qdf = db.CreateQueryDef("ScrapNumbers", "SELECT [PHYSICAL PROPERTY
DATA].[PRODUCT NAME], [PHYSICAL PROPERTY DATA].[LOT NUMBER], [PHYSICAL
PROPERTY DATA].[DATE PRODUCED], [PHYSICAL PROPERTY DATA].CUSTOMER,
[PHYSICAL
PROPERTY DATA].[POUNDS PROD], [PHYSICAL PROPERTY DATA].[RM LBS USED],
[PHYSICAL PROPERTY DATA].EXTRUDER_NUMBER, [POUNDS PROD]/[RM LBS USED] AS
ScrapRate FROM [PHYSICAL PROPERTY DATA]WHERE ((([PHYSICAL PROPERTY
DATA].[DATE PRODUCED]) Is Not Null And ([PHYSICAL PROPERTY DATA].[DATE
PRODUCED]) Between [Forms]![frmTopScrapProducersReport]![fldStartingDate]
And
[Forms]![frmTopScrapProducersReport]![fldEndingDate]) AND (([PHYSICAL
PROPERTY DATA].[POUNDS
PROD])>[Forms]![frmTopScrapProducersReport]![fldFrom]
And ([PHYSICAL PROPERTY DATA].[POUNDS
PROD])<[Forms]![frmTopScrapProducersReport]![fldTo]) AND (([PHYSICAL
PROPERTY
DATA].[RM LBS USED])<>0))ORDER BY [POUNDS PROD]/[RM LBS USED] DESC;")

Set rst = qdf.OpenRecordset()

rst.Close
db.QueryDefs.Delete qdf.Name
db.Close
Click to expand...

Without intervention by Access (as when you use RunSQL), DAO views your
references to controls on the form "frmTopScrapProducersReport" as
parameters, and expects you to fill in the values for those parameters. One
way to handle this is to get Access to fill in the parameters in the
querydef for you:

Dim qdf As DAO.QueryDef
Dim prm As DAO.Parameter
Dim rst As DAO.Recordset

Set qdf = ...

For Each prm In qdf.Parameters
prm.Value = Eval(prm.Name)
Next prm

Set rst = qdf.OpenRecordset

Another way to do it, since you are building the query SQL on the fly, is to
embed the values of those form controls directly in the SQL, so there are no
parameters to be evaluated. If you do that, you don't need to create even a
temporary QueryDef; you can then write:

Dim strSQL As String

strSQL = "SELECT ..."

Set rst = db.OpenRecordset(strSQL)
 
I'm trying to figure out exactly what you're trying to do here.

First of all, to find the Top 5, you can use the TOP predicate and sort the
query. So you don't need any additional code to find them. This may
eliminate the need for creating a Recordset and thus eliminate the Parameter
problem.

On my website (www.rogersaccesslibrary.com), is a small Access database
sample called "TopQuery.mdb"
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TopQuery.doc
which illustrates how to do this.

Secondly, I don't see why you're creating the query via code. Seems to me,
you can just create your query as a saved query What are you doing with the
Top 5 after you've found them? That might explain some things.

Regardless, if you need to open a query with parameters as a DAO recordset.
You need to use the Parameters Collection. I also have a sample called:
"TooFewParameters.mdb"
http://www.rogersaccesslibrary.com/download3.asp?SampleName=TooFewParameters.mdb
which illustrates that with some variations.

If you really want to create your querydef via code, you should fill the
*value* from the form control, rather than the *name* of the control. Thus,
your SQL statement would look something like this:

strSQL ="SELECT [PRODUCT NAME], [LOT NUMBER], [DATE PRODUCED], " & _
"CUSTOMER, [POUNDS PROD], [RM LBS USED], EXTRUDER_NUMBER, " & _
"[POUNDS PROD]/[RM LBS USED] AS ScrapRate " & _
"FROM [PHYSICAL PROPERTY DATA] " & _
"WHERE [DATE PRODUCED] Is Not Null And [DATE PRODUCED] Between #" & _
[Forms]![frmTopScrapProducersReport]![fldStartingDate]
"# And #" & _
[Forms]![frmTopScrapProducersReport]![fldEndingDate] & _
"# AND [POUNDS PROD]> " & _
[Forms]![frmTopScrapProducersReport]![fldFrom] & _
" And [POUNDS PROD] < " & _
[Forms]![frmTopScrapProducersReport]![fldTo]
" AND [RM LBS USED]<>0 ORDER BY [POUNDS PROD]/[RM LBS USED] DESC;"

Set qdf = db.CreateQueryDef("ScrapNumbers", strSQL)

Note: I've cleaned up the query and formatted it for clarity. I also
assigned the SQL statement to a string variable and used that variable in
the Querydef parameter. It's a better method because you can use a
Debug.Print strSQL command to see exactly what the string looks like.

But notice that the parameters ([forms]!...etc) are NOT within the quoted
string. The *values* in those fields will be inserted. So you won't need
to deal with the Parameters collection when basing a recordset on it. I've
got another sample called "CreateQueries2.mdb"
http://www.rogersaccesslibrary.com/download3.asp?SampleName=CreateQueries2.mdb
which illustrates this.

--
--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

Bob B. said:
I am trying to set up a querydef to allow me to pull the top 5 worst
scrap
producers from a table based on user entered starting and ending dates and
an
upper and lower range for total pounds produced. Once the query is
created, I
will use additional code to pull the top 5 products from a recordset of
the
query results.

The querydef appears to work fine as the query is created and the data is
flawless. However, when I try to issue the Set rst = qdf.OpenRecordset(),
I
get the following error:

Run-time error '3061':
Too few parameters. Expected 4.

I do this in other applications I have created and there is no issue. I
have even copied the code from them and replaced it with the specifics for
this application only to yield the same results.

Below is my exact code. Any help that can be provided as to what I am
doing wrong and that may help me correct this issue would be greatly
appreciated. Thank you in advance for your help.



Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset

Set db = CurrentDb()

'Build a query called ScrapNumbers and select the all fields from
Physical Property Data.
'Order the list by ScrapRate and attach that query to the Currrent
Database.

Set qdf = db.CreateQueryDef("ScrapNumbers", "SELECT [PHYSICAL PROPERTY
DATA].[PRODUCT NAME], [PHYSICAL PROPERTY DATA].[LOT NUMBER], [PHYSICAL
PROPERTY DATA].[DATE PRODUCED], [PHYSICAL PROPERTY DATA].CUSTOMER,
[PHYSICAL
PROPERTY DATA].[POUNDS PROD], [PHYSICAL PROPERTY DATA].[RM LBS USED],
[PHYSICAL PROPERTY DATA].EXTRUDER_NUMBER, [POUNDS PROD]/[RM LBS USED] AS
ScrapRate FROM [PHYSICAL PROPERTY DATA]WHERE ((([PHYSICAL PROPERTY
DATA].[DATE PRODUCED]) Is Not Null And ([PHYSICAL PROPERTY DATA].[DATE
PRODUCED]) Between [Forms]![frmTopScrapProducersReport]![fldStartingDate]
And
[Forms]![frmTopScrapProducersReport]![fldEndingDate]) AND (([PHYSICAL
PROPERTY DATA].[POUNDS
PROD])>[Forms]![frmTopScrapProducersReport]![fldFrom]
And ([PHYSICAL PROPERTY DATA].[POUNDS
PROD])<[Forms]![frmTopScrapProducersReport]![fldTo]) AND (([PHYSICAL
PROPERTY
DATA].[RM LBS USED])<>0))ORDER BY [POUNDS PROD]/[RM LBS USED] DESC;")

Set rst = qdf.OpenRecordset()

rst.Close
db.QueryDefs.Delete qdf.Name
db.Close
Click to expand...
 
First of all, I would like to thank everyone who replied to this post, I
learned something from each response. Secondly, I would like to thank Roger
for pointing out that I didn't explicitly reference my values. I did that and
this application works perfectly. I would also like to thank you for the web
site, I will look into the Top predicate next as a way to improve my code.
Thank you again, people like you are what makes this site work for so well
for many people.
 
Warning!

this Chris guy is not credible.
He does not know the basics of MS Access.

-Aaron



You have at least two typos in your query. You need to add a space before
your WHERE clause and ORDER BY clause. Your query is so difficult to read,
I'll let you find the other typos if there are any.

ScrapRate FROM [PHYSICAL PROPERTY DATA]WHERE ((([PHYSICAL

should be:

ScrapRate FROM [PHYSICAL PROPERTY DATA] WHERE ((([PHYSICAL

DATA].[RM LBS USED])<>0))ORDER BY [POUNDS PROD]/[RM LBS USED]

should be:

DATA].[RM LBS USED])<>0)) ORDER BY [POUNDS PROD]/[RM LBS USED]

Chris
Microsoft MVP




I am trying to set up a querydef to allow me to pull the top 5 worst scrap
producers from a table based on user entered starting and ending dates and an
upper and lower range for total pounds produced. Once the query is created, I
will use additional code to pull the top 5 products from a recordset of the
query results.
Click to expand...
The querydef appears to work fine as the query is created and the data is
flawless. However, when I try to issue the Set rst = qdf.OpenRecordset(), I
get the following error:
Click to expand...
Run-time error '3061':
Too few parameters. Expected 4.
Click to expand...
I do this in other applications I have created and there is no issue. I
have even copied the code from them and replaced it with the specifics for
this application only to yield the same results.
Click to expand...
Below is my exact code. Any help that can be provided as to what I am
doing wrong and that may help me correct this issue would be greatly
appreciated. Thank you in advance for your help.
Click to expand...
Dim db As Database
Dim qdf As QueryDef
Dim rst As Recordset
Click to expand...
Set db = CurrentDb()
Click to expand...
'Build a query called ScrapNumbers and select the all fields from
Physical Property Data.
'Order the list by ScrapRate and attach that query to the Currrent
Database.
Click to expand...
Set qdf = db.CreateQueryDef("ScrapNumbers", "SELECT [PHYSICAL PROPERTY
DATA].[PRODUCT NAME], [PHYSICAL PROPERTY DATA].[LOT NUMBER], [PHYSICAL
PROPERTY DATA].[DATE PRODUCED], [PHYSICAL PROPERTY DATA].CUSTOMER, [PHYSICAL
PROPERTY DATA].[POUNDS PROD], [PHYSICAL PROPERTY DATA].[RM LBS USED],
[PHYSICAL PROPERTY DATA].EXTRUDER_NUMBER, [POUNDS PROD]/[RM LBS USED] AS
ScrapRate FROM [PHYSICAL PROPERTY DATA]WHERE ((([PHYSICAL PROPERTY
DATA].[DATE PRODUCED]) Is Not Null And ([PHYSICAL PROPERTY DATA].[DATE
PRODUCED]) Between [Forms]![frmTopScrapProducersReport]![fldStartingDate] And
[Forms]![frmTopScrapProducersReport]![fldEndingDate]) AND (([PHYSICAL
PROPERTY DATA].[POUNDS PROD])>[Forms]![frmTopScrapProducersReport]![fldFrom]
And ([PHYSICAL PROPERTY DATA].[POUNDS
PROD])<[Forms]![frmTopScrapProducersReport]![fldTo]) AND (([PHYSICAL PROPERTY
DATA].[RM LBS USED])<>0))ORDER BY [POUNDS PROD]/[RM LBS USED] DESC;")
Click to expand...
Set rst = qdf.OpenRecordset()
Click to expand...
rst.Close
db.QueryDefs.Delete qdf.Name
db.Close
Click to expand...
Click to expand...
 
Back
Top