Repeat a report but with different criteria on same report page

[KyleB]

I'm not sure if I'm looking at this problem correctly, but here's what I have set up (as close to 3NF as I can manage using generic names for simplicity below)

tblDatatype1 & subtblDetails1
tblDatatype2 & subtblDetails2
tblDatatype3 & subtblDetails3
tblDatatype4 & subtblDetails4
tblDatatype5 & subtblDetails5

qryData1 -> qrysummariesandcalcsofData1
qryData2 -> qrysummariesandcalcsofData2
qryData3 -> qrysummariesandcalcsofData3
qryData4 -> qrysummariesandcalcsofData4
qryData5 -> qrysummariesandcalcsofData5
qryUnionSummaryofAboveSummariesandCalcs
qryCrosstab1ofData1
qryCrosstab2ofData1
qryCrosstab1ofData2
qryCrosstab2ofData2
qryCrosstab1ofData3
qrySummaryofAllAboveQueriesinApprovedFormat

qryReportofSummaryQueryAbove

On my DB front end I have a switchboard that contains a text box. Depending on the department entering information they will modify this text box so that all of the work they deal with is pertinent to thier department only. Other department data will not show in thier queries, forms, reports etc.

However; also on this switchboard there is a command button which I wish to use, that will run a report for each department individually and create a report that sequentially lists the exact same sums for each department in the same format. Unfortunately the problem I'm encountering is that the report that is generated goes through 3 standard select queries, a union query, and 5 crosstab queries in order to display the information in the format, and order requested. By doing so I do not know how I can create a report that is in the continuous form style that I've used in the past. Would I be able to have the command button use the text box on the switchboard to run the first report (I can get this far, it's the following I can't figure out) then, without using the control button again, but as a sequential operation, have the VBA change the text box to another value, and rerun the same report and append it to the end of the previous report until all departments have been run and appended, then preview the report?

Kyle

 

[KyleB]

In case I was too confusing with my description I'll try this simpler version:

Is it possible via VBA code to run a report (based on a text box control), have the code modify the text box, and run the report again, then modify the text box a third time, and run the report a third time, then print all three results sequentially on one page?

Am I making this too complicated? Would I be better off making 3 seperate sets of queries (and all underlying subqueries) and print the three reports as sub-reports instead, so that they show up on one page?

While the second option above is viable it seems like an excessive duplication of queries (17 queries would need to be duplicated 3 more times, leaving me with 51 extra queries, and 4 more reports) for nothing more than one page, containing details for 2 options, and a grand summary of those two options on one page. (see bottom of post for format layout) I'm just trying to keep this from getting too bloated, though it's looking like I won't have much choice from what I've been able to determine so far.


KyleB


Desired report format thus:

Report 1

Details here based on one set of criteria, summaries via crosstabs in a​

requested format.​

Report 2

Details here based on one set of criteria, summaries via crosstabs in a​

requested format.​

Report 3

Summary of two reports above, using the exact same formatting, and​

crosstab data.​