Mulitple Reports

[CJBIRKIN]

Hello

I have a report with a dynamic recordsource that is created from selections made on a form.


For efficiencies sake I want to open mulitple instances of the same report using a loop. Each report has a different set of parameters for the recordsource query. The reports need to be previewed by the user before printing.

Can anyone help?

Thanks Chris

Dim WAITCOUNT As Integer

WAITCOUNT = 0
Do Until WAITCOUNT = CMBO_MONTHS_AHEAD + 1
' function passes data to variables which are used to create recordsource for the report
OPENROLLINGREPORT CMBO_MONTHS_WAIT, CMBO_MONTHS_AHEAD

DoCmd.OpenReport "IP_PTL_ROLLING_IP_REPORT", acViewPreview
WAITCOUNT = WAITCOUNT + 1
Loop

 

[CJBIRKIN]

In answer to my own question:



Private Sub CMD_PROJECTION_RPT_Click()
Dim WAITCOUNT As Integer
Dim DB As DAO.Database
Dim RST As DAO.Recordset
Dim PROCESSEDDATE As Date

Set DB = CurrentDb()
Set RST = DB.OpenRecordset("LWVDATATABLE")
PROCESSEDDATE = RST![ProcessDates]

WAITCOUNT = 0
Do Until WAITCOUNT = CMBO_MONTHS_AHEAD + 1

If IsNull(CMBO_MONTHS_WAIT) Then
MsgBox "You have not selected a wait in months the query cannot be run"
Exit Sub
Else

' function sends data to public variables on each loop which are used by the recordsource string
OPENROLLINGREPORT CMBO_MONTHS_WAIT, WAITCOUNT, Format(DateAdd("M", WAITCOUNT, PROCESSEDDATE), "MMMM YYYY")
CreateReport (WAITCOUNT)
WAITCOUNT = WAITCOUNT + 1
End If
Loop

End Sub



................................................................................................
Public Function GETRECORDSOURCE() As String
GETRECORDSOURCE = "SELECT .......WAITINMNTHS & WAITINGTIME ;"
End Function
.................................................................................................
Public Function OPENROLLINGREPORT(MNTHSWAIT As Integer, MONTHSAHEAD As Integer, PROCESSINGDATE As String)

WAITINMNTHS = MNTHSWAIT
WAITINGTIME = MONTHSAHEAD

' this one is used in a text box on the report
MONTHFORREPORT = PROCESSINGDATE

End Function
....................................................................................................



Public Sub CreateReport(RptNum As Integer)
Dim Rpt As Report


On Error Resume Next
DoCmd.DeleteObject acReport, "IP_PTL_ROLLING_IP_REPORT" & RptNum
' open the template report
DoCmd.OpenReport "IP_PTL_ROLLING_IP_REPORT", acViewDesign
' make a copy of the template report and rename to working name
DoCmd.CopyObject "", "IP_PTL_ROLLING_IP_REPORT" & RptNum, acReport, "IP_PTL_ROLLING_IP_REPORT"
' close the template
DoCmd.Close acReport, "IP_PTL_ROLLING_IP_REPORT", acSaveYes
' open the working copy
DoCmd.OpenReport "IP_PTL_ROLLING_IP_REPORT" & RptNum, acViewDesign
' create a report object
Set Rpt = Reports("IP_PTL_ROLLING_IP_REPORT" & RptNum)
' set the recordsource to the crosstab table
Rpt.RecordSource = GETRECORDSOURCE()
' save report

DoCmd.Close acReport, "IP_PTL_ROLLING_IP_REPORT" & RptNum, acSaveYes
DoCmd.OpenReport "IP_PTL_ROLLING_IP_REPORT" & RptNum, acViewPreview