Problem with subreports (1 Viewer)

[Graham1894]

Hi, can anyone help.

We undertake statutory inspections of machinery. I am in the process of creating a database Access 2013 which amongst others has a table that contains all the equipment we inspect (tblEquipment).
Due to the regulations that apply to different types of equipment I have 60 different inspection reports all running from the same query (queReport3), one each for a thorough inspection, a working inspection and a non-complete inspection for each category of equipment.

New equipment is added to the client location by a combo box, when the category is selected DLookups insert the name of the corresponding reports into three columns of the tblEquipment table. On the inspection reporting form there are three buttons, thorough, working and non-complete. Clicking the corresponding button produces a two page inspection report personalised to the category of equipment and type of inspection. When the user clicks complete on this form it produces a PDF copy of the inspection report and saves it to a directory, it can then be viewed by clicking a button which runs the following VBA;

Dim strPath As String
'Get path
strPath = ("C:\DATABASE\InspectionReports" & "\Inspection No" & Me.ReportID & ".pdf")
Application.FollowHyperlink strPath

This all works as it should.

However, we currently produced all inspection reports in Word, convert them to one pdf file and email them to the client, and although the end result will be for the database to go onto an sql and provide a customer portal, during discussions with our clients a lot of them have said they would still like us to provide this one pdf file with all that days inspection reports.

This is what I am having a problem with.

I was looking at creating a button that used VBA to combining into one all the pdf inspection reports from a site visit using the SiteVisitID as a reference. Although this would keep the integrity of the report I now think there would be too many problems in both setting it up and long term as Adobe release updates.
So I am now looking at producing a main report that uses the same 60 inspection reports as subreports, big big problems and I’ve only tried it with three types of reports so far.

In the inspection report I’ve moved the company logo and address from the report header into Group Header. It works fine as both the main inspection report and as a subreport (small trail with one type of report).

What I need is for each subreport to keep the integrity of the single report, correct report type, same number of pages etc.

If I put the subreports in the detail of the main report it produces a report for each item of equipment in each of the reports. In trails, if I had three types of subreports in the detail but the equipment only used 2 of them, only the 2 types of sub report would appear but the equipment would use both and appear as many times as there where equipment 4 items, 2 types of report produce 32 reports, (4 items X 2 types X 4 items).

If I put the subreport in the Group Header I can get it to run the right amount of times but if the last report of the first type runs into three pages which can happen if there are a lot of defects to report, the first report of the next type starts right below it, not on the next page. If I put a page break between each subreport I end up with a load of blank pages at the end where report types with no data would appear if they had data. Besides that I don’t think I can put 60 subreports in the Group Headers.

I know the problem is the 60 different types of report running from the same query but I don’t really want to split the equipment table up, any on got any ideas including structure change if needs by.

Many thanks,

Structure

tblEquipment, this contains all the equipment information that remains the same for the life of the equipment, i.e. Make, Model, serial number etc. What I call the constants.

tblInspectionReports, this contains the information that changes at each inspection i.e. defects, hours of service etc, what I call the variables.

tblSiteVisit, this contains the date of visit, Travel Time, etc.

A query (queReport3) brings all the information together for producing the final inspection report, which requires information from each of the three tables.

 

[Uncle Gizmo]

I noticed you have yet to receive a reply, this might be because you have posted such a long post no one has bothered to read it! You often get a better response if you restrict yourself too short succinct questions. This works for two reasons:-
1) - None of us have much time, and can't really spend a lot of time reading through a mass text blast to find out the essence of your question or questions we just move on to something which is much simpler and easier to answer.
2) - The way to solve a problem is to break it down into smaller and smaller problems this is also the essence of good programming, (build your code from small useful "working" blocks of code) It's the essence of anything really, break it down into the smallest possible problems and solve those and build up from there. By thinking about your problem and breaking it down into smaller problems which you can post as questions you will also start to see the problem differently yourself.

With regard to your problem, I had a quick peruse through, I didn't see anything I can do to help with your exact problem, however I do recall helping another member here create multiple PDF documents and I thought it might be an option you might consider, although I understand you want like 3 single reports in one single PDF document. You can find more information on my website here:- Videos - ‎Generate PDF Invoices in MS Access‎ https://sites.google.com/site/msacc...pies-of-report-with-different-criteria/videos

 

[Graham1894]

Thank you for your reply, I will bear that in mind in future.