Welcome to SPN

Register and Join the most happening forum of Sikh community & intellectuals from around the world.

Sign Up Now!

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

Discussion in 'Information Technology' started by Gijs van Swaaij, Jul 28, 2006.

  1. Gijs van Swaaij

    Gijs van Swaaij
    Expand Collapse
    Guest

    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
     
  2. Loading...


  3. Allen Browne

    Allen Browne
    Expand Collapse
    Guest

    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.

    --
    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" <g.vanswaaij@gmail.com> wrote in message
    news:1149767752.190036.220910@c74g2000cwc.googlegroups.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
    > 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
     
  4. Gijs van Swaaij

    Gijs van Swaaij
    Expand Collapse
    Guest

    Thanks a lot for your help! I used the first method you described, and
    it works now.
     

Share This Page