INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Dynamic report based on multiple selections - formatting issues

Dynamic report based on multiple selections - formatting issues

(OP)
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:

CODE -->

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:
  • chkCOV_SHEET = rptPPW_PKT01_COV_SHT_Page1
  • chkELIGIBILITY = rptPPW_PKT02_ELIGIBILITY_Page1
  • chkCONSENT = rptPPW_PKT03_CONSENT_Page1
  • chkMED_CERT_COV = rptPPW_PKT04_MED-CERT-COV_Page1
  • chkMED_CERT_EMP = rptPPW_PKT06_MED-CERT-EE_Page1
  • chkMED_CERT_FAM = rptPPW_PKT06_MED-CERT-FAM_Page1
  • chkCA_MED_CERT_EE = rptPPW_PKT07_B_CA-MED-CERT_EE_Page1
  • chkCA_MED_CERT_FAM = rptPPW_PKT07_A_CA-MED-CERT_FAM_Page1
  • chkCA_MED_CERT_MAT = rptPPW_PKT08_CA-MED-CERT-MAT_Page1
  • chkPPL_Policy = rptPPW_PKT14_PPL_Policy_Page1
  • chkFIT_FOR_DUTY = rptPPW_PKT09_FIT-FOR-DUTY_Page1
  • chkCA_CHG_NOT = rptPPW_PKT10_CA-CHG-NOT_Page1
  • chkCA_ORG_BONE = rptPPW_PKT11_CA-ORG-BONE_Page1
  • chkCA_PREG_NOT_B = rptPPW_PKT12_CA-MAT-NOTICE-B_Page1
  • chkCA_LOA_MAT = rptPPW_PKT13_CA_LOA_MAT_Page1
  • chkLOAJobAid = rptPPW_PKT15_LOAJobAid_Page1
  • chkEEChecklist = rptPPW_PKT16_EEChecklist_Page1
  • chkEEChecklist_CHAH = rptPPW_PKT16_EEChecklist_CHAH_Page1
  • chkEEChecklist_CORD = rptPPW_PKT16_EEChecklist_CORD_Page1
  • chkEEChecklist_MAT = rptPPW_PKT16_EEChecklist_MAT_Page1
  • chkSTDPay = rptPPW_PKT17_STDPay_Page1
  • chkDESIGATION_Initial = rptPPW_DESIGNATION_Page1

Any help is greatly appreciated!

RE: Dynamic report based on multiple selections - formatting issues

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?

RE: Dynamic report based on multiple selections - formatting issues

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

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.

RE: Dynamic report based on multiple selections - formatting issues

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.

RE: Dynamic report based on multiple selections - formatting issues

(OP)
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?

RE: Dynamic report based on multiple selections - formatting issues

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

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

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 

RE: Dynamic report based on multiple selections - formatting issues

(OP)
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.

RE: Dynamic report based on multiple selections - formatting issues

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.

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"

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close