Results 1 to 3 of 3
  1. #1
    Join Date
    Jul 2005
    Location
    Northern California
    Posts
    21

    Unanswered: Multiple group reports, seperate pages

    I currently have a database (Access 97) that tracks aged trouble-tickets. Various tables for the tickets, locations, reps, status, etc.

    I've a form, query, report combination that allows user to select a location and generate a report for all tickets at that location in Left Message status. Generally, I run this report for the manager when she is going to a meeting at a particular location. Using this same form, I can generate the report to show all Left Messages at all locations {as an aside, I just realize it doesn't sort by location when I do that.}

    Anyhow, I've been asked to come up with that report, but have it grouped by location - the manager will be having a weekly meeting with managers from all locations. That part in itself isn't so difficult. But, it was stated (and makes sense,) to have the groups seperated by pages - so that each group will start on a new page. This would be so that my manager can hand the seperate sections to each corresponding location manager.

    I do not see a way of doing this from the report design. I can do groupings, but each one stars right after the first one finishes - on the same page if there is room.

    So, I thought maybe I could do it by way of code from the form. Currently, I could manually generate a seperate report for each location. But, I was thinking I could use a loop to go through each location and have the reports go straight to the printer instead of opening. The problem is incrementing through each location. I'm having difficulty accessing the datafields from the location table. I pretty much understand the FOR NEXT loop. And I'm thinking what I would want for this is the FOR EACH NEXT loop.

    quasi code would be something like:

    FOR EACH [address] in [table_locations]
    address_variable = [address]
    {generate report} '-- such as DoCmd.OpenReport stDocName, acViewNormal
    NEXT

    I would also need to use a variable/parametre (the address_variable}. I currently have the report/query use a parametre for the location that is from a ComboBox. For this looping generation, I would need to use something else I think. Or, at least, I would need to have a way to go through the ComboBox. In fact, it might be best if I could have the loop just work from there. Is there a way to to do that?

    If I can just get the report to start a new page with each new group, that would be ideal.

    Thanks.
    Cor
    {Currently working with Access 97, so most questions will be from that point-of-view}

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Enable the footer of the group. Select the group footer (by clicking on it) and there is a property called "Force new page" which you can set to "After Section".

    Similarly, you can set the same property of the group header to force new page before the section - this allows the creation of a cover page and results in each group starting on a new page. Then you could get fancy and put things like "the total number of calls by location" on the first page using a sub report.

    have fun
    have fun!

    Todd S.
    click to email


    Independent Development Consultant
    Biz db & reports - SQL, Access, Crystal, Seradex and more
    Small, custom, unique programs - Office, VBA, .NET


    _________________________________________________
    Favorite message from Windows:
    There was an error displaying the previous error message

    Sadly, there was no error number to look up ...

  3. #3
    Join Date
    Jul 2005
    Location
    Northern California
    Posts
    21
    The Group-Footer worked. I had issues getting the subtotals to work. I wasn't trying to use any sub forms, just trying to do it in code. Oh, by the way, I had just figured out how to get the multiple reports from the form (incrementing outputs) shortly before finding your answer. Using the Reports ability to start new pages with groups (I think I saw I could do that with the Header as well) is better.

    Anyhow, my subtotals for each group were fine if the group was only on one page. If it covered multiple pages, it was adding a few additional numbers to the subtotals. I never figured out where exactly it was getting the additional counts, but have deduced that it probably has something to do with formatting runs. I was checking Access Help, and there was a mention about it my format a page more than once depending on what the Keep-together property is. It may format a section more than once to see if everything will fit on one page, if it won't fit, it formats again. This was probably causing my increment code to do additional adding.

    Well, the manager and other consultant said, "we don't need the subtotals." So, I just removed them and moved on - and the report is what they want.

    Thanks for the help.
    Cor
    {Currently working with Access 97, so most questions will be from that point-of-view}

Posting Permissions

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