Run multiple reports clicking one button

Question

Hi,

I have to run 32 reports from the same Access database every month which means I have to enter the parameters 32 times and wait 32 times for them to run and print.  Can I just click one button "Run Monthly Reports" that does all this for me automatically?

Report Name	Year	Month	Rep Code	Collected (C) or Delivered (N)?	Uninvoiced(2) or Invoiced(1)?	Number of Reports
MarginByOrder	YYYY	MM	01,02,03,04 etc.	C	1	14
MarginByOrder	YYYY	MM	01,02,03,04 etc.	N	1	14
MarginSummary	YYYY	MM	% (All)	C	1	1
MarginSummary	YYYY	MM	% (All)	N	1	1
RepSummary	YYYY	MM	% (All)	C	1	1
RepSummary	YYYY	MM	% (All)	N	1	1

Thank-you in anticipation.

Ruth

Answer 1

Hi,

don't use words so as Month, it can be a reserved word in Access. Call your field txtMonth then:

Forms!DateEntry.txtMonth

Mimmo

Answer 2

Oh boy!

All sounds very complicated!

In reply to Scottgem, yes I do enter parameters at run time and I have to enter 5 parameters for each of the 32 reports.

I've started simple and tried to make it work for just month.  So I've got my new form DateEntry with fields Month and Year.  I've modified my query, taking out [Enter Month] to Forms!DateEntry.Month which didn't work, then I tried [Forms!DateEntry.Month] which didn't work, then I tried Forms!DateEntry.[Month] which also didn't work - each time it came up with the message "Enter parameter value" for the Forms!DateEntry.Month field.

Trying to get off the starting blocks and failing miserably.......!

Answer 3

Probably the easiest and most straight forward way to do that would be to create a form with text boxes for each of the parameters.  Then change the reports' record source query parameters from things like  [Enter value XXX]  to Forms!theform.textboxXXX   This will take care of entering the parameters more than once.

Then you can add a command button to the form and use its Click event with 32 DoCmd.OpenReport statements to run all the reports.

The only issue with this simple approach is if you ever need to add another report to the list.  Using a table of report names would make that very easy.  Without the table, you would then need to modify the form to add another DoCmd.OpenReport line.  OTOH, you may also need to add more parameters for the new report so the form would have to be modified anyway.  You should think about how often and how many new reports might be needed and whether their parameters are the same or different from the ones already on the form before deciding on which approach you pursue.

Answer 4

Not quite enough info. The main question is whether you need to enter parameters at run time or not for any or all of the reports. 

But you can do as Mimmo suggests and put all the reports in a table, then loop through the table running each report in turn. Or you can simply add 32 DoCmd.OpenReport methods behind a button on a form, Or you can create a macro with an OpenReport action for each report.

Answer 5

Hi,

you can do it via Vba code. If your prospect is a table from where take parameters, then:

Dim myDb As Database

Dim myRst As Recordset

Dim myFilter As String

Dim myRepCode() As String

Dim ind As Integer

Set myDb = CurrentDb()

Set myRst = myDb.OpenRecordset("Select * From myTableName")

Do While Not myRst.EOF

   If myRst("RepCode") = "% (All)" Then

      myFilter = "Year = '" & myRst("Year") & "' And Month= '" & myRst("Month") & "'"

      DoCmd.OpenReport myRst("ReportName"), acViewReport, , myFilter

   Else

      myRepCode = Split(myRst("RepCode"), ",")

      For ind = 0 To UBound(myRepCode)

          myFilter = "Year = '" & myRst("Year") & "' And Month= '" & myRst("Month") & "' And RepCode = " & myRepCode(ind)

          DoCmd.OpenReport myRst("ReportName"), acViewReport, , myFilter

      Next ind

   End If

   myRst.MoveNext

Loop

myRst.Close

Set myRst = Nothing

Set myDb = Nothing

Mimmo