Solved

Access report with subreport

Posted on 2013-01-24
2
684 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 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

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

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

Today's users almost expect this to happen in all search boxes. After all, if their favourite search engine juggles with tens of thousand keywords while they type, and suggests matching phrases on the fly, why shouldn't they expect the same from you…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

929 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now