Dynamic report based on multiple selections - formatting issues

[Denae]

I have a switchboard with 22 different reports, the user has the ability to check-mark the reports they want to print (e.g. 1, 5 or 22), then print the selected files to one PDF.
I accomplished this by creating one master report with each of the 22 reports added to the detail as sub-reports. I then set each report to visible based on the selections on the switchboard using VBA. This was working fine, slow, but fine; however I have recently found that the formatting on some of the reports when run outside of this master report look fine, but when run as part of the master report (sub-report) is messed up.

Here is an example of a piece of formatting run from an individual report and a sub-report, you can see how the bullets stack on top of the text and there are light boxes in strange places.

I did some research online, and there is a way to have access print multiple files to one PDF directly rather than doing the sub-report, however my VBA experience is limited and I can't find an example that works using the inherent code in Access, they were all created before this functionally was available directly in Access:

DoCmd.OutputTo acOutputReport, "rptPPW_PKT00_Consolidated", acFormatPDF, strPathAndFile, True

I found this thread, but it uses an AcroApp to create the files and not the DoCmd and try as I might, I cannot figure out how to adapt it to my situation.

http://www.tek-tips.com/viewthread.cfm?qid=1648313

Here is the list of check boxes on my switchboard and their associated report names:
[ul]
[li]chkCOV_SHEET = rptPPW_PKT01_COV_SHT_Page1[/li]
[li]chkELIGIBILITY = rptPPW_PKT02_ELIGIBILITY_Page1[/li]
[li]chkCONSENT = rptPPW_PKT03_CONSENT_Page1[/li]
[li]chkMED_CERT_COV = rptPPW_PKT04_MED-CERT-COV_Page1[/li]
[li]chkMED_CERT_EMP = rptPPW_PKT06_MED-CERT-EE_Page1[/li]
[li]chkMED_CERT_FAM = rptPPW_PKT06_MED-CERT-FAM_Page1[/li]
[li]chkCA_MED_CERT_EE = rptPPW_PKT07_B_CA-MED-CERT_EE_Page1[/li]
[li]chkCA_MED_CERT_FAM = rptPPW_PKT07_A_CA-MED-CERT_FAM_Page1[/li]
[li]chkCA_MED_CERT_MAT = rptPPW_PKT08_CA-MED-CERT-MAT_Page1[/li]
[li]chkPPL_Policy = rptPPW_PKT14_PPL_Policy_Page1[/li]
[li]chkFIT_FOR_DUTY = rptPPW_PKT09_FIT-FOR-DUTY_Page1[/li]
[li]chkCA_CHG_NOT = rptPPW_PKT10_CA-CHG-NOT_Page1[/li]
[li]chkCA_ORG_BONE = rptPPW_PKT11_CA-ORG-BONE_Page1[/li]
[li]chkCA_PREG_NOT_B = rptPPW_PKT12_CA-MAT-NOTICE-B_Page1[/li]
[li]chkCA_LOA_MAT = rptPPW_PKT13_CA_LOA_MAT_Page1[/li]
[li]chkLOAJobAid = rptPPW_PKT15_LOAJobAid_Page1[/li]
[li]chkEEChecklist = rptPPW_PKT16_EEChecklist_Page1[/li]
[li]chkEEChecklist_CHAH = rptPPW_PKT16_EEChecklist_CHAH_Page1[/li]
[li]chkEEChecklist_CORD = rptPPW_PKT16_EEChecklist_CORD_Page1[/li]
[li]chkEEChecklist_MAT = rptPPW_PKT16_EEChecklist_MAT_Page1[/li]
[li]chkSTDPay = rptPPW_PKT17_STDPay_Page1[/li]
[li]chkDESIGATION_Initial = rptPPW_DESIGNATION_Page1[/li]
[/ul]


Any help is greatly appreciated!

 

[MajP]

I do not believe there is a native way in Access to do this using the docmd method. I think it would require acrobat pro as suggested. So do you have Acrobat Pro not Reader?

 

[Denae]

You can do it without Adobe Pro using the following code.

DoCmd.OutputTo acOutputReport, "rptPPW_PKT00_Consolidated", acFormatPDF, strPathAndFile, True

My problem is not how to do the PDF, but how to do the consolidation based on the users selections without using the method I described above of a main report with 22 sub reports.

 

[MajP]

No, I do not think you can. It will not merge the file, it will only overwrite the file. At least from my testing that is what happens.

 

[Denae]

So take the printing/PDFing out of it, since that part seems to be working.

Is there any way to combine multiple reports into one file based on the selections made on the form? Outside of the way I am currently doing it with a master report and sub-reports?

 

[MajP]

I have Adobe Pro and this is the only way I could merge multiple reports into a single file. I modified some of that code in the link provided.

In the tag property of each of my checkboxes I put the name of the associated report.
1) Loop all the controls and read the tags of the checked boxes
2) Save the report names to a collection
3) Loop the collection and save all the reports to disk
4) Open the first report and then each report inserting its pages into the first report
5) Save the merged report
6) Remove all the individual reports from disk

Private Sub cmdMerge_Click()
  Dim ReportNames As Collection
  Dim SavePath As String
  SavePath = CurrentProject.Path
  If Not Right(SavePath, 1) = "\" Then SavePath = SavePath & "\"
  Set ReportNames = GetReportNames
  SaveReports ReportNames, SavePath
  MergeReports ReportNames, SavePath
  KillReports ReportNames, SavePath
End Sub

Private Function GetReportNames() As Collection
  Dim ctrl As Access.Control
  Dim RptName As String
  Set GetReportNames = New Collection
  For Each ctrl In Me.Controls
    If ctrl.ControlType = acCheckBox Then
      If ctrl.Value = True Then
         RptName = ctrl.Tag
         GetReportNames.Add RptName
      End If
    End If
  Next ctrl
End Function

Private Sub SaveReports(ReportNames As Collection, SavePath As String)
  Dim I As Integer
  Dim RptName As String
  Dim FileName As String
  For I = 1 To ReportNames.Count
    RptName = ReportNames(I)
    FileName = SavePath & RptName & ".pdf"
    DoCmd.OpenReport RptName, acViewPreview, , , acHidden
    DoCmd.OutputTo acOutputReport, RptName, acFormatPDF, FileName
    DoCmd.Close acReport, RptName
  Next I
End Sub

Private Sub KillReports(ReportNames As Collection, SavePath As String)
  Dim I As Integer
  Dim FileName As String
  For I = 1 To ReportNames.Count
    FileName = SavePath & ReportNames(I) & ".pdf"
    Kill FileName
  Next I
End Sub

Public Sub MergeReports(ReportNames As Collection, SavePath As String)
    Dim I As Long
    Dim NumberPages As Long
    Dim TotalPages As Long
    Dim FileName As String
    Dim AcroApp As Object
    Dim partDoc As Object
    Dim PartDocs As New Collection
    Const DestFile = "MergedFile.pdf"
    Const PDSaveFull = 1
    Set AcroApp = CreateObject("AcroExch.App")
    
    If Len(Dir(SavePath & DestFile)) Then Kill SavePath & DestFile
    For I = 1 To ReportNames.Count
      FileName = SavePath & ReportNames(I) & ".pdf"
      If Dir(FileName) = "" Then
        MsgBox "File Not Found" & vbCrLf & FileName, vbInformation, "Not Found"
      Else
      ' Open PDF document
        Set partDoc = CreateObject("AcroExch.PDDoc")
        PartDocs.Add partDoc
         
        PartDocs(I).Open FileName
        NumberPages = PartDocs(I).getNumPages()
        
        If I > 1 Then
          If Not PartDocs(1).InsertPages(TotalPages - 1, PartDocs(I), 0, NumberPages, True) Then
                MsgBox "Cannot insert pages of" & FileName, vbExclamation, "Canceled"
           Else
             TotalPages = TotalPages + NumberPages
             PartDocs(I).Close
            
           End If
        End If
      End If
    Next I
    Debug.Print SavePath & DestFile
    If Not PartDocs(1).Save(PDSaveFull, SavePath & DestFile) Then
       MsgBox "Cannot save the resulting document" & vbLf & SavePath & DestFile, vbExclamation, "Canceled"
    End If
    AcroApp.Exit
    Set AcroApp = Nothing
End Sub

 

[Denae]

MajP, thank you for the code.

I have pulled it into my form, what I am not understanding is what I need to change to make it work in my database. I have filled in the tag field of all the checkboxes with their report names, but other than that, I am not sure what to do. I apologize for my ignorance.

 

[MajP]

Again this only works with adobe PRO installed as far as I know. No way for me to test it without since I have Adobe Pro installed.

If you put a button or your Form and call it "CmdMerge" and make a click event it should execute the bellow proceduere. You can make another event but just have to put the below code into that event. That is the code that calls all the other code.

Private Sub cmdMerge_Click()
  Dim ReportNames As Collection
  Dim SavePath As String
  SavePath = CurrentProject.Path
  If Not Right(SavePath, 1) = "\" Then SavePath = SavePath & "\"
  Set ReportNames = GetReportNames
  SaveReports ReportNames, SavePath
  MergeReports ReportNames, SavePath
  KillReports ReportNames, SavePath
End Sub

It will save the reports into the same directory as the database and make a merged report called "MergedFile.PDF