Solved

MS Access 2010 problem with DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF

Posted on 2014-03-14
10
4,791 Views
Last Modified: 2014-06-08
I have an Access report that contains some formatting in the Report_Open section of code.  This turns on or off a label based on a computation.
When I run DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF to save the report as a PDF the required code does not run because the report is not opened.  Is there a way to set the label info before the OutputTo command is run?  I can't have the user save the previewed report as a pdf because I am merging a number of pdf files together using Stephen Lebans code.  All  of my reports are working with no problems except this one.
0
Comment
Question by:JodyD
  • 6
  • 3
10 Comments
 
LVL 34

Expert Comment

by:PatHartman
ID: 39930834
Try moving the code to the Format event for the section where the control is located.
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39931153
Have you tried opening the report in print preview mode (you can do it hidden if you don't want it visible), and then tried the docmd.Output to command with the report already open?
0
 

Author Comment

by:JodyD
ID: 39931169
I moved the code to the Detail_Format of the report.  Works when I run the report straight but not when I use the output to PDF.
0
 

Author Comment

by:JodyD
ID: 39931172
I modified the code to produce the pdf report adding the open report in preview mode.  PDF is still incorrect.

If strReportName = "rptSalesRepCommission" Then
        DoCmd.OpenReport "rptSalesRepCommission", acViewPreview
        DoCmd.OutputTo acOutputReport, "rptSalesRepCommission", acFormatPDF, sPDFPath & "\" & sPDFName, False
        DoCmd.Close acReport, "rptSalesRepCommission"  
    Else
        DoCmd.OutputTo acOutputReport, strReportName, acFormatPDF, sPDFPath & "\" & sPDFName, False
    End If
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39931177
Can you provide screenshots of the report in Preview mode and the PDF version, so we can see what is not formatting properly?

If you provide the code that you are running the in the Report_Open event we might be able to recommend an alternative method.
0
Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

 

Author Comment

by:JodyD
ID: 39931201
I'm attaching 2 copies of the same report.  One was printed to CutePDF and the other was created using the code in question.  The yellow "CONRATULATIONS" is only supposed to display if the amount in the Remaining Balance block is zero.
There is additional code to hide the whole Countdown section for Sales Reps with higher titles.  This code seems to work.  I have a button that creates the reports as a combined PDF for all of the Reps.  The Countdown section is hidden when it should be an displayed when it should be.  But the Congratulations always shows when the block is displayed.

This report is a subreport placed in the report footer of the main report.  The files show the 2 page report, the footer is always on page 2.

Code follows:
Dim strTerritory As String
Dim strChoice As String
Dim strPeriod As String
Dim strHold As String
Dim strSQL As String
Dim dbCurrent As Database
Dim rs As Recordset
Dim rst As Recordset
Dim curTotal As Currency
Dim bShowPromo As Boolean
   
    Set dbCurrent = CurrentDb
    If IsNull(strUserID) Or strUserID = "" Then
        strUserID = Environ("username")
    End If
    strSQL = "SELECT Choice, Period FROM tblPick WHERE UserName = " & Chr(34) & strUserID & Chr(34)
    Set rs = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
    If rs.EOF Then
        MsgBox "Unable to Run Team Report. No Filters selected.", vbOKOnly, "rptSalesCommission Report Load"
        Exit Sub
    Else
        strChoice = rs!Choice
        strPeriod = rs!Period
    End If

    If Left(strChoice, 4) = "OPEN" Then
        Me.lblPeriod.Visible = False
        Me.lblYTD.Visible = False
        Me.lnPeriod.Visible = False
        Me.lnYTD.Visible = False
        Me.lblComm.Visible = False
        Me.txtPerComm.Visible = False
        Me.txtYTDComm.Visible = False
        Me.lblBonus.Visible = False
        Me.txtPerBonus.Visible = False
        Me.txtYTDBonus.Visible = False
        Me.lblRPStart.Visible = False
        Me.txtRPStart.Visible = False
        Me.lblRPEnd.Visible = False
        Me.txtRPEnd.Visible = False
        Me.lblTotal.Visible = False
        Me.txtPerTot.Visible = False
        Me.txtYTDTot.Visible = False
        Me.lnTotPer.Visible = False
        Me.lnTotYTD.Visible = False
    End If
    strSQL = "SELECT tblManager.Period, tblManager.Initials " & _
            "FROM tblManager " & _
            "WHERE (((tblManager.Period)=" & Chr(34) & strPeriod & Chr(34) & ") AND " & _
            "((tblManager.Initials)=" & Chr(34) & strChoice & Chr(34) & "))"
    Set rs = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rs.EOF Then
        Me.lblRPStart.Visible = False
        Me.txtRPStart.Visible = False
        Me.lblRPEnd.Visible = False
        Me.txtRPEnd.Visible = False
    End If
   
'    Hide Promo for OPEN, AMIT and zero goal except title 1
    bShowPromo = True
    strSQL = "SELECT tblSalesRep.Initials, tblSalesRep.AreaMgrInTraining " & _
            "FROM tblSalesRep " & _
            "WHERE (((tblSalesRep.Initials)=" & Chr(34) & strChoice & Chr(34) & "))"
    Set rs = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rs.EOF Then
        If rs!AreaMgrInTraining Then
            bShowPromo = False
        End If
    End If
    rs.Close
    If strChoice Like "OPEN*" Then
        bShowPromo = False
    End If
    strSQL = "SELECT Title, Initials FROM tblSalesRep " & _
            "WHERE (((Initials)=" & Chr(34) & strChoice & Chr(34) & "))"
    Set rs = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
    If Not rs.EOF Then
        strSQL = "SELECT ID, Title, Goal, NextTitleID FROM tblTitles " & _
                "WHERE (((Title)=" & Chr(34) & rs!Title & Chr(34) & "))"
        Set rst = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
        If Not rst.EOF Then
            If rst!Goal = 0 And rst!ID <> 1 Then
                bShowPromo = False
            End If
            If rst!ID = rst!NextTitleID Then
                bShowPromo = False
            End If
            rst.Close
        End If
    End If
    rs.Close
    If bShowPromo Then
        lblLastPromo.Visible = True
        txtNextPromo.Visible = True
        lblPromoTarget.Visible = True
        txtTargetPromo.Visible = True
        lblPromoAchieved.Visible = True
        txtActualPromo.Visible = True
        txtVariancePromo.Visible = True
        txtStartDate.Visible = True
        lblPromoBalance.Visible = True
        strSQL = "SELECT Choice, Period, Total FROM tblPick WHERE UserName = " & Chr(34) & strUserID & Chr(34)
        Set rs = dbCurrent.OpenRecordset(strSQL, dbOpenSnapshot)
        If rs.EOF Then
            MsgBox "Unable to Run SalesRep Report. No Filters selected.", vbOKOnly, "rptFootSalesComm Report Load"
            Exit Sub
        Else
            strChoice = rs!Choice
            strPeriod = rs!Period
            curTotal = rs!Total
        End If
        If curTotal > 0 Then
            Me.lblPromo.Visible = False
        Else
            Me.lblPromo.Visible = True
        End If
    Else
        lblLastPromo.Visible = False
        txtNextPromo.Visible = False
        lblPromoTarget.Visible = False
        txtTargetPromo.Visible = False
        lblPromoAchieved.Visible = False
        txtActualPromo.Visible = False
        txtVariancePromo.Visible = False
        txtStartDate.Visible = False
        lblPromoBalance.Visible = False
        lblPromo.Visible = False
    End If
TestCutePDF.pdf
TestOutputTo.pdf
0
 
LVL 47

Expert Comment

by:Dale Fye (Access MVP)
ID: 39931212
And this code is all in the REport Open event?

First, I rarely use the Report Open event.  I prefer to use the Load event to format aspects of the report that will apply to all of the records, and either the Report_Current, Detail_Format, or Detail_Print events that relate to specific records in the report.

I'm not certain I understand the purpose of creating a recordset in the Open event.  Are the fields you have included there not in the report itself?  If they are, again it would be conducive to move the code from the Open to the Form Current or Details events.
0
 

Author Comment

by:JodyD
ID: 39931287
This is a two part report. I run a procedure that loads the totals for the footer  including Period and YTD totals loading this data into tblVariance there are lots of computations and the totals are Period and YTD.  
The main report uses a query on the regular tables.  The subreport in the footer uses a query on tblVariance.  I coundn't figure out a way to show both period and YTD totals on the same page in the required side by side format without creating the extra table.

The code I posted doesn't work if it is in the Report_Load section, the Countdown block and the Congratulations is displayed even in the print preview of the report.
The code works when it is in the Report_Open or Detail_Format  section in the print preview except for hiding the Congraulations.
0
 

Accepted Solution

by:
JodyD earned 0 total points
ID: 39931560
I finally figured out my problem.  It had nothing to do with the OutputTo command.  Turns out I was setting the value I check to turn on/off Congratulations to zero before running the call to create the PDF.  Everything is working properly now.  

Thank you Dale for giving me a hand.  It kept me working on the problem.
0
 

Author Closing Comment

by:JodyD
ID: 40120215
I was able to figure the problem out with Dales help.  Mainly because it kept me working on the report.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Join & Write a Comment

The first two articles in this short series — Using a Criteria Form to Filter Records (http://www.experts-exchange.com/A_6069.html) and Building a Custom Filter (http://www.experts-exchange.com/A_6070.html) — discuss in some detail how a form can be…
Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now