Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Access report with subreport

Posted on 2013-01-24
2
Medium Priority
?
698 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
2 Comments
 
LVL 20

Accepted Solution

by:
GrahamMandeno earned 2000 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

Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

Question has a verified solution.

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

Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
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…
Suggested Courses

963 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