RowSource Dilemma

[Mugs321]

Hey all,
Here's the deal... I've created a survey system and I am displaying the results in a report. The individual graphs themselves are in a subreport.

Some survey questions are multiple selection and the others are single choice. Because of the multiple selections, I need to use a separate query to generate the proper data in the graph.

My issue is where/when to change the RowSource to this other query. I put it in the On_Format event of the graph subReport (which I thought was run prior to printing) but I got a "Can't set Row Source after printing has started." error.

Any ideas?

 

[Crowley16]

have you tried putting it in the on open event of the sub report?

--------------------
Procrastinate Now!

 

[dhookom]

I think it is impossible to set varying Row Source or Record Source properties in subreports and charts.

You may need to find another solution. We might be able to provide an alternative if we knew more about your table structures, data, and other significant information.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Mugs321]

I think you may be right. I tried Crowley's suggestion and the following error occurred: "You entered an expression that has an invalid reference to the property RowSource" (because the graph doesn't yet exist?..)

The table structure of the Survey_Results is as follows:
resultID num
LoginID num
QID num
AID1 num
AID2 num
AID3 num
AID4 num
AID5 num
AID6 num
AID7 num
Comment text
Date_Answered datetime
Where AID1-7 are possible answers (AID1 is used for single selection, all or none may be used for multiple selections)

Each question has a 'bit' field named multipleSelect so I know which are multiple selections and which aren't.

Currently I use a straight-forward query to generate the graph:

SELECT a.Answer_e, Count(r.AID1) AS CountOfAID1
FROM Prod_Survey_Results AS r INNER JOIN Prod_Survey_Answer AS a ON r.AID1=a.AID
WHERE (r.QID=[Forms]![frmSurveyData]![cboQID].value) And (Date_Answered Between [Forms]![frmSurvey]![txtHiddenFrom].value And [Forms]![frmSurvey]![txtHiddenTo].value)
GROUP BY a.AID, a.Answer_e
ORDER BY a.AID;

I use two queries to generate the graphs for a multiple selection:
#1 (named qrySurveyMultiReport):

SELECT     aid1 as aid, COUNT(AID1) AS answer
FROM         Prod_Survey_Results
WHERE     qid = [Reports]![rptSurvey]![QID]  And (Date_Answered Between [Forms]![frmSurvey]![txtHiddenFrom] And [Forms]![frmSurvey]![txtHiddenTo])
GROUP BY aid1
UNION
.
.
up to aid7

#2:

SELECT a.Answer_e, Sum(r.answer) AS SumOfanswer
FROM qrySurveyMultiReport AS r, Prod_Survey_Answer AS a
WHERE (((r.aid)=a.AID))
GROUP BY a.Answer_e, a.AID;

The only other thing I could think of would be to create procedure on the SQL server with an if statement inside of it to determine which query to use and pass the required parameters through the procedure (Not that I've ever done that before...).

Let me know if you need any more info. The rest of the report looks beautiful and I'd hate to have to scrap it because of this one issue.

 

[dhookom]

Without trying to decipher what you have, consider looking at the At Your Survey sample at

http://www.rogersaccesslibrary.com/OtherLibraries.asp#Hookom,Duane.

This has a report with pie charts for each question and uses a normalized table structure.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[Mugs321]

Sorry, I probably should have provided some explanation with my code.

I tried to dl the files from the provide URL but each time I did, Winzip said it didn't appear to be a valid archive (tried both the '97 and 2000 versions).

My other tables are normalized but I didn't bother with the Results table since there will never be more than 7 survey questions/answers. (Maybe I should have taken the time and I never would've run into this).

Either way, I got it to work, through a very inefficient and unethical manner, but at least it works. I created another subReport and assigned the multiple selection query as the rowSource. I placed it directly on top of the first one. Then I used the 'Cancel' parameter in the Detail_Format event to decide which subReport to display.

This does require 2x the processing as it should.. but like I said... at least it works..