How use one table as record source for several subreports

Posted on 2014-08-07
Medium Priority
Last Modified: 2014-08-13
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).

Question by:SteveL13
  • 6
  • 5

Author Comment

ID: 40247321
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

LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40247336
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.


Author Comment

ID: 40247349
I am not just curious.  This is a real report and we don't want the "empty groups" to display on the report.
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks


Author Comment

ID: 40247362
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!
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40247373
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...


Author Comment

ID: 40247455

Could you possibly send me the code clip again?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40247803
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
...to get to that link.

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


Author Comment

ID: 40248502
I found it.  Just not sure how to adapt it for my needs.
LVL 24

Assisted Solution

Bitsqueezer earned 1000 total points
ID: 40248731

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.


LVL 74

Accepted Solution

Jeffrey Coachman earned 1000 total points
ID: 40248875
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?
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40249163
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 40259204

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Suggested Courses

850 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question