Hiding Group Footer based on condition

M

Macker

Can anyone tell me how to hide Group Footer based on a condition? Below is an
example of output. I want to display subtotal only if number of projects are
more than 2. Company B has only 2 projects and no sub total should be
displayed.

Company A
Project1 Cost $1000
Project2 Cost $1000
Project3 Cost $1000
Sub Total $3000

Company B
Project1 Cost $1000
Project2 Cost $1000

Company C
Project1 Cost $1000
Project2 Cost $1000
Project3 Cost $1000
Project4 Cost $1000
Sub Total $4000
 
M

Marshall Barton

Macker said:
Can anyone tell me how to hide Group Footer based on a condition? Below is an
example of output. I want to display subtotal only if number of projects are
more than 2. Company B has only 2 projects and no sub total should be
displayed.

Company A
Project1 Cost $1000
Project2 Cost $1000
Project3 Cost $1000
Sub Total $3000

Company B
Project1 Cost $1000
Project2 Cost $1000

Company C
Project1 Cost $1000
Project2 Cost $1000
Project3 Cost $1000
Project4 Cost $1000
Sub Total $4000


Add a tet box (named txtNumProjects) to the group header or
footer section and set its expression to =Count(*)

Then add a line of VBA code to the footer section's Format
event procedure:
Cancel = (Me.txtNumProjects <= 2)
 
M

Macker

Marshall, Thank you for your quick response. I have tried as you mentioned.
The subtotal still shows in the report for all groups. The text box shows
number of records correctly, but looks like my Format event is not
working/firing. Below is the code I used. When I run my report I do not even
see any message box. I am not sure what am I missing...Please advise.

FYI...I tested other events (OnPaint, OnClick) and they are firing correctly.

Private Sub CompanyFooter_Format(Cancel As Integer, FormatCount As Integer)
MsgBox (Me.txtNumProjects.Value)
Cancel = (Me.txtNumProjects.Value <= 3)
End Sub
---------------------------------------------------------------------
 
M

Macker

Marshall,

It is working when I run the report in "Print Preview", but not in "Report
View"...Thank you for your help with this...
 
M

Marshall Barton

Report view does not run any VBA code. I don't know what
that view is good for, maybe somthing to do with displaying
in a web page, whatever that might mean :-\
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top