Generate Separate PDF Export By Group

[hfreedman1957]

Hi,


I have a report that has a grouping by Project Manager name (PM). The goal is to be able to email each report grouping to the respective PM. The report runs from a query. As the report runs, it should export each grouping as a separate pdf document, then I will manually handle the emailing of the reports to the PMs. The filename to be saved will use the PM name and I can hard code the path. If there is another way to reach my goal, I`m open for suggestions. Please help! Thanks so much!

[ranman256]

Make a reporting form, on it put a listbox, lstMgrs.
The listbox is the Project manager list of emails.
The code will scan the list ,sending each person their report. The report looks at the listbox to pull that persons data.

'------------
Public Sub btnSendList_click()
'------------
Dim vTo, vSubj, vBody, vRpt, vItm
dim i as integer

vRpt = "rReport1"
vBody = "body of email"
vSubj = "your report"

     'scan the list box
For i = 0 To lstMgrs.ListCount - 1
   vItm = lstMgrs.dataitem(i)   'get new item in list
   lstMgrs = vItm               'set list box to item
   vTo = lstMgrs.Column(1)      'col 1 has name, col2 has email  (in vb, combo box items starts with 0)
     
      'send report
   DoCmd.SendObject acSendReport, vRpt, acFormatPDF, vTO, , , vSubj, vBody

Next
End Sub

[HiTechCoach]

Here is an example I created and posted to my web site to show you a way to handle this:

Batch Printing and Save As PDF

[AccessPower]

I have an exact example of this on my website:

https://accessgurublog.wordpress.com...eports-as-pdf/

Private Sub cmdPrintLoop_Click()
If Dir("C:\Users\YourUsername\Desktop\Report\", vbDirectory) = "" Then
        MkDir "C:\Users\YourUsername\Desktop\Report\"
End If


Dim dbs As DAO.Database
Dim rsNames As DAO.Recordset
Dim strNames As String
Dim strPDF As String


strNames = "SELECT DISTINCT Manager FROM tblTemporary;"


Set dbs = CurrentDb
Set rsNames = dbs.OpenRecordset(strNames, dbOpenDynaset)


rsNames.MoveFirst
Do While Not rsNames.EOF
    strPDF = rsNames!Manager & ".pdf"
    strPDF = Replace(strPDF, "/", "-")
    DoCmd.OpenReport "rptAll", acViewReport, , "Manager = '" & rsNames!Manager & "'"
    DoCmd.OutputTo acOutputReport, "rptAll", acFormatPDF, "C:\Users\YourUsername\Desktop\Report\" & strPDF
    DoCmd.Close acReport, "rptAll", acSaveNo
    rsNames.MoveNext
Loop
End Sub

[hfreedman1957]

Hi All,
Thank you so much for your responses. I looked through each and used the code from HiTechCoach as my starting point. With that I still can't get it to work. I "think" my requirement is more complicated. My report is based on a query, not an individual table. My query utilizes several tables and runs through all projects (table) pulling and grouping all services (table) for each project. The rest of the tables and calculated fields are inconsequencial for now, but the report is complicated enough that I need to run the report off the query. My VB is weak at best but I can make my way through most of it. I am utlizing a form as in the example, based on the project table to select which projects to print individual reports for (I like that!). I'm thinking I need to set up the query with a criteria for the project field so it only runs for the one project and therefore only selects services for the one project. This criteria must be fed via a variable or something from the form button's VB code. I may be talking through my hat. Please let me know your thoughts. Thanks again!

[hfreedman1957]

WhooHooo! I have it working. In the vba code for the button. I removed all references to the query. I simply set a
value (current project name based on selection form) in a static table field. I used that field as criteria for the project name in the query, and Bob's your uncle. Thanks for all the help. Greatly appreciated.

[hfreedman1957]

I don't know how to mark this thread as solved 😳

[HiTechCoach]

WhooHooo! I have it working. In the vba code for the button. I removed all references to the query. I simply set a
value (current project name based on selection form) in a static table field. I used that field as criteria for the project name in the query, and Bob's your uncle. Thanks for all the help. Greatly appreciated.

You're welcome. Glad we could assist.

FYI: My example is very simple to show how the concept/technique/method works. My goal was to strip it down as much as possible to the minimum required and still be something that was a real world application of the concept. The concept can be adapted to work in many situations.

Glad it was helpful.

[hfreedman1957]

Actually, I would like to make one change. Instead of setting a field in static table to use in the query criteria, I'd rather set a variable in the vb code and use that variable in the criteria of the query. I've tried several ways, but the query can't recognize the variable. If you are able to help, I will need it very detailed ie. Where exactly do I declare the variable, an example of the declaration, and exactly how I reference that variable in the criteria field in the query, with an example. Thanks so very much.

[HiTechCoach]

Actually, I would like to make one change. Instead of setting a field in static table to use in the query criteria, I'd rather set a variable in the vb code and use that variable in the criteria of the query. I've tried several ways, but the query can't recognize the variable. If you are able to help, I will need it very detailed ie. Where exactly do I declare the variable, an example of the declaration, and exactly how I reference that variable in the criteria field in the query, with an example. Thanks so very much.

Use the TempVars collection. It can set it in VBA code an used it in a query!

To use a VBA variable in a query you must create a User Defined Function (UDF) to return the value. Every time you want o use a new variable you will have to update your VBA code to handle it. Another reason to use TempVars.

FWIW: I have started using TempVars a lot. I have not had any issues with them. They do save me a lot to coding!

[hfreedman1957]

I Googled for examples on using TempVars and implemented it. It all works perfect and was easy. Thanks so much for pointing the way. Greatly appreciated.

[HiTechCoach]

I Googled for examples on using TempVars and implemented it. It all works perfect and was easy. Thanks so much for pointing the way. Greatly appreciated.

You're welcome.

Glad we could assist.