how can I print 100+ reports in MS Access not individually?

[Dave Ganesh]

I have set-up my MS Database so that I can print Supplier Scorecards for over
120 Suppliers. The problem is that I must print each report individually for
each Supplier which get tedious after a while.

Is there a way to get around this?

 

[John Spencer]

Why do you need to print each report individually?

Do you have 120 separate reports or must one report that you open 120 times
with different criteria?

IF you want to print the same report 120 times, you could use a VBA routine to
do so. You would need to loop through the suppliers to do so and set criteria
for the report.

If you just need to print all the scorecards in one batch and need a page
break so each scorecard is on a separate page, that is simple to do.

UNTESTED AIR CODE - Note there is no error handling added
Assumptions:
-- a table of suppliers
-- SupplierID is a number field and is the primary key
-- the report's record source for the report has one reference to the
supplierId either as the primary key or a foreign key of a table used in the
record source. If multiple references, you will have to refer to the table
plus the field name when building strCriteria
-- a single report is used to print the scorecards
-- you need to print the score cards separately


Public Sub sPrintIndividualScoreCards()
Dim dbAny as DAO.Database
Dim rstAny as DAO.Recordset
Dim strCriteria as String

Set dbAny = CurrentDb()
Set rstAny = dbAny.OpenRecordset ("SELECT SupplierID FROM SuppliersTable")

With rstAny
While Not .EOF AND Not .BOF
strCriteria = "SupplierID = " & .Field(SupplierID}
Docmd.OpenReport "ScoreCardReport",WhereCondition:=strCriteria
.Move Next
WEND
End With

End Sub


John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

 

[John Spencer]

I realize that I may have implied that the code was for printing one report
with a break between suppliers. The code is not for that. THe code is for
printing the report many times and changing which supplier the score card is
being printed for.



John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County