Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2014-03-14
10
Medium Priority
?
5,435 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 40

Expert Comment

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

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
Restore individual SQL databases with ease

Veeam Explorer for Microsoft SQL Server delivers an easy-to-use, wizard-driven interface for restoring your databases from a backup. No expert SQL background required. Web interface provides a complete view of all available SQL databases to simplify the recovery of lost database

 

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 49

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 49

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

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…
Suggested Courses

580 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