Report based on parameter query

[Amy Blankenship]

I have a report that is based on an ordinary parameter query (i.e. right now
it pops up dialogs asking for three values that it needs for its where
clause). I use this query in several places, so I don't want to make the
parameters dependent on specific form controls, which is the only
information I've been able to find on google on feeding parameters in a
report query's Recordsource.

What I actually want to happen is to make a button in the main switchboard
which, when pressed, will look up those values in a different query I
already have and then use those to populate the parameters for the query and
open the form. I think this means that I'll need a macro that launches some
VBA code. And that's fine, I know how to do that. I also know how to run
the query from code to get the parameter values. If I were just _running_
the parameter query from code, I could also do that. What I don't know how
to do is to pass those parameters to the Report to use for its RecordSource.

Does anyone have any sample code that they'd care to share?

Thanks;

Amy

 

[Klatuu]

If you need to do this from multiple places, I would suggest a form that
would first run the query to retrieve the parameter values, then construct a
string to use as the Where argument in the OpenReport method.

If there is no user input needed for the parameters, then the form could be
hidden.
From where you are now opening the report, open the form instead and let the
form open the report then close itself.

 

[Amy Blankenship]

If you need to do this from multiple places, I would suggest a form that
would first run the query to retrieve the parameter values, then construct
a
string to use as the Where argument in the OpenReport method.

The query is based on a stack of other queries that all need these same
parameters passed in the normal way (as they're being used various places as
well, including from outside Access). In order to rewrite the query in this
way, I'd need to trace my way back through all the other queries it is based
on and rebuild them. Is there an answer to my question as asked? Surely I
_can't_ be the first person to ever need to run this type of report with
real parameters...?

Thanks;

Amy

 

[Klatuu]

The answer to your question is as asked.
When you ask a question, provide the What.
The answers will provide the How.

What is the problem with running the query? Why does it matter where it is
run from.

I understood the question as asked. Did you understand the answer?

 

[Amy Blankenship]

The answer to your question is as asked.
When you ask a question, provide the What.
The answers will provide the How.

What is the problem with running the query? Why does it matter where it
is
run from.

I understood the question as asked. Did you understand the answer?

I don't think you did. The "What" I asked is about passing _real_
parameters to a parameter query as opposed to passing references to form
controls. As I said, the query is based on a stack of other queries that
require _real_ parameters to run, and if I were to go back and recreate them
to use form control references that would cause two problems

1) It would take far more time than I have already wasted trying to figure
this out

2) It would create a maintenance nightmare of having two parallel tracks of
queries that have to be edited every time something changes.

Perhaps I didn't understand your answer, though. Were you suggesting that
there is a way to get the form references somehow _into_ a parameter query
that is expecting actual values as opposed to form references? If so, could
you please clarify, as I am not aware of what the syntax would be to do
that.

Thanks;

Amy

 

[Klatuu]

Are the parameter values always the same? If not, how do you change them? I
think we can make this work for you, but I need to know how you pass the
parameters currently.

 

[Amy Blankenship]

OK, here's the deal, in detail:

I have a separate program that essentially acts as an engine to allows
students to take exams based on content stored in the database, and it
stores information about what they did and when. Ordinarily, all the
reporting is done at the end of an exam based on queries run from the
engine. It takes a fair amount of effort to get an exam to a point where it
would actually be usable through the engine, but we can make a "mock exam"
with considerably less effort, since we don't care about the content
displayed on the screen. I do this in Access forms. This moves the need to
report while we're unit testing into Access, but I don't want to duplicate
all the effort that has already been spent on my queries.

My client just wants to be able to run the mock exam and look at a report
based on the most recent student and whatever exam they took last. However,
the queries need to have a numeric parameter that tells them the student,
the exam, and a boolean parameter that tells them whether the result we're
interested in is from an open session or a closed one.

I can easily determine these parameters by running a query based on the last
response to any question entered by any student and working my way up the
chain. So I want to use the result of that query to enter the parameters
for the query and in so doing suppress the dialogs that ask for the
parameter values. In the interim, I'm just popping up a message box that
tells the user what to enter to get the most recent report ;-).

I've already tried this http://www.mvps.org/access/reports/rpt0014.htm, and
it doesn't seem to use the open recordset, but instead reruns its SQL (and
so you get parameter popups).

Thanks;

Amy

 

[Klatuu]

Thanks, Amy. It appears the query you run requires parameters.
"by running a query based on the last response to any question entered by
any student"

So if I am understanding correctly you need to put in a student id and a
question id to run the query that returns your parameters?

Sorry, but I want to make sure to get this right.

 

[Amy Blankenship]

The query that returns the parameters doesn't need any parameters. It just
looks up the last response from any student, sees who answered it, what
question it was to, what exam it was part of, and whether the exam session
was open or closed. Once that query is run, at present I am popping up a
message box that says "these are the answers to the questions you're about
to be asked" and open the form, which then pops up the dialogs for the
parameters. If I could pass the results directly into the parameters, the
user would never need to know.

-Amy

 

[Klatuu]

Okay, good. Now, I am back to my original suggestion with a new twist.
The form I suggested earlier is still a good idea (IMHO). But, make the
recordsource of the form the query that returns the parameters you want to
use. Use bound controls for the fields to be used as parameters.
Then build a string to use as the Where argument of the OpenReprort method
using the values of the bound controls.

This doesn't require changes to you query.
It takes no user intervention.
You can open the form from anywhere.
If you don't need the user to see it, open it hidden.

 

[Amy Blankenship]

Okay, good. Now, I am back to my original suggestion with a new twist.
The form I suggested earlier is still a good idea (IMHO). But, make the
recordsource of the form the query that returns the parameters you want to
use. Use bound controls for the fields to be used as parameters.
Then build a string to use as the Where argument of the OpenReprort method
using the values of the bound controls.

Those parameters don't appear in the where clause of the top-level query.
They are buried in a variety of underlying queries. Essentially, the query
that is the recordsource for the form is simply compiling information that
has already been filtered in a lot of other queries. Is there a way to say
something like:

[Enter CID] = Forms![MyForm].MyControl
[Enter SID] = Forms![MyForm].MyOtherControl

?

If not, I don't see that your suggestion will work.

Thanks;

Amy

 

[Klatuu]

Those parameters don't appear in the where clause of the top-level query.

I am not talking about Where in your queries. I thought you said the top
level query returns the values you want to use as parameters. Is that not
correct?

If the top level query returns the values you want to use as parameters for
the report, what I suggest will work just fine. I guess I am getting
confused because you said that no input is required to return the values in
the query.


[Enter CID] = Forms![MyForm].MyControl
[Enter SID] = Forms![MyForm].MyOtherControl

Will not work. You can put the form reference directly in a query:

WHERE [CID] = Forms![MyForm]!MyControl


--
Dave Hargis, Microsoft Access MVP

Okay, good. Now, I am back to my original suggestion with a new twist.
The form I suggested earlier is still a good idea (IMHO). But, make the
recordsource of the form the query that returns the parameters you want to
use. Use bound controls for the fields to be used as parameters.
Then build a string to use as the Where argument of the OpenReprort method
using the values of the bound controls.

Those parameters don't appear in the where clause of the top-level query.
They are buried in a variety of underlying queries. Essentially, the query
that is the recordsource for the form is simply compiling information that
has already been filtered in a lot of other queries. Is there a way to say
something like:

[Enter CID] = Forms![MyForm].MyControl
[Enter SID] = Forms![MyForm].MyOtherControl

?

If not, I don't see that your suggestion will work.

Thanks;

Amy

 

[Amy Blankenship]

Those parameters don't appear in the where clause of the top-level query.

I am not talking about Where in your queries. I thought you said the top
level query returns the values you want to use as parameters. Is that not
correct?

If the top level query returns the values you want to use as parameters
for
the report, what I suggest will work just fine. I guess I am getting
confused because you said that no input is required to return the values
in
the query.


[Enter CID] = Forms![MyForm].MyControl
[Enter SID] = Forms![MyForm].MyOtherControl

Will not work. You can put the form reference directly in a query:

WHERE [CID] = Forms![MyForm]!MyControl

The query will still ask for its parameters unless there is a way to supply
them.

Here's a rough sketch of what is going on:

rptSessionTree -> query that asks for these parameters. It joins the
session table to itself multiple times so that it is possible to see all
sessions that have been instantiated within a given category (Exam) session.

rptTimeInQuestion -> joins the sessions at the Page level in the session
tree query back to the session table to determine how much time was spent in
each page

rptStudentResponsesByQuestion -> joins the sessions at the question level in
the tree to the QuestionResponses table to get all responses to each
question

rptResponsesbyQuestion_Distractor -> relies entirely on previous query,
retrieves just the most recent response to each question _for each
distractor_

rptRespA (B, C, etc.) -> retrieves a boolean for the given response based on
whether the most recent response to it turned it on or off (based on query
directly above)

rptDistractorsOnOff -> joins ABCDE queries back to the QuestionID to
denormalize the data

rptResponseDetailABCDE -> joins the base question table to
rptDistractorsOnOff and rptTimeInQuestion to another query that follows a
similar model (that essentially returns what the actual last response was to
the question) to get complete data on how and when the question is answered
and what answer or answers are currently selected

acReportProgressDetail then joins rptResponseDetailABCDE with the Program,
Category, Topic, Page, question Info in order to organize the report in a
rational manner. This is the data source for the form.

So, now that _maybe_ I have made the underlying problem more clear, could
you please tell me how I can properly fill in the parameters that are being
asked for in rptSessionTree using your Where clause suggestion, because I'm
completely not seeing it...

-Amy

 

[Klatuu]

In this case, you will need to show the form. Also, it can't use the top
query as the record source because you don't know the the parameter values
for the underlying queries.
Provide unbound text boxes for the user to enter the paramters.
Reference the text boxes in the query parameters:
WHERE [CID] = Forms!MyReportForm.txtCID (for example)

provide a command button for the user to initiate the report. (I would start
with the command button's Enabled property set to No. Then use a sub like
this to enable it once all the paramter text boxes have been filled. Call
the function in the After Update event of the parameter text boxes:

Private Sub CheckForParms()

If Not IsNull(Me.txtCID) And Not IsNull(Me.txtEID) Then
Me.cmdReport.Enabled = True
Else
Me.cmdReport.Enabled = False
End If

End Sub

Now you have your parameters to run your query. In the Click event of the
command button, open the query as a recordset. Use the values in the fields
to build your Where argument for the OpenReport


Dim rst As Recordset
Dim strWhere As String

set rst = Currentdb.OpenRecordset("MyTopLevelQuery")
If rst.RecordCount = 0 Then
MsgBox "No Matching Records"
Docmd.Close acForm, Me.Name, acSaveNo
Else
strWhere = "[SomeField] = " & rst![FirstParam] & "[AnotherField] = "
& _
rst![SecondParam]
Docmd.OpenReport "MyReport", , , strWhere
End If
--
Dave Hargis, Microsoft Access MVP

Those parameters don't appear in the where clause of the top-level query.

I am not talking about Where in your queries. I thought you said the top
level query returns the values you want to use as parameters. Is that not
correct?

If the top level query returns the values you want to use as parameters
for
the report, what I suggest will work just fine. I guess I am getting
confused because you said that no input is required to return the values
in
the query.


[Enter CID] = Forms![MyForm].MyControl
[Enter SID] = Forms![MyForm].MyOtherControl

Will not work. You can put the form reference directly in a query:

WHERE [CID] = Forms![MyForm]!MyControl

The query will still ask for its parameters unless there is a way to supply
them.

Here's a rough sketch of what is going on:

rptSessionTree -> query that asks for these parameters. It joins the
session table to itself multiple times so that it is possible to see all
sessions that have been instantiated within a given category (Exam) session.

rptTimeInQuestion -> joins the sessions at the Page level in the session
tree query back to the session table to determine how much time was spent in
each page

rptStudentResponsesByQuestion -> joins the sessions at the question level in
the tree to the QuestionResponses table to get all responses to each
question

rptResponsesbyQuestion_Distractor -> relies entirely on previous query,
retrieves just the most recent response to each question _for each
distractor_

rptRespA (B, C, etc.) -> retrieves a boolean for the given response based on
whether the most recent response to it turned it on or off (based on query
directly above)

rptDistractorsOnOff -> joins ABCDE queries back to the QuestionID to
denormalize the data

rptResponseDetailABCDE -> joins the base question table to
rptDistractorsOnOff and rptTimeInQuestion to another query that follows a
similar model (that essentially returns what the actual last response was to
the question) to get complete data on how and when the question is answered
and what answer or answers are currently selected

acReportProgressDetail then joins rptResponseDetailABCDE with the Program,
Category, Topic, Page, question Info in order to organize the report in a
rational manner. This is the data source for the form.

So, now that _maybe_ I have made the underlying problem more clear, could
you please tell me how I can properly fill in the parameters that are being
asked for in rptSessionTree using your Where clause suggestion, because I'm
completely not seeing it...

-Amy

 

[Amy Blankenship]

In this case, you will need to show the form.
Why?

Also, it can't use the top
query as the record source because you don't know the the parameter values
for the underlying queries.

I know what I _want_ them to be. I have a query that returns one record
with three columns, and each corresponds to one of the parameters that the
query will be asking for.

Let me be quite, quite clear on this: The pyramid of queries that this is
based on was very time-consuming (read expensive) to build. The client will
NOT pay for me to make a parallel series of queries based on a form control
just because Access for whatever reason makes it difficult or impossible to
fill in the parameters for the query of a report with actual values rather
than references to form controls. Not to mention the purpose of the reports
is to test the results of the actual queries that will be in use, not near
copies of them.

Provide unbound text boxes for the user to enter the paramters.
Reference the text boxes in the query parameters:
WHERE [CID] = Forms!MyReportForm.txtCID (for example)

I'm confused. Are you saying that if I put this in the Where clause, that
Access will somehow know that it should put the Forms!MyReportForm.txtCID
into the parameter [Enter CID]? That doesn't make any sense at all to me.

provide a command button for the user to initiate the report.

I already have a command button on the switchboard.

(I would start

with the command button's Enabled property set to No. Then use a sub like
this to enable it once all the paramter text boxes have been filled.

Why does the user have to fill out these boxes, since I can look the values
up?

Not to be rude, but it seems like you haven't managed to answer the question
as asked--you keep wanting to move me back to a form based solution. And I
have no problem with using a form, just as long as, once the form fields are
filled, the _values_ of the fields (rather than a reference to the controls
containing them) can be passed to the parameters that the query needs.

I need to either forget this problem and make do with the messagebox that
tells the user what to enter in the parameter popups, or I need to be
spending my time researching to find the answer to this. If you happen to
think of a way that allows me to pass numeric values to those parameters
from whatever source, great! Please post back. Otherwise, let's just both
move on with our day instead of going around in continual circles on this
one ;-).

Thanks for trying;

Amy

 

[Klatuu]

Amy, I don't know how I can explain it any better.
You said you have to pass parameters to your set of queries that will return
the values needed to filter the report. If you show the form and put text
boxes on it that will allow a user to enter the parameters for the lower
querys, you can modify ONLY the queries that need the parameters to return
your report parameters.

You do not need to rebuild queries. I don't understand the confusion on
this. All you need is to reference the form controls that have the initial
parameters.

--
Dave Hargis, Microsoft Access MVP

In this case, you will need to show the form.
Why?

Also, it can't use the top
query as the record source because you don't know the the parameter values
for the underlying queries.

I know what I _want_ them to be. I have a query that returns one record
with three columns, and each corresponds to one of the parameters that the
query will be asking for.

Let me be quite, quite clear on this: The pyramid of queries that this is
based on was very time-consuming (read expensive) to build. The client will
NOT pay for me to make a parallel series of queries based on a form control
just because Access for whatever reason makes it difficult or impossible to
fill in the parameters for the query of a report with actual values rather
than references to form controls. Not to mention the purpose of the reports
is to test the results of the actual queries that will be in use, not near
copies of them.

Provide unbound text boxes for the user to enter the paramters.
Reference the text boxes in the query parameters:
WHERE [CID] = Forms!MyReportForm.txtCID (for example)

I'm confused. Are you saying that if I put this in the Where clause, that
Access will somehow know that it should put the Forms!MyReportForm.txtCID
into the parameter [Enter CID]? That doesn't make any sense at all to me.

provide a command button for the user to initiate the report.

I already have a command button on the switchboard.

(I would start

with the command button's Enabled property set to No. Then use a sub like
this to enable it once all the paramter text boxes have been filled.

Why does the user have to fill out these boxes, since I can look the values
up?

Not to be rude, but it seems like you haven't managed to answer the question
as asked--you keep wanting to move me back to a form based solution. And I
have no problem with using a form, just as long as, once the form fields are
filled, the _values_ of the fields (rather than a reference to the controls
containing them) can be passed to the parameters that the query needs.

I need to either forget this problem and make do with the messagebox that
tells the user what to enter in the parameter popups, or I need to be
spending my time researching to find the answer to this. If you happen to
think of a way that allows me to pass numeric values to those parameters
from whatever source, great! Please post back. Otherwise, let's just both
move on with our day instead of going around in continual circles on this
one ;-).

Thanks for trying;

Amy

 

[Amy Blankenship]

Amy, I don't know how I can explain it any better.
You said you have to pass parameters to your set of queries that will
return
the values needed to filter the report. If you show the form and put text
boxes on it that will allow a user to enter the parameters for the lower
querys, you can modify ONLY the queries that need the parameters to return
your report parameters.

You do not need to rebuild queries. I don't understand the confusion on
this. All you need is to reference the form controls that have the
initial
parameters.

Yes, I would, since if I use anything, anywhere in the tree, that first
query will still need its parameters filled _and all of the other queries
are dependent on it, usually several times_. If I were to change _that_ to
work with form controls, then none of the queries would work when called
from outside of Access. I do appreciate your attempts to help, and I
recognize that this is a task that is not often attempted. I have not so
far found a documented solution, and I realize there may not be one.

Thanks;

Amy