Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

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

Posted on 2014-03-14
10
Medium Priority
?
5,346 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 3
10 Comments
 
LVL 39

Expert Comment

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

Expert Comment

by:Dale Fye
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
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 

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 48

Expert Comment

by:Dale Fye
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 48

Expert Comment

by:Dale Fye
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

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

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

609 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