How to use a saved cross-tab query in a report?

[Richard Hollenbeck]

I am very sorry about the (almost) re-post, but you will see that my first
question wasn't very clear; I have another question I posted this morning
called, "in DAO: Run time error 3061 Too few parameters...."

I have read many articles on the web about how to make a dynamic report
based on a cross-tab query. But for some reason mine never works right.

First, my saved query's criteria is the data in an open form's combo box.
So the resulting data is limited to about 30-45 records out of several
thousand records. The "parameters" data is properly filled in at the saved
query and it matches the criteria in the design view. When running the
query by itself it correctly limits the records to match the data in that
aforementioned combo box. So far, so good.

Now I try to build my report based on this query. It should look kind of
like a spreadsheet but it will span a few pages deep and a few pages wide,
depending on how many fields are generated and how many records are pulled.
I have no way of knowing either the number of records or fields at this
time. It will depend on many things. Sorry I'm being cryptic, but it is
that it is a grades program with varying number of students and varying
number of assignments.

So my saved query is called qryTableOfGrades.

I will want to cycle through all the fields in the query to assign field
names to the columns in the report, as is typically shown on many web sites.
I start by setting up a DAO database:

*-*-*

Dim rs As DAO.RecordSet
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryTableOfGrades;")

*-*-*

Should work right? Nope! "Run-time error '3061': Too few parameters.
Expected 0."

Should I take off the parameters in the saved query and put the parameters
into the report instead with a WHERE clause?

This is a very old question. I asked about six months ago and never got an
answer that worked.

Maybe something obvious is wrong but it isn't obvious to me. I'm missing it
completely.

Can I send somebody a copy of my program zipped? This is a live program
with real students so I will have to make a copy and delete students in the
copy to protect their privacy and I'll add dummy names llike Fred Flintstone
instead.

[spier]

On Jun 10, 8:29 pm, "Richard Hollenbeck"
<[e-mail]:

I am very sorry about the (almost) re-post, but you will see that my first
question wasn't very clear; I have another question I posted this morning
called, "in DAO: Run time error 3061 Too few parameters...."

I have read many articles on the web about how to make a dynamic report
based on a cross-tab query. But for some reason mine never works right.

First, my saved query's criteria is the data in an open form's combo box.
So the resulting data is limited to about 30-45 records out of several
thousand records. The "parameters" data is properly filled in at the saved
query and it matches the criteria in the design view. When running the
query by itself it correctly limits the records to match the data in that
aforementioned combo box. So far, so good.

Now I try to build my report based on this query. It should look kind of
like a spreadsheet but it will span a few pages deep and a few pages wide,
depending on how many fields are generated and how many records are pulled.
I have no way of knowing either the number of records or fields at this
time. It will depend on many things. Sorry I'm being cryptic, but it is
that it is a grades program with varying number of students and varying
number of assignments.

So my saved query is called qryTableOfGrades.

I will want to cycle through all the fields in the query to assign field
names to the columns in the report, as is typically shown on many web sites.
I start by setting up a DAO database:

*-*-*

Dim rs As DAO.RecordSet
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryTableOfGrades;")

*-*-*

Should work right? Nope! "Run-time error '3061': Too few parameters.
Expected 0."

Should I take off the parameters in the saved query and put the parameters
into the report instead with a WHERE clause?

This is a very old question. I asked about six months ago and never got an
answer that worked.

Maybe something obvious is wrong but it isn't obvious to me. I'm missing it
completely.

Can I send somebody a copy of my program zipped? This is a live program
with real students so I will have to make a copy and delete students in the
copy to protect their privacy and I'll add dummy names llike Fred Flintstone
instead.

Hi,
just some hints that may help!

- Declare parameters in your query: while in design view, query >
parameters copy&paste from the query grade, e.g. [Form]![FormName]!
[comboBox], with appropriate data type;

- in DAO, supply an argument for each parameter:

Dim qdf As QueryDef
....
Set qdf = db.QueryDefs("qryTableOfGrades")
With qdf
.Parameters("[Form]![FormName]![comboBox]") = [Form]!
[FormName]![comboBox] ' odd ;-)
...
End With
Set rs = qdf.OpenRecordset
' now your recordset should be ok.
for each field in rs.fields
....

Set qdf = Nothing
Set rs = Nothing
...

HTH
spier

[Richard Hollenbeck]

Many thanks to you (and to Allen Browne who answered my original post). I
don't have time this morning to try your examples but I will take my Access
Developer's Handbook to work with me this morning to study QueryDefs. Then
I will study both of your replies again and try it.

I will post the results.

Rich

"spier" <sp*****@yahoo.comwrote in message
news:11*********************@c77g2000hse.googlegro ups.com...

On Jun 10, 8:29 pm, "Richard Hollenbeck"
<[e-mail]:

>I am very sorry about the (almost) re-post, but you will see that my
first
question wasn't very clear; I have another question I posted this morning
called, "in DAO: Run time error 3061 Too few parameters...."

I have read many articles on the web about how to make a dynamic report
based on a cross-tab query. But for some reason mine never works right.

First, my saved query's criteria is the data in an open form's combo
box.
So the resulting data is limited to about 30-45 records out of several
thousand records. The "parameters" data is properly filled in at the
saved
query and it matches the criteria in the design view. When running the
query by itself it correctly limits the records to match the data in that
aforementioned combo box. So far, so good.

Now I try to build my report based on this query. It should look kind of
like a spreadsheet but it will span a few pages deep and a few pages
wide,
depending on how many fields are generated and how many records are
pulled.
I have no way of knowing either the number of records or fields at this
time. It will depend on many things. Sorry I'm being cryptic, but it is
that it is a grades program with varying number of students and varying
number of assignments.

So my saved query is called qryTableOfGrades.

I will want to cycle through all the fields in the query to assign field
names to the columns in the report, as is typically shown on many web
sites.
I start by setting up a DAO database:

*-*-*

Dim rs As DAO.RecordSet
Dim db As DAO.Database

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT * FROM qryTableOfGrades;")

*-*-*

Should work right? Nope! "Run-time error '3061': Too few parameters.
Expected 0."

Should I take off the parameters in the saved query and put the
parameters
into the report instead with a WHERE clause?

This is a very old question. I asked about six months ago and never got
an
answer that worked.

Maybe something obvious is wrong but it isn't obvious to me. I'm missing
it
completely.

Can I send somebody a copy of my program zipped? This is a live program
with real students so I will have to make a copy and delete students in
the
copy to protect their privacy and I'll add dummy names llike Fred
Flintstone
instead.

Hi,
just some hints that may help!

- Declare parameters in your query: while in design view, query >
parameters copy&paste from the query grade, e.g. [Form]![FormName]!
[comboBox], with appropriate data type;

- in DAO, supply an argument for each parameter:

Dim qdf As QueryDef
...
Set qdf = db.QueryDefs("qryTableOfGrades")
With qdf
.Parameters("[Form]![FormName]![comboBox]") = [Form]!
[FormName]![comboBox] ' odd ;-)
...
End With
Set rs = qdf.OpenRecordset
' now your recordset should be ok.
for each field in rs.fields
...

Set qdf = Nothing
Set rs = Nothing
...

HTH
spier

[Richard Hollenbeck]

Hi,

just some hints that may help!

- Declare parameters in your query: while in design view, query >
parameters copy&paste from the query grade, e.g. [Form]![FormName]!
[comboBox], with appropriate data type;

- in DAO, supply an argument for each parameter:

Dim qdf As QueryDef
...
Set qdf = db.QueryDefs("qryTableOfGrades")
With qdf
.Parameters("[Form]![FormName]![comboBox]") = [Form]!
[FormName]![comboBox] ' odd ;-)
...
End With
Set rs = qdf.OpenRecordset
' now your recordset should be ok.
for each field in rs.fields
...

Set qdf = Nothing
Set rs = Nothing
...

HTH
spier

Thanks. The following seems to be working:
*-*-*
Set db = CurrentDb
Set qdf = db.QueryDefs("qryTableOfGrades")

With qdf
.Parameters("[Forms]!frmSelectCourse]![cboSelectCourse") =
[Forms]![frmSelectCourse]![cboSelectCourse]
End With

Set rs = qdf.OpenRecordset()
*-*-*

I don't know why it is working but it does cycle through the records.

Now for my next question. . . (in a new post.)