Solved

Access report with subreport

Posted on 2013-01-24
2
690 Views
Last Modified: 2013-01-24
I have a report that shows all invoiced items and not yet billed items for a job. The report is grouped on invoice number within job number. So general layout is:

report header

job header

invoice header

invoice item detail
invoice footer (showing invoice totals)
job footer - including sub report of unbilled items for this job plus job totals
report footer (showing grand totals for all jobs)

The subreport mentioned needs to come after all invoices for current job, and before job totals.  It details all not yet billed items for current job.

Immediately after this subreport the job footer also shows job totals, including totals collected in the unbilled items subreport.  These are gathered in global variables.

My problem is that the job footer format event is triggered BEFORE the subreport is formatted and so the unbilled totals are need are not calculated yet.

Any ideas of how I could manage this?

Thanks.
0
Comment
Question by:MonkeyPie
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 500 total points
ID: 38817404
One option is to open a recordset based on a SQL statement querying the Sum() of the unbilled invoices, filtered by the current job.  Open and read the recordset in your Format event procedure and write the value(s) into the inbound textbox(es).

Another option is to add another group level based on job number with a footer (no header).  Place your subreport in the first job footer and your job totals in the second.

Best wishes,
Graham [Access MVP 1996-2013]
0
 

Author Closing Comment

by:MonkeyPie
ID: 38817468
Excellent!  An extra group level for job number did the trick.  Thanks.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

630 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question