visible=false in a footer in a report

[Greg]

i can't get this working. in vba i tried to set visible to false in an if
statement that looked like this:
Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
If IsNull(Forms!formquerybuilder.Controls!cboxdepartment) = False Then
Reports!rptcontactdata.Section(GroupFooter1).Visible = False

End If

instead of 'groupfooter1' i tried using 'section(1)', i tried using
'saleperson footer' which is the name of the group that corresponds to that
section. i just can't figure this one.

the report is divided into 3rds, top is 'department 1', middle is
'department 2' and bottom is the combined total. my statement is supposed to
hide the total (bottom 3rd) when the user selects only one department to run
the report off of (rather than the usual 'null' that will force the report to
do both departments w/total).

what have i got wrong?

 

[Allen Browne]

Greg, try something like this:

Private Sub GroupFooter1_Format(Cancel As Integer, FormatCount As Integer)
Dim bShow As Boolean
bShow = Not IsNull(Forms!formquerybuilder!cboxdepartment)
With Me.Section(acGroupLevel1Footer)
If .Visible <> bShow Then
.Visible = bShow
End If
End With
End If

That uses the acSection value to refer to the group footer. It would
probably be better to refer to it by name, e.g.:
With Me.Section("GroupFooter1")

The code only sets the Visible property of the section if it is set wrongly.
It also resets it to True again when needed.

Another alternative to setting the Visible would be to set the report's
PrintSection and MoveLayout properties.

 

[Greg]

allen,
at first it didn't work and i was going to post for more help.
truthfully, i didn't understand your vba as well as all that because i'm not
proficient with it (it's the with/end with thing that i have never seen
before that's making it hard). when i looked more closely i noticed you had
this line:

bShow = Not IsNull(Forms!formquerybuilder!cboxdepartment)

and when i changed it to

bShow = IsNull(Forms!formquerybuilder!cboxdepartment)

it worked great. i don't know if my intentions weren't clear in my 1st post
or if my code was hard to grasp (in the same way a first grader copying a
passage from macbeth would be hard to grasp) and it threw you off the goal
but i needed it to be visible only when no specific department was selected
in formsquerybuilder. that way, if they do select a specific department,
then they don't get duplicate sections of the form (with only two available
departments, the department calculations and total calculations would be the
same if only one was selected).

if you can see anything wrong in my logic or the change i made to your code
based on this information, please say so. or if you feel like orating on how
a 'with' statement works, please do. otherwise, thank you very much for your
help.

 

[Allen Browne]

No, that's fine, Greg. What you did was perfect.

What we try to do is give you an example to work from, and let you apply it.
I didn't read your question well, and got your intention backward.

The With block is just another way to refer to the same thing several times.
For example instead of saying:
Forms!Form1!Text1 = 1
Forms!Form1!Text2 = 2
you could code:
With Forms!Form1
!Text1 = 1
!Text2 = 2
End With

HTH