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

  • Thread starter Thread starter Gijs van Swaaij
  • Start date Start date
G

Gijs van Swaaij

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
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
 
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
loaded, and if so set the value of chkShowSubRpt based on how it is set.
 
Thanks a lot for your help! I used the first method you described, and
it works now.
 
Back
Top