Run multiple reports for multiple asset types

[cphelps]

I have inherited a Plant database that tracks plant and assets, their maintenance history and generates checklists for employees to use during quarterly processes. For example, a vehicle is entered. That vehicle may be equipped with a number of items (e.g. fire extinguisher, first aid kit, slings, chains, etc) that need to be inspected regularly. The asset details are listed into different tables from the vehicle details.

Currently the Fleet administrator every three months loses a day to printing output from the database that includes a summary checklist of what the vehicle is equipped with. One of these is created for every vehicle. However in addition to this she runs a total of nine other reports that prints out the specific and detailed checklist for assets on the vehicles that are due for an inspection before a date that she specifies.

The reports that are run print off in order by vehicle number and are for the following items that needs to be inspected. Each of these need to have their own unique checklist due to the nature of asset and the inspection that needs to be done.

• Fire Extinguisher
• Pole Top Rescue Kit
• Flat Slings
• Ladders
• Round Slings
• Chain Slings
• Fire Blanket
• First Aid Kit
• Calibration Due

The reason the Fleet Admin loses a day to this process is that all ten reports (summary checklist plus the nine reports listed above) produces a pile of paper that she then has to manual collate.



Putting aside the urge to go paperless (another story), I am looking to give her an efficiency in this process by having all of the nine reports printed in order by vehicle (e.g. all items due for inspection that are on vehicle #1, all items due for inspection that are on vehicle #2, all items due for inspection that are on vehicle #3, etc.). This way she will only have two piles of paper to work from by having to match up the summary checklist with a number of reports for the corresponding vehicle.

I am wondering if anybody has done anything like this before so that I do not need to totally re-invent the wheel again. I was thinking that a process may need to run to create some temporary tables that are then used control the sequence of the printing.

Anybody got any suggestions or guidance?

[June7]

No temp tables needed.

VBA could open a recordset object of the vehicles meeting a given criteria, loop through that recordset, open each report in turn filtered for vehicle in the record, print, close report, move to next vehicle record, repeat the printing. Something like:

Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("SELECT vehicleID FROM Vehicles WHERE some criteria here;")
While Not rs.EOF
DoCmd.OpenReport "report1", , , "vehicleID=" & rs.vehicleID
DoCmd.Close acReport, "report1", acSaveNo
DoCmd.OpenReport "report2", , , "vehicleID=" & rs.vehicleID
DoCmd.Close acReport, "report2", acSaveNo
....
rs.MoveNext
Wend

There are probably cleaner code structures but that should give you an idea to start with.