Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Conditional report sections

    Note: Sep. 17 2013: I have since learned this post should be entitled, "How to Stack Multiple Subreports in Main Report"

    First off, I am new to access and this forum, so thanks in advance for your guidance. I may fall short in terms on not always using the appropriate terminology. I have come to MS Access as a means to streamlining operations at my work by automating some tedious yet important tasks. I have created a database with multiple linked tables and simple reports based on that.

    The Scenario

    I would like to design templates for different sections of a report. When it comes time to run the report not all sections will be included. I work for and IT services provider, so the different sections will correspond to different configuration items such as workstations, servers, various network devices, ISP information, On-premise Exchange, etc.

    Not all the sections will be needed for a particular report. For instance, a client may use Hosted Exchange rather than On-premise Exchange, and I'd like to layout the info in the report for each in a different way.

    Is this possible? Or is there a better way to reach this same end?

    Thanks in advance for your help!
    Last edited by Operator; 2013-09-17 at 21:37.

  2. #2
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I've been informed by theDBguy on another forum to lookup subreports. I'll update the post if this does trick...

  3. #3
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Subreports don't appear to be the answer. It may help to ask my question differently, is there a way to combine multiple, and independent, reports (only the ones that are applicable) into one single report? And to programmatically select which reports to run based on the data?

    In the mean time I'll go ahead and create the reports separately and worry about refinements later.

    Thanks!

  4. #4
    Star Lounger
    Join Date
    Dec 2009
    Location
    Findlay, Ohio
    Posts
    57
    Thanks
    4
    Thanked 6 Times in 6 Posts
    What's wrong with setting a flag the report checks at 'on open' do a cancel=true

  5. #5
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply, orangehat! I assume you're referring to the portion on 'programmatically selecting which reports to run, based on the data'.

    I have since learned 1) how subreports are utilized in this case. The one-to-many relationship wasn't immediately obvious to me. And 2) that it is possible to hide subreports that have no data, or based on some other condition. (I'll look into your comment about this latter step orangehat)

    1. This is one of those things, once you learn it, it's very easy. I realized I had to create a trivial table called 'Templates' and that is how to include each report template as a subreport. (The one-to-may relationship, since a template is typically used several times) The main report is based on the Templates table.

    I'll marked this thread as solved, once I am able to implement step 2.

  6. #6
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    THere is another way to effectively hide subreports where there is no data that you may find easier than diving into VBA. That is to set the height of the subform control to 0 (zero), and the Can Grow property to Yes. You are taking on a pretty ambitious project for the first Access database, so don't get too frustrated with all the different options.

    Also it would help to know what version of Access you are working with, as there are some fairly significant differences in capabilities. In general the later versions offer more, though there are instances where some functionality has been removed.
    Wendell

  7. #7
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi WendellB,

    This has actually been a really fun experience, no insurmountable hurdles, ...yet.

    Thanks for the tip on setting the subform control height =0, Can Grow = Yes. I see using this a lot.

    I have since found out my subreports are still not printing out as they do as standalone reports. I'm using Access 2010. I'll try to explain as concisely as possible...

    I created multiple reports based on data originating from one record set. Which report a record belongs to is determined by the column named TYPE. (with values of, say, A, B, and C). I then created a query for each report that uses the filter, TYPE = "A" (or "B", "C"). The reports ReportA, ReportB, and ReportC print out as expected, listing all relevant records.

    Now I created a main report that has the above reports stacked on each other. The report is based on a 1 column table (Table_TYPE) that includes the values A, B, and C. I revised the above queries to link this table to the field TYPE in the table used in the above queries.

    The result is that only one subreport prints out as expected. This subreport corresponds to the 1st row of Table_TYPE.

    After playing around with different things I can't get all subreports to have the same print out as they do as standalone reports.

    This has proven to be my main hurdle, and any help would be appreciated

    Thanks!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Two things about subreports that are a bit tricky:
    - the linking of the subreport to the main report is done in the subreport control and requires the same key field or fields on both
    - the report header and footer do not display on a subreport

    The first may require putting a hidden control (setting the visible property to False/No) that contains the key field. Also, you may not want to stack the subreports precisely on top of each other, but space them 0.001 or so apart so the reports show in the expected order.

    If you aren't able to resolve you issues, you might try posting a cut-down version of your database with enough data (that doesn't contain and personal or confidential info) so that one of us can debug it.
    Wendell

  9. #9
    New Lounger
    Join Date
    Sep 2013
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks a lot Wendell! I won't be able to work on it until this evening or tomorrow, but the previous post helps.

  10. #10
    New Lounger
    Join Date
    Sep 2013
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    I am not sure what your portions of the report will contain. If it is similar data that you can basically set up the headers and than have the various information displayed based upon which table (or combination of tables), then 1 you could use a macro to read who is logged on (meaning that users will have to log onto the database with a password) and select the correct query. 2. you could require user input from the user to identify who they are, through an unbound form, which is stored in a storage table, then use DLookup in the query criteria to select the desire info. 3. you can set up subreports, especially important if the headers are NOT going to be the same, then use macros to make them visible or not visible again according to either who is logged on or user input. (If it is "for your eyes only" info, thenI suggest you use passwords.)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •