Bulk reporting

[AlliCarr]

Hi,

I'm not quite sure whether what I want to do is possible but, here goes:

I have a reporting form in my database which can be used to select a list of reports and the criteria for the reports. The example below will bring up all the grants our organisation has made in a specific geographic location. I have included the resulting report.

What I have been asked to do is to add the ability to bulk report for each constituency so that separate reports are produced all at once showing the grants for each constituency, without the need to keep going to the reporting form and changing the drop down box and running the report again each time.

I have tried to research online but don't really have an idea of how to even search for what I want to do.

Any guidance would be appreciated.

 

[Gasman]

Use whatever controls the bulk action as a recordset, and generate report for each record.

Sub Print_All_Ships()
 Dim rs As DAO.Recordset
 Dim db As Database
 Dim stSQL As String, stDate As String, stDBpath As String, stFTPpath As String
 Dim stRptName As String, stParam As String, stLinkCriteria As String, stAlphabet As String, astAlpa(1, 26) As String
 Dim stStart As String, stEnd As String, iloop As Integer
 Dim iOKCancel As Integer
  
 ' GoTo rptalpha
  
 stRptName = "Main_by_Ship"
 Set db = CurrentDb
' Generate all the Ship reports
' GoTo rptleave:
stDBpath = CurrentProject.Path & "\"
stFTPpath = stDBpath & "Gazette\"

 stSQL = "SELECT Ship.Ship FROM Ship WHERE (((Ship.ID)<> 26 and (Ship.ID)<> 27 and (Ship.ID)<> 60))ORDER BY Ship.Ship"

 Set rs = db.OpenRecordset(stSQL)

 Do While Not rs.EOF
 ' Need to convert any spaces in ship name to _ for website
    stParam = LCase(Replace(rs!Ship, " ", "_"))
    stLinkCriteria = "[Ship] = '" & rs!Ship & "'"
    
    'DoCmd.CopyObject , stParam, acReport, stRptName
    DoCmd.OpenReport stRptName, acViewPreview, , stLinkCriteria, acHidden
 ' Pause for 5 seconds to save report
    'Pause (5)
    DoCmd.OutputTo acOutputReport, stRptName, acFormatPDF, stFTPpath & stParam & ".pdf", False
    DoCmd.Close acReport, stRptName
    'DoCmd.DeleteObject acReport, stParam

    rs.MoveNext
    
'    iOKCancel = MsgBox("OK to proceed?", vbOKCancel)
'    If iOKCancel = vbCancel Then
'        Exit Sub
'    End If
    
 Loop
 rs.Close
 Set rs = Nothing
End Sub

 

[Minty]

There are a number of ways to achieve what you are after.

How are you filtering the reports currently, I assume you are build a where clause to put into the open report command?
Or is it part of the report record source?

 

[theDBguy]

Not in front of a computer now, but I thought you cannot open multiple instances of a report without overwriting the previous one unless maybe if you can create multiple instances of a report like you can with forms. Are you trying to view multiple reports or save them in a folder?

 

[Pat Hartman]

Here is an example. It is more complex than you requested because it is designed to show several methods of exporting to several different types such as Preview, Print, PDF, Excel. You could add other options such as Both Print & PDF so the loop could do both if you wanted to do both. You can also add an email option that emails the PDF or Excel file. The email option hasn't been implemented yet.

 

[AlliCarr]

There are a number of ways to achieve what you are after.

How are you filtering the reports currently, I assume you are build a where clause to put into the open report command?
Or is it part of the report record source?

The report combobox is used to filter the reports. It is based on a query which selects all the reports in the database and then the report is opened based on the combobox value.

 

[AlliCarr]

Not in front of a computer now, but I thought you cannot open multiple instances of a report without overwriting the previous one unless maybe if you can create multiple instances of a report like you can with forms. Are you trying to view multiple reports or save them in a folder?

I don't need to view the reports but it would be useful to save them to a folder and to print them too.

 

[AlliCarr]

Thanks @Gasman and @Pat Hartman I'll take a look at your suggestions and try them out.

 

[theDBguy]

I don't need to view the reports but it would be useful to save them to a folder and to print them too.

Then you should be able to use the OutputTo method to save them into files.

 

[Isaac]

It doesn't look to me like you actually have separate reports. You have a form with some dlookups.
Create a query showing all constituents with the appropriate columns and the totals sum. Then output that to Excel.
I doubt you need to get as fancy as a Report object in Access, which I'd avoid unless you actually need it.

 

[Pat Hartman]

I don't need to view the reports but it would be useful to save them to a folder and to print them too.

My example includes that.