Solved

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

Posted on 2014-03-14
10
5,023 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 35

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
The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

 

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
 

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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

839 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