Optional Sub Reports

[DarkProdigy]

Hey guys,

I have a main report with several subreports on it that are filtered by a form with several combo boxes on it

What I would like to do is have some of the sub reports only open on the main report depending on the input in the combo box

ie.if the user chooses "Yes" from the 'Heated' combo box, I would like that to show the "Heater" sub report on the main report

thanks in advance for your help

Nathan

 

[JoeComputer]

Hint

Hmm... on the onOpen event for the report, you could probably go:

if form!combobox=ValueIfGood
Subreport.visible=true
else
Subreport.visible=false
endif

Technically, the subform runs, but is invisible.

Just a thought...

 

[DarkProdigy]

I don't think this will work for me

see, there are some subtotal text boxes on these subforms that contribute to a final total box on the main form

if they are simply invisible, their totals would still appear in the final total would it not?

 

[DarkProdigy]

I really need an answer to this guys or even if it's possible

if anyone can help me

 

[richary]

Not tested, but extending JoeComputer's idea, for each textbox that gets summed, on your sub report, the control source could be something like

=iff([Visible],YourField,0)

setting its value to zero if the sub-report has been made invisible.

 

[DarkProdigy]

Hmm... on the onOpen event for the report, you could probably go:

if form!combobox=ValueIfGood
Subreport.visible=true
else
Subreport.visible=false
endif

Technically, the subform runs, but is invisible.

Just a thought...

is the syntax for this correct?

it seems to be having trouble compiling it

 

[DarkProdigy]

If anyone can help me with the syntax of this if statement that would be great. It's kind of a priority

 

[DarkProdigy]

/bump

anyone, anyone at all

 

[DarkProdigy]

ok update

I think I got the code right

Private Sub Report_Open(Cancel As Integer)
    If Forms!frmInvoice!cboTankType = "Sour" Then
    rptCoating.Visible = True
    Else
    rptCoating.Visible = False
    End If

End Sub

but I get a "Run Time Error: 424" when it runs

any suggestions?

 

[JoeComputer]

Is rptCoating a chart?

Also, you may need to specify to Access that you're talking about the subreport of the main report, such as:

me!rptCoating.Visible=true

Access may think you're talking about rptCoating on its own.

Here's info on Run Time Error: 424
http://support.microsoft.com/search/default.aspx?spid=1265&query=Run+Time+Error%3A+424&catalog=LCID%3D1033&pwt=false&title=false&kt=ALL&mdt=0&comm=1&ast=1&ast=2&ast=3&mode=a&x=0&y=0

 

[DarkProdigy]

Thanks for your reply JoeComputer, but I figured it out FINALLY!!

What I had was the code on the On Open event of the sub report. So was I did was move this coding onto the On Open event of the main report and now it works wonderfully

 

[DarkProdigy]

Not tested, but extending JoeComputer's idea, for each textbox that gets summed, on your sub report, the control source could be something like

=iff([Visible],YourField,0)

setting its value to zero if the sub-report has been made invisible.

I'm still having difficulty with part of the report. I tried putting that code in the "Filter" box of my subreport. It does affect my overall total text box which has the sub total box incorporated in it's sum but the change in the overall total is not the same as the sub total itself from this sub report. I can't seem to figure out why this is.

If anyone has any suggestions as to how to fix this, that would be great or even if I'm going about it the right way

Thanks, Nathan

 

[giovi2002]

Access has a special event in a report called on no data.
Simply goto the subreport and goto events
Witnin no data set the following code

Private Sub Report_NoData(Cancel As Integer)
Me.Visible = False
End Sub

Set the can grow can shrink properties on the main report of the sub report to true and minimize your report.
In this way you can hide the report without too much whitespace