How use one table as record source for several subreports

I really thought I had the answer to this problem via another topic I had posted a few days ago.  But nothing seems to be working the way I need it to work.  I have attached an example Access file which has been cut down regarding the number of fields for demonstration.

My problem is this... I have a report that needs to contain up to 34 subreports arranged 9 across and up to 4 down.  The data has to come from a table like the attached example or I suppose form a query of some kind.  But the data in the table is in groups like...

Table Design
As you can see the "groups" are X, Y, and Z.  But here is the challenge..  The resulting sub-report has to look like...

(Y Group not showing because the field Y_REQ is false in the table. (again, please see the example Access file).

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SteveL13Author Commented:
Note:  I just created a form and added the "._REQ" field to the form and bound the form to the table.  Then tried to execute this code from a command button on the form but of course I'm getting nowhere.

Private Sub Command0_Click()

Dim RS As DAO.Recordset
Dim dbs As Database

Set RS = CurrentDb.OpenRecordset("tblExample")
    If Me.chkbxX_REQ = True Then
        dbs.Execute " INSERT INTO tblTempSpecsForSpecReport " & "SELECT X_min, X_max, X_aim " & "FROM [tblExample];"
    End If
    If Me.chkbxY_REQ = True Then
        dbs.Execute " INSERT INTO tblTempSpecsForSpecReport " & "SELECT Y_min, Y_max, Y_aim " & "FROM [tblExample];"
    End If
    If Me.chkbxZ_REQ = True Then
        dbs.Execute " INSERT INTO tblTempSpecsForSpecReport " & "SELECT Z_min, Z_max, Z_aim " & "FROM [tblExample];"
    End If

End Sub

Open in new window

Jeffrey CoachmanMIS LiasonCommented:
I remember that question...

As I stated, this is tricky to do (but still do-able), but without the "wrapping"

As you recall, the first suggestions there were that this was something that was not supported in Access reports natively.
I am not even sure an Access report can contain 34 subreports
And even if it could, I would not be tempted to do so...

Is this design something you are "curious" about?
...Because I have never seen a report that displays (or functions) in this way.

SteveL13Author Commented:
I am not just curious.  This is a real report and we don't want the "empty groups" to display on the report.
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.

SteveL13Author Commented:
And actually one report with 34 "blocks" of data would be fine if I could just get them to appear next to each other and then make all the "empty" fields visible = no if the "._REQ" was false.  Make sense?

I just need to somehow come up with a dataset that contains only the "groups" where the "._REQ" is true.

Is driving me nuts!
Jeffrey CoachmanMIS LiasonCommented:
If it were me, I would do it all on 1 row (using the code in he link I provided in your previous Q), ..and leave it like that.

The "wrapping" requirement is the sticking point here...

But lets wait and see if another expert has any other ideas...

SteveL13Author Commented:

Could you possibly send me the code clip again?
Jeffrey CoachmanMIS LiasonCommented:
I've posted so many solutions here...

You may have to go back to your original Q.
...even I had to do a search here of something like :
    boag2000 hide report columns get to that link.

If you cant find it in a day or so, ...i'll try to look up the file/link

SteveL13Author Commented:
I found it.  Just not sure how to adapt it for my needs.

to solve this you need to use an unbound subreport with a fixed number of rows and columns, the maximum number which is possible from the query for both. In your case, the columns are four: The type (Min/Max/Aim) and X/Y/Z.
The rows can be a maximum of three (Min/Max/Aim) and so you need a subreport with four rows and four columns of unbound controls (one additional row for the header).

Then you can open a recordset which only returns the needed number of rows and columns which can easily be achieved by using a parameterized crosstab query. This is based on two UNION ALL queries which creates a normalized table from your original table. The crosstab query can only return the result for one row of your example table as the number of columns can be different in each row, depending on your REQ columns.
So that is the query which will be opened as a Recordset in the "Format" events of the subreport, one for the header and one for the detail section (you can also of course move the header row into the detail section but on this way it is a little bit more separated). The Header Format procedure will read out the generated column names from the crosstab query and assign them to the label captions, the Detail Format procedure does the same with the values of the crosstab query.

That does only work in Preview View or Print mode, not in Report View because the "Format" events are not firing in the Report View.

In the attachment you can find the result.


Jeffrey CoachmanMIS LiasonCommented:
So in this Q, you are not concerend with the results "Wrapping"?

    < Just not sure how to adapt it for my needs. >
That code shrinks the designated controls, ...then moves the remaining controls over to cover the empty space...
So, it looks like it would be fairly straightforward...

In my code, I use checkboxes to designate what controls will be hidden.
If me.chkControl1=True Then
    Shrink  txtCntrl1

My guess is that you will have to use criteria like:
If me.chk_Y_REQ=False Then
    Shrink column Y

I am also not clear on your data and why it is being stored in the table, in this way.
I am not clear on those results you are showing?  Is this how you want the table data to look?, is that some type of crosstab query?..or is this the final result you want to appear in the report?

I am also not sure how/why/when the checkbox is triggered,...or even why the checkbox is needed?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Jeffrey CoachmanMIS LiasonCommented:
Jeffrey CoachmanMIS LiasonCommented:
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

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.