none
VBA code is not working when saving a PDF Report File from a form RRS feed

  • Question

  • Hello,

    I have a form called "frmInvoice" and there are two search field boxes called "Customer" and "Order No."under its form header. There is a command button called "Save PDF". The form is linked to a subform called "frmInvoiceSubform". The goal of this form is to search by Customer Name and then by Order No, one will save a PDF file from a report called "rptInvoice" into a folder on the desktop, by clicking on the Save PDF command button.

    When I try to test the VBA code by selecting a Customer Name and an Order No from the search boxes and then clicking the "Save PDF"command button, I received a Microsoft Visual Basic error message and the debugging error occurs at line 7: - 

    Run-time error '2059':

    Microsoft Access cannot find the object '|1'.

    The VBA code is attached below

    Many Thanks for your help.

    Private Sub CmdSavePDF_Click()
    Dim FileName As String
    Dim Filepath As String
    
    FileName = Me.cboCustomerID & "_ID "
    Filepath = "C:\Users\Laurence\Desktop\Client Folder\" & FileName & ".pdf"
    'DoCmd.OutputTo acOutputForm, "rptInvoice", acFormatPDF, Filepath
    DoCmd.OutputTo acOutputForm, "rptInvoice", acFormatPDF, Filepath
    MsgBox "Customer info has been successfully", vbInformation, "Save confirmed"
    
    End Sub

     


    • Edited by wirejp Sunday, April 10, 2016 1:16 PM
    Sunday, April 10, 2016 12:16 PM

Answers

  • You need to filter the reports datasource by using a reference to your actual form. Open the form, select your customer and order. Get in the design view of your report. Edit the record source on the data page of the property editor. Use the Builder to access your loaded subforms.

    The resulting expression should be Form!frmInvoice!frmInvoiceSubform!OrderNo.

    • Marked as answer by wirejp Monday, April 11, 2016 3:51 PM
    • Unmarked as answer by wirejp Monday, April 11, 2016 4:00 PM
    • Marked as answer by wirejp Monday, April 11, 2016 6:00 PM
    Sunday, April 10, 2016 1:31 PM
  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by wirejp Monday, April 11, 2016 3:50 PM
    • Unmarked as answer by wirejp Monday, April 11, 2016 4:00 PM
    • Marked as answer by wirejp Monday, April 11, 2016 6:00 PM
    Sunday, April 10, 2016 6:12 PM

All replies

  • When you want to save a report as PDF then you need to acOutputReport constant as first parameter instead of acOutputForm:

    DoCmd.OutputTo acOutputREport, "rptInvoice", acFormatPDF, Filepath
    

    Sunday, April 10, 2016 12:49 PM
  • Hi Stefan,

    It worked! Thanks. However, the saved pdf file contains the complete list of invoices for all of the customers. I just want a saved pdf copy of the invoice of the specific customer which I selected from the Customer & Order search boxes in the frmInvoice. How do you fix this problem?

    Many Thanks.

    Sunday, April 10, 2016 1:25 PM
  • You need to filter the reports datasource by using a reference to your actual form. Open the form, select your customer and order. Get in the design view of your report. Edit the record source on the data page of the property editor. Use the Builder to access your loaded subforms.

    The resulting expression should be Form!frmInvoice!frmInvoiceSubform!OrderNo.

    • Marked as answer by wirejp Monday, April 11, 2016 3:51 PM
    • Unmarked as answer by wirejp Monday, April 11, 2016 4:00 PM
    • Marked as answer by wirejp Monday, April 11, 2016 6:00 PM
    Sunday, April 10, 2016 1:31 PM
  • Hi Stefan,

    I followed your instructions above, but I cannot get it to work. This is what I have done: -

    (i) I opened the frmInvoice form, and I selected the [CustomerName] and the [OrderNumber] in the subform

    (ii) I opened the RptInvoice report in design view. Then I tried to edit the record source on the data page of the Property Editor. This is the part which is giving me the trouble: - the current record source is the qryInvoice invoice, when I tried to evoke the Query Builder of the Record Source, it immediately opens the qryInvoice (I do not see an option to access the loaded subforms), and so I add the below VBA code as a Criteria under the CustomerName, saved and closed the Property Editor: -

    Form!frmInvoice!frmInvoiceSubform!OrderNo

    However, when I return the frmInvoice and I try to test by Selecting a Customer Name and by Order Number, the search result is blank.

    Can you point out to me what I am doing wrong?

    Many thanks

    Sunday, April 10, 2016 2:46 PM
  • The OrderNo expression must be applied to the order no column of your invoice query. I assumed that OrderNo is a candiate key uniquely identifiying.

    Sunday, April 10, 2016 3:36 PM
  • Hi Stefan,

    It is not working. Yes, OrderNumber is a candidate key which is uniquely identifying. In the qryInvoice query, under the Criteria of the OrderNumber field, I opened the Expression Builder and navigated to the exact path of the loaded subform and the path is:

    Forms![frmInvoice]![sfmInvoice].Form![OrderNumber]

    However, when I try testing in the frmInvoice form, by searching by Customer and by OrderNumber, the search is blank.

    Can you say what I am doing wrong?

    Thank in advance for your help.

    Sunday, April 10, 2016 6:01 PM
  • You might like to take a look at InvoicePDF.zip in my public databases folder at:

    https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

    Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.  

    If you have difficulty opening the link copy its text (NB, not the link location) and paste it into your browser's address bar.

    Ken Sheridan, Stafford, England

    • Marked as answer by wirejp Monday, April 11, 2016 3:50 PM
    • Unmarked as answer by wirejp Monday, April 11, 2016 4:00 PM
    • Marked as answer by wirejp Monday, April 11, 2016 6:00 PM
    Sunday, April 10, 2016 6:12 PM
  • Thank you for all of the help.

    @ Ken: your database is very good and I have taken helpful tips from it.

    I have attached my database file for your review. Can you point out or show me how to fix the problem?

    Many thanks.

    Gymn Database

    Monday, April 11, 2016 2:33 AM
  • Using Stefan's explanation and Ken's database example, I can now save the PDF files to a folder (I followed Ken's example to create a separate query called qryPDFInvoice and filter the report's datasource by using the Order Number as the reference to the form). However, how do I save the files with the customer's name + the word "Invoice" as Customer's Name-Invoice (currently my files are saving as 7-ID i.e the CustomerID number-ID)?

    Monday, April 11, 2016 6:25 AM
  • Just modify your Filepath variable in your button click event. There you're setting the name to the ID currently.

    Monday, April 11, 2016 8:25 AM
  • However, how do I save the files with the customer's name + the word "Invoice" as Customer's Name-Invoice (currently my files are saving as 7-ID i.e the CustomerID number-ID)?

    In my demo you'd amend the code as follows:

        strFullPath = varFolder & "\" & Me.Customer.Column(1) & "-Invoice" & ".pdf"

    However, this would allow you to create only one invoice per customer as the file name would be the same for each invoice per customer created.  In my demo the invoice number is also concatenated to the file name.


    Ken Sheridan, Stafford, England

    Monday, April 11, 2016 10:50 AM
  • Thanks, Stefan & Ken. It is working now, as the files are now being saved into a Folder with the customer name and invoice no.

    @ Ken: I tried to import your form called "frmOpen"into my database, but I am getting some debugging errors on the VBA code associated with this form:-

    Line 5: GetFolder () is highlighted, with the compile error: sub or function not defined

    Line 10 is highlighted in yellow and an arrow points to "ChangeProperty" in line 15. The error message is: -

    compile error

    sub or function not defined

    Many Thanks

    Option Compare Database
    Option Explicit
    Private Sub cmdBrowse_Click()
        Dim strFolder As String
        strFolder = GetFolder()
       
        If Len(strFolder) > 0 Then
            Me.FolderPath = strFolder
        End If
        
    End Sub

    Private Sub Form_Close() Const DOCNAME = "frmInvoice" DoCmd.OpenForm DOCNAME If Me!ShowOnStart = False Then ' Change start up form to main form ChangeProperty "StartupForm", dbText, DOCNAME Else ChangeProperty "StartupForm", dbText, "frmOpen" End If End Sub



    • Edited by wirejp Monday, April 11, 2016 3:20 PM
    Monday, April 11, 2016 2:55 PM
  • @ Ken: I tried to import your form called "frmOpen"into my database, but I am getting some debugging errors on the VBA code associated with this form:-

    Line 5: GetFolder () is highlighted, with the compile error: sub or function not defined

    You'll also need to import the basChangeProperty module, or copy and paste the ChangeProperty function from it into a standard module in your database.

    Ken Sheridan, Stafford, England

    Monday, April 11, 2016 3:28 PM
  • Thanks Ken, it is working now. I appreciate all of the help.
    Monday, April 11, 2016 3:50 PM
  • Hi Ken,

    Two more issues: I am getting a debugging error of the VBA code associated with the frmOpenForm, where line 2 is highlighted in yellow and the GetFolder () is heighted with the Compile error:

    Sub or Function is not defined.

    Private Sub cmdBrowse_Click()
    
        Dim strFolder As String
    
        strFolder = GetFolder()
        
        If Len(strFolder) > 0 Then
            Me.FolderPath = strFolder
        End If
         
    End Sub

    when I click on the SAVE PDF button of the frmInvoice, there is a Microsoft Visual Basic debugging error at Line 15 where "MKDir varFolder" is highlighted: -

    Run-time error '75':

    Path/File access error

    Many Thanks

    Private Sub CmdSavePDF_Click()
    
    Const FOLDER_EXISTS = 75
        Const MESSAGE_TEXT1 = "No current invoice."
        Const MESSAGE_TEXT2 = "No folder set for storing PDF files."
        Dim strFullPath As String
        Dim varFolder As Variant
        
        If Not IsNull(Me.cboInvoiceNumber) Then
            ' build path to save PDF file
            varFolder = DLookup("Folderpath", "pdfFolder")
            If IsNull(varFolder) Then
                MsgBox MESSAGE_TEXT2, vbExclamation, "Invalid Operation"
            Else
                ' create folder if does not exist
                varFolder = varFolder & "\" & Me.cboCustomerID.Column(1)
                MkDir varFolder
                strFullPath = varFolder & "\" & Me.cboCustomerID.Column(1) & " " & Me.cboInvoiceNumber & ".pdf"
                ' ensure current record is saved before creating PDF file
                Me.Dirty = False
                DoCmd.OutputTo acOutputReport, "rptInvoice", acFormatPDF, strFullPath, True
            End If
        Else
            MsgBox MESSAGE_TEXT1, vbExclamation, "Invalid Operation"
        End If
    
    Exit_Here:
        Exit Sub
        
    Err_Handler:
        Select Case Err.Number
            Case FOLDER_EXISTS
            Resume Next
            Case Else
            MsgBox Err.Description
            Resume Exit_Here
        End Select
        

    Monday, April 11, 2016 4:14 PM
  • For the first one you need to install the basBrowseForFolder module.

    For the second debug the code by setting a breakpoint early in the procedure and step into the code line by line until you reach the MkDir line, examining the value of the varFolder variable at each line to see at what stage it is not being built correctly.  Debugging the procedure in my demo file in this way will show you what to expect.

    Ken Sheridan, Stafford, England

    Monday, April 11, 2016 4:47 PM
  • Hi Ken

    The first issue is now fixed.

    Second issue is now also fixed (I had changed the name of the table "pdfFolder" to "tblpdfFolder" . I had to update the VBA code to reflect the change).

    This is one last issue to be resolved for my database. I copied your VBA code and modified it for my database in order to send emails to customers, but I get a Compile error on line 6:

    Method or data member not found.

    How do I define the client's email address so that resulting email address automatically appears in the "To" field of Microsoft Outlook email application? I have deleted that line 6 and once I click on the email command button, and the email populates and appears, I manually enter the client's email address in the "To" send field of the Microsoft Outlook email application. The VBA code is currently referencing only the two search combo boxes (cboCustomerID and cboInvoiceNumber) in my Form Header. I have included the email address in the subform called sfmInvoice

    Private Sub CmdEmail_Click()
    
        Dim strTo As String
        Dim strSubject As String
        Dim strMessageText As String
        
        Me.Dirty = False
        
        strTo = Me.Email
        strSubject = "Invoice Number " & Me.cboInvoiceNumber
        strMessageText = Me.cboCustomerID.Column(1) & ":" & _
            vbNewLine & vbNewLine & _
            "Your latest invoice is attached." & _
            vbNewLine & vbNewLine & _
            "Customer Accounts Department, Huco Sport Oy"
    
    
        DoCmd.SendObject ObjectType:=acSendReport, _
            ObjectName:="rptInvoice", _
            OutputFormat:=acFormatPDF, _
            To:=strTo, _
            Subject:=strSubject, _
            MESSAGETEXT:=strMessageText, _
            EditMessage:=True
    
    
    End Sub

    Monday, April 11, 2016 5:13 PM
  • If the email control is in a subform you have two options:

    1.  Include it as a hidden extra column in the CustomerID combo box and reference it via the Column property in the same way as the customer name is referenced.  If it's the third column then it would be Column(2) as the Column property is zero-based.  

    2.  Reference the control in the subform, using syntax Me.SubformControlName.Form.Email.  Note that SubformControlName is the name of the subform control in the parent form's Controls collection, i.e. the control which houses the subform.

    Ken Sheridan, Stafford, England

    Monday, April 11, 2016 5:25 PM
  • Hi Ken,

    Thank you very much. I choose option 2 and the issue is now fixed.

    Thank you very much for being very patient and helping me to resolve these problems. I have learnt a lot in the process.

    Thanks again.

    Monday, April 11, 2016 6:00 PM