How to conditionally print subreports 1

[Apschminkey]

I am making changes to a commercial laboratory LIMS system that our company uses to track and report test samples. We have a document called a “Lab Sheet” which is used to document various pieces of information before the data is entered into the computer. Currently, to print these lab sheets I have a form with a combo box from which the user can select an order number. An order is a collection of samples from single client. Each sample may have one or more tests assigned to it. The query which feeds the report takes the selected order number and finds all of the tests which are assigned to the samples in the order. A single Lab Sheet is printed for each test. The details section includes a list of test parameters associated with a particular test. People here are not happy with the simple list of test parameters and would like to design custom Lab Sheets for many of the tests. What I would like to do is create a single lab sheet having the normal headers/footers EXCEPT that the body of the report is produced by a subreport. All of the possible subreports for all of the possible tests would be placed on the main report. When test "X" comes up in the query, I would like to make visible ONLY the subreport corresponding to the lab sheet required for Test "X". If the next sheet to be printed is test "Z", I would like to print a sheet with only the test "Z" subreport. I found a piece of code (below) online which was written in response to a guy who was looking for a way to control the printing of a subreport.

--------------------------------------------------------
In the "On Page" event of the report, put the following code:

If YourFlag=True then
Me.subformname.Visible = False
Else
Me.subformname.Visible = True
End If

---------------------------------------------------------

Could I do something like:

If testName1=True then
Me.subformname.visible=true
If testname2=True then
Me.subformname.visible=true
If testname3=True then
Me.subformname.visible=true
If testname4=True then
Me.subformname.visible=true
Else
Me.DefaultSubform.visible=true
End if

The If/Else block is probably not correct (I struggle with that all the time) but I think you can see where I'm going with it. Am I on the right track?

Thanks!

 

[dhookom]

I wouldn't use the On Page event. Consider displaying or hiding subreports with code in the On Format event of the section containing the subreports.

The code might look something like:

Me.subReportName.Visible = (Me.SomeTextBox = True)

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

What dhookom said if you stick with your solution...

I would be inclined to make the header and the footers the subreports in the various lab sheets.

Then I would create a table associating each report with the test or class of test or whatever makes since in your case.

Then when I generated the lab sheet, I would open the appropriate report.

This is simply more intuitive to mangage to me. On the otherhand, if you are printing more than one lab sheet at a time, forget I said anything.

 

[Apschminkey]

I DO have to print bunches of lab sheets at one time. If I use the On Format event will the "visibility" reset if the next page to be printed is a different test? In other words, if I have several different types of report to print with the subreports toggle on and off as needed?

 

[dhookom]

Don't you have different data on different pages? Doesn't the data determine if a subreport should be visible? I would think the only time you need to use the On Page event would be if you wanted to manipulate the controls in the page header or footer.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[lameid]

As long as your data changes for the section, then yes on format should work.

Looking back it looks like we failed to mention something.

The Can Grow and Can Shrink properties should be set to true for all the sub reports and the section they are in.

If you want to see how it will work, put subreports A and B in your report, make the related code and run it with criteria to limit to lab sheets for A and B.

 

[Apschminkey]

This is REALLY close! I created two subreports, each with one line of text, and they become visible as the test name dictates. The single line of text that I put into the subreport is in the details section, and it shows up twice when it prints in the main report. I haven't figured out if this is because of the details (test paramaters) associated with the test name or if something else is going on. I tried to move the text to another portion of the subreport but none of them are visible in the main report even though all of the properties of all of the sections are set to visible. If I used a form instead of a report would it be less suseptible to effects from the data?

The subreports are currently in the test header, there could be MANY of them if this works out....is there a limit? I am hoping to create a default report in the details section which would be used if there were no test names which "activated" subreports in the test header. I'll keep tinkering, I really appreciate all your help!

 

[Apschminkey]

Is it possible that because I am putting something in the page header of a subreport (which really doesn't have a "page" of its own) it will not print?

 

[Apschminkey]

I used the report header of the subreport......it works!

 

[lameid]

You seemed to mention both having your subreports in your details section and a header section.

The thing to keep in mind is the report will print in the associated section the number of times it appears. So if you have a lab with 2 tests and you put it in the lab header (or footer) you would expect to see it once but if you put it in the detail section you would expect to see the same subreport for each test (assuming of course you are matching up the lab and not test).

I hope that helps.

 

[BoulderRidge]

Sounds like you're well on the way to a great report...

Re your comment "If I used a form instead of a report would it be less suseptible to effects from the data?", I just wanted to suggest staying with report objects as subreports rather than form objects. I recently inherited a very complex report with several subforms, which worked very nicely except that we had no control over page break formats.

I wound up converting them all into subreports with report objects just to get the functionality of repeating the header section of the subreport on every page of the main report when it spanned multiple pages. I was not able to identify any advantage offered by using forms instead, and the repeating header was critical to my situation.

Best of luck, --BoulderRidge

 

[Apschminkey]

Here is the code I have in the On Format event so far:


Private Sub GroupHeader6_Format(Cancel As Integer, FormatCount As Integer)
Me.STA_LabSheetSubreport_A.Visible = (Me.Testname = "Test A")
Me.STA_LabSheetSubreport_B.Visible = (Me.Testname = "Test-B")
Me.STA_LabSheetSubreport_C.Visible = (Me.Testname = "Test-C")
Me.STA_LabSheetSubReport_Default.Visible = (Me.Testname = "Test-X")
End Sub

For testing purposes, I triggered my default report with a specific test name and it works fine. How would I arrange the conditions so that the default prints only when none of the above conditions apply? IF/ELSE?


Also, Will the spaces between my subreports eventually accumulate so that it will kick over into a second page? Should I butt all of the subreports together so that there is no space at all?

 

[lameid]

Private Sub GroupHeader6_Format(Cancel As Integer, FormatCount As Integer)
Me.STA_LabSheetSubreport_A.Visible = False
Me.STA_LabSheetSubreport_B.Visible = False
Me.STA_LabSheetSubreport_C.Visible = False
Me.STA_LabSheetSubReport_Default.Visible = False

Select Case Me.Testname
Case "Test A"
Me.STA_LabSheetSubreport_A.Visible = True
Case "Test-B"
Me.STA_LabSheetSubreport_B.Visible = True
Case "Test-C"
Me.STA_LabSheetSubReport_Default.Visible = True
Case "Test-X"
Me.STA_LabSheetSubReport_Default.Visible = True
Case Else
'Make your no other report visible here
End Select

End Sub

 

[Apschminkey]

This works perfectly. Thanks for all of your help!