Solved Report Output (to pdf) question (1 Viewer)

[mib1019]

Hello all,

I know what is causing my issue but don't know how to fix it. I'm sure someone can point me in the right direction....

I have a button on a form that outputs a report to pdf, saves in a specified folder, then attaches it to an email. It's worked perfectly until I had the need modify the OnLoad event for the report to hide or display two labels based on a text box on the report.

It looks like output to pdf doesn't run the code on the report, so the pdf displays both labels, one on top of the other.

Here's the code on the OnLoad event for the report.

Private Sub Report_Load()
    
'ck Advertiser and modify accordingly
Dim Advertiser
Dim Contact
Dim CPCName
Dim CPCPhone

Advertiser = Me.txtBCompanyID
Contact = Me.txtBContactID
CPCName = Me.txtCPCName
CPCPhone = Me.txtCPCPhone

If Advertiser = 463 Then
    CPCName = DLookup("ContactName", "tblContacts", "Contact_ID = " & Contact)
    CPCPhone = DLookup("OtherPhone", "tblContacts", "Contact_ID = " & Contact)
    
    Debug.Print CPCName
    Debug.Print CPCPhone


    With Me
        .txtCPCName = CPCName
        .txtCPCPhone = CPCPhone
        .lblTTTNotes.Visible = True
        .lblOtherNotes.Visible = False
    End With
 
Else
    With Me
        .txtCPCName = ""
        .txtCPCPhone = ""
        .lblTTTNotes.Visible = False
        .lblOtherNotes.Visible = True
    End With
 
End If

End Sub

Here is the piece of code that saves pdf and sends email...

strUserName = Environ("Username")
strFileName = Me.txtFileName
Me.txtFileName.Requery
strFilePath = "C:\Users\" & strUserName & "\OneDrive - xxx\xxx\xxx\xxx\" & IOYear & "\" & strFileName

Debug.Print strFilePath

'Create PDF file of IO

'Turn off system messages
    DoCmd.SetWarnings False
'Output File
    DoCmd.OutputTo acOutputReport, "Insertion Order Print", acFormatPDF, strFilePath

So, how do I get the desired report output?

Thanks in advance for your help!

 

[theDBguy]

Hi. Try opening the report first (DoCmd.OpenReport "Insertion Order Print", acViewPreview) before using the OutputTo method, and then close the report at the end (DoCmd.Close).

Hope that helps...

 

[Ranman256]

you shouldnt use any code in the report. All the fields & data should be in the query.

then email:
docmd.SendObject acSendReport ,"rMyReport",acFormatPDF,sTo,,,sSubj,sBody

 

[mib1019]

Hi. Try opening the report first (DoCmd.OpenReport "Insertion Order Print", acViewPreview) before using the OutputTo method, and then close the report at the end (DoCmd.Close).

Hope that helps...

Thanks, theDBguy! That works perfectly.

 

[theDBguy]

Thanks, theDBguy! That works perfectly.

You're welcome. Glad we could assist. Good luck with your project.

 

[arnelgp]

you can also move your code to the Format Event of the Report's Detail Section.
then you Don't need to Open the report first, directly you can execute OutputTo:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Static tf As Boolean
'ck Advertiser and modify accordingly
Dim Advertiser
Dim Contact
Dim CPCName
Dim CPCPhone
If Not tf Then
    tf = True
    Advertiser = Me.txtBCompanyID
    Contact = Me.txtBContactID
    CPCName = Me.txtCPCName
    CPCPhone = Me.txtCPCPhone
    
    If Advertiser = 463 Then
        CPCName = DLookup("ContactName", "tblContacts", "Contact_ID = " & Contact)
        CPCPhone = DLookup("OtherPhone", "tblContacts", "Contact_ID = " & Contact)
        
        Debug.Print CPCName
        Debug.Print CPCPhone
    
    
        With Me
            .txtCPCName = CPCName
            .txtCPCPhone = CPCPhone
            .lblTTTNotes.Visible = True
            .lblOtherNotes.Visible = False
        End With
    
    Else
        With Me
            .txtCPCName = ""
            .txtCPCPhone = ""
            .lblTTTNotes.Visible = False
            .lblOtherNotes.Visible = True
        End With
    
    End If
End If
End Sub