asked on

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...

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).

Example.accdb

SteveL13

ASKER

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
     
    dbs.Close

End Sub

Open in new window

Jeffrey Coachman

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.

JeffCoachman

SteveL13

ASKER

I am not just curious. This is a real report and we don't want the "empty groups" to display on the report.

SteveL13

ASKER

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 Coachman

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...

JeffCoachman

SteveL13

ASKER

Jeff,

Could you possibly send me the code clip again?

Jeffrey Coachman

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

JeffCoachman

SteveL13

ASKER

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

SOLUTION