|
|
Hiding Group Footer based on condition
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
|
|
0
|
|
|
|
Reply
|
 Utf
|
5/20/2010 3:54:01 PM |
|
Macker wrote:
>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)
--
Marsh
MVP [MS Access]
|
|
|
0
|
|
|
|
Reply
|
Marshall
|
5/20/2010 4:19:42 PM
|
|
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
---------------------------------------------------------------------
"Marshall Barton" wrote:
> Macker wrote:
>
> >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)
>
> --
> Marsh
> MVP [MS Access]
> .
>
|
|
|
0
|
|
|
|
Reply
|
Utf
|
5/21/2010 7:28:01 PM
|
|
Marshall,
It is working when I run the report in "Print Preview", but not in "Report
View"...Thank you for your help with this...
"Macker" wrote:
> 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
> ---------------------------------------------------------------------
>
> "Marshall Barton" wrote:
>
> > Macker wrote:
> >
> > >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)
> >
> > --
> > Marsh
> > MVP [MS Access]
> > .
> >
|
|
|
0
|
|
|
|
Reply
|
Utf
|
5/21/2010 7:32:06 PM
|
|
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 :-\
--
Marsh
MVP [MS Access]
Macker wrote:
>It is working when I run the report in "Print Preview", but not in "Report
>View"...Thank you for your help with this...
>
>"Macker" wrote:
>
>> 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
>> ---------------------------------------------------------------------
>>
>> "Marshall Barton" wrote:
>>
>> > Macker wrote:
>> >
>> > >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)
>> >
>> > --
>> > Marsh
>> > MVP [MS Access]
>> > .
>> >
|
|
|
0
|
|
|
|
Reply
|
Marshall
|
5/21/2010 9:26:09 PM
|
|
|
4 Replies
1459 Views
(page loaded in 0.07 seconds)
|
|
|
|
|
|
|
|
|
Search |
Post Question |
Groups |
Stream |
About |
Register
4435 articles. 
12 followers. 