Can a checkbox on a form control whether a report displays a subreport?

#1 (**permalink**) 28-Jul-2006, 08:17 AM

Hey everyone,

I have a form in my database (frmRes) that lets users select several
criteria, based on which a report (repRes) is made. For instance, the
user can choose to show only those data that relate to a specific
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/information-technology/11036-can-checkbox-form-control-whether-report.html
person. This is done by generating a where clause in a VBA script when
the user clicks "OK". After the where clause has been generated, the
script loads the report repRes with it.

Now the problem is: frmRes has a checkbox on it (chkShowSub) that
should control whether or not repRes will display a subreport. If it is
selected, the subreport should be visible, otherwise it should be
hidden. Is there a way to make my form do this? I don't see a good way
to include this in my where clause without seriously hacking the way
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11036
the report is generated - and that would be some ugly hack! Ideas?

Thanks,
Gijs van Swaaij

#2 (**permalink**) 28-Jul-2006, 08:17 AM

1. Open the query that feeds your subreport, in design view.

2. In a fresh column in the Field row, enter:
[Forms].[frmRes].[chkShowSub]
3. In the Criteria row under this, enter:
True
The query will not have no records unless the box is not checked.
Consequently, the subreport will have no records either.

If you don't want to tie the subreport to the query, you could do it
programmatically through the LinkMasterFields/LinkChildFields of the
subreport control.

1. Add a check box to the subreport and set these properties:
Name chkAlwaysTrue
Control Source =-1
Visible No

2. Add a check box to the main report. Leave its Control Source blank, and
set thes properties:
Name chkShowSubRpt
Visible No

3. Add these names to the LinkMasterFields and LinkChildFields of the
subreport. For example, if you already have:
Link Child Fields ClientID
Link Master Fields ClientID
change it to:
Link Child Fields [ClientID]; [chkAlwaysTrue
Link Master Fields [ClientID]; [chkShowSubRpt]

4. In the Open event procedure of the report, test if Forms!frmRes is
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11036
loaded, and if so set the value of chkShowSubRpt based on how it is set.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Gijs van Swaaij" wrote in message
news:1149767752.190036.220910@c74g2000cwc.googlegr oups.com...
> Hey everyone,
>
> I have a form in my database (frmRes) that lets users select several
> criteria, based on which a report (repRes) is made. For instance, the
> user can choose to show only those data that relate to a specific
> person. This is done by generating a where clause in a VBA script when
> the user clicks "OK". After the where clause has been generated, the
> script loads the report repRes with it.
>
> Now the problem is: frmRes has a checkbox on it (chkShowSub) that
> should control whether or not repRes will display a subreport. If it is
> selected, the subreport should be visible, otherwise it should be
> hidden. Is there a way to make my form do this? I don't see a good way
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11036
> to include this in my where clause without seriously hacking the way
> the report is generated - and that would be some ugly hack! Ideas?
>
> Thanks,
> Gijs van Swaaij

#3 (**permalink**) 28-Jul-2006, 08:17 AM

Thanks a lot for your help! I used the first method you described, and
it works now.
Reference:: Sikh Philosophy Network http://www.sikhphilosophy.net/showthread.php?t=11036