• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 702
  • Last Modified:

Access report with subreport

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?

1 Solution
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]
MonkeyPieAuthor Commented:
Excellent!  An extra group level for job number did the trick.  Thanks.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now