Hide report sections when not needed

[tweetyksc]

I'm hoping someone can help me here.
I have reports that from criteria chosen on a form.
Report is grouped by 1)Sales Area 2) Sales Rep.

There is a group footer for both, with subtotals. There is also grand totals in the report footer. There are page breaks between the reps because they need to be on a page without others.

Unfortunately, if the user chooses to run for one rep, they get a rep total, AND an area total, AND a grand total....3 pages - with redundant Area and grand total pages.

I was thinking I could use the "on format" event for the report to check the form's combo boxes, and cancel formatting...but I can't seem to access the form's comboboxes through code.
For instance, if the combo box for the sales rep has data, the footers will be canceled.

If [Forms]![frmStartup]![cboSRSBP].Value Is Not Null Then....

I get an error saying variable not defined.
I'm sure this is something other people have run across - only needing grand totals if there is more than one group, etc.?

 

[Rich]

Something like
Private Sub GroupFooter0_Format(Cancel As Integer, FormatCount As Integer)
If Me.Text47 > 1 Then
Me.GroupFooter0.Visible = True
Else: Me.GroupFooter0.Visible = False
End If
End Sub

 

[tweetyksc]

Thanks for the reply Rich.
The problem is, it can't find the control on the form ("method or data member not found").

FYI - I'm putting this code in the report's module, under the "on format"...should I be putting it in the form module instead that calls the report??
------------
Private Sub AreaFooter_Format(Cancel As Integer, FormatCount As Integer)

If Me.cboSRSBP.Value Is Not Null Then
Me.AreaFooter.Visible = False
Me.AreaFooter.ForceNewPage = False
End If
End Sub
-----------------
This would check the "sales rep" and "sales Area" combo box and if it has a value, then the footers would not be needed.

I know I can create seperate reports to do this and do a select case to pull the different ones, but I'd like to do it more efficiently.

 

[Rich]

There's no point in having a combo box on a report in this case, change it to a textbox

 

[tweetyksc]

No, the combobox is not on the report - it's on the form that is used to select the parameters for the report. They can run the report for all (which will need the footers/totals) or filter the report for specific customers, sales reps, etc. (and not need the footers/totals). I'm trying to find out if they are selecting a particular customer, etc. then the footers will not be needed on the report. I'm trying to check to see if the comboboxes have data selected.

A mixture of comboboxes and option groups are used to choose the selections, and then a button runs the report.