Convert report into individual PDF documents

[grego9]

I have a report that is grouped by a field called SiteName. When I run the report I get a front page with some summary statistics against each site and then a second page which details indivudual line details from my query.

Currently when I run the report all the sites come out together and I then print off the data and send it by post. I would ideally like to be able to extract the detail (the second page) by site and create a unique pdf for each sites detail. Is there any way to do this within Access (NB I don't want one file with all the sites info it - I need a unique file for each site - I am not bothered about the naming convention)



The structure of the report looks as follows:

Report Header (blank)
Page Header (blank)
Site Name Header

This has some summary statistics from my query

Update Header (update is another field in my query)
This has a few heading labels in it

Detail
This has the individual line details from my query in it

Update Footer

This has some summary statistics in it

Page footer (blank)

report footer


Any help would be greatly appreciated

thanks

 

[pbaldy]

You should be able to adapt this to create the pdf files instead of emailing.

http://www.granite.ab.ca/access/email/reporttomultiplerecipients.htm

 

[grego9]

There is only one recipient of the reports - and not all sites will have data every month -so I don't want any blank reports going out - will this code still deliver that?

thanks for your reply

 

[pbaldy]

It can certainly be adapted to. If using the report's no data event doesn't prevent it from being exported, you can certainly use a DCount() to test the report's record source each pass, using the current value from the recordset as a criteria.

 

[grego9]

I found this bit of code which enables me to send the data from a series of queries. The only problem is that I have 1800 potential entities - so I don't really want to have to create 1800 queries unique to each business. Ideally I want to be able to run some code across one query and for the code to be powerful enough to generate a unique file for each entity.

I am a bit of a novice on this sort of thing so any further help would be greatly appreciated! (NB I didn't write the code below!)


Private Sub run_Click()

Dim mydb As Database
Dim RS, RS1 As Recordset
Dim lngCount, lngRSCount As Long
Dim strBody, mySubject, MyMessage, Mymessage2, stDocName, stFileName, stTo, stCc As String
Set mydb = CurrentDb()

stDocName = "Entity Names"

Set RS1 = mydb.OpenRecordset(stDocName)
RS1.MoveFirst
Do Until RS1.EOF
stFileName = "Batch " & RS1!

        If QueryExists("Batch " & RS1![Code]) <> 0 Then
            If IsNull(RS1![To]) Then
            Else
                stTo = RS1![To]
                If IsNull(RS1![cc]) Then
                    stCc = ""
                Else
                    stCc = RS1![cc]
                End If
                mySubject = RS1![Entity] & "FERC Monthly Listing - " & Date
                Set RS = mydb.OpenRecordset(stFileName)
                lngRSCount = RS.RecordCount
                If lngRSCount = 0 Then
                    Mymessage2 = "You have no open FERCs this month. Please confirm via return email that this is correct."
                    DoCmd.SendObject , , , stTo, stCc, , mySubject, Mymessage2, True
                Else
MyMessage = "Initial message details"
Mymessage2 = MyMessage & " Extended message details"
                    DoCmd.SendObject acSendQuery, stFileName, acFormatXLS, stTo, stCc, , mySubject, Mymessage2, True
                End If
            End If
            Set RS = Nothing
        Else
        End If
        RS1.MoveNext
    Loop
    
End Sub

 

[pbaldy]

The code I referred you to would not require separate queries. The report filters itself based on the current value in the recordset (not shown in the looping code would be setting the form control equal to the current entity). In other words, the report is based on a query that returns all entities. As you loop through a recordset of the entities, it sends an individualized report to each.