Access 2010 Report with 40 questions that each require a different subreport

Within an Access Report, I have 40 questions. Each of the questions is listed within the Detail section of the report. For each of the questions, I need to be able to use a different subreport. How can I set up the subreport so that a different subreport is used for each of the forty questions.
James_MoroneyAsked:
Who is Participating?
 
Dale FyeCommented:
Agree with JD, it is rare that you would need 40 different tables.  There are really only a few types of questions when it really comes down to it.  Check out:http://www.rogersaccesslibrary.com/forum/at-your-survey_topic3.html
for a really good example of a survey database.
0
 
Dale FyeCommented:
Can you explain what is in the sub-reports?

I don't have 2010, so this is mere speculation, but normally for something like this they would all be in a similar format, and would only be filtered by the the field that is linked as the ID field on the main report.  Or maybe to the ID field, and the Question #.

You might be able to use the detail sections Format event to change the SourceObject of the subreport, something like:

Private Sub Detail_Format

   Select Case me.txt_Question
        Case 1, 3, 7, 10
            me.subReportControlName.Report.ControlSource = "rptQuestionYesNo"
        Case 2, 4, 8
            me.subReportControlName.Report.ControlSource = "rptQuestionTextResponse"
        Case 5, 6, 9
            me.subReportControlName.Report.ControlSource = "rptQuestionPickOne"
        Case 11 to 15
            me.subReportControlName.Report.ControlSource = "rptQuestionMultiResponse"
     End Select
End  Sub

If you actually have 40 different subreports, you could use an If statement instead of the Case statement.

You might also have to modify the child/master link fields to ensure that each of the subreports will be filtered to reflect the correct question.


       
0
 
JVWCCommented:
40 Different subreports suggests the info will be from 40 different tables!
I expect the aswer to your Q will be in the table/query structure.
Are you able to upload a sanitised sample of your DB?

Cheers
JC
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
hnasrCommented:
Why not use grouping? Unless your setup is different from normalized data!
0
 
James_MoroneyAuthor Commented:
The data for each of the subreports comes from 40different tables. Collectively, the data for the different subreports cannot be normalized into a common table. Each of the 40 questions deals with a different subject matter.

The suggestion to use a case statement (or If-Endif) would be very helpful. However, the "Source Object" tab within the Property Sheet for an unbound subreport seems to only permit selection of a single and specific choice for the data source. Where would I put the vba code for the case (or if-endif) statements? Thanks in advance.
0
 
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<The data for each of the subreports comes from 40different tables. Collectively, the data for the different subreports cannot be normalized into a common table. Each of the 40 questions deals with a different subject matter. >>
  FWIW, generally that's not the case.  It's just a matter of structuring it right.  If you haven't gone too far with the design, you might want to post a question looking for some ideas on how to re-structure.
JimD.
0
 
Patrick MatthewsCommented:
This question has been classified as abandoned and is being closed as part of the Cleanup Program.  See my comment at the end of the question for more details.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.