Solved

Access report with subreport

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

778 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