none
Getting a button to work properly RRS feed

  • Question

  • I am having difficulties having a button perform what I want. I have an MS Access db which I input all of the information for a particular project. I also have a contract word document which is mail merged with this db.

    So far with the help of some of you I've gotten this far (code below). It works, but if I have 120 records when the button is pressed it creates a long contract with all 120 records. I simply want to have just the current record (the record on my screen at the time) to only make a pdf.

    I would also like to change where the file is saved to.

    Option Compare Database
    Option Explicit
    
    Private Sub Command205_Click()
    Dim strWordDoc  As String
    
        'Path to the word document of the Mail Merge
        '###-1 CHANGE THE FOLLOWING LINE TO POINT TO YOUR DOCUMENT!!
        strWordDoc = "C:\Users\.....\01- Proposal\contract.docx"
    
        ' Call the code to merge the latest info
        startMerge strWordDoc
    
    End Sub
    
    
    '----------------------------------------------------
    ' Auto Mail Merge With VBA and Access (Early Binding)
    '----------------------------------------------------
    ' NOTE: To use this code, you must reference
    ' The Microsoft Word 14.0 (or current version)
    ' Object Library by clicking menu Tools > References
    ' Check the box for:
    ' Microsoft Word 14.0 Object Library in Word 2010
    ' Microsoft Word 15.0 Object Library in Word 2013
    ' Click OK
    '----------------------------------------------------
    Function startMerge(strDocPath As String)
        Dim oWord           As Word.Application
        Dim oWdoc           As Word.Document
        Dim wdInputName     As String
        Dim wdOutputName    As String
        Dim outFileName     As String
    
        ' Set Template Path
        wdInputName = strDocPath            ' was CurrentProject.Path & "\mail_merge.docx"
    
        ' Create unique save filename with minutes and seconds to prevent overwrite
        outFileName = Me.Product_Name.Value & " - " & Me.Client_Name.Value
    
        ' Output File Path w/outFileName
        wdOutputName = CurrentProject.Path & "\" & outFileName
    
        Set oWord = New Word.Application
        Set oWdoc = oWord.Documents.Open(wdInputName)
    
        ' Start mail merge
    
        '###-2 CHANGE THE SQLSTATEMENT AS NEEDED
        With oWdoc.MailMerge
            .MainDocumentType = wdFormLetters
            .OpenDataSource _
                Name:=CurrentProject.FullName, _
                ReadOnly:=True, _
                AddToRecentFiles:=False, _
                LinkToSource:=True, _
                Connection:="QUERY mailmerge", _
                SQLStatement:="SELECT * FROM [Contract Information] Where Id = " & Me.ID.Value & ""  ' Change the table name or your query"
            .Destination = wdSendToNewDocument
            .Execute Pause:=False
        End With
    
        ' Hide Word During Merge
        oWord.Visible = False
    
        ' Save file as PDF
        ' Uncomment the line below and comment out
        ' the line below "Save file as Word Document"
        '------------------------------------------------
        oWord.ActiveDocument.SaveAs2 wdOutputName & ".pdf", 17
    
        ' Save file as Word Document
        ' ###-3 IF YOU DON'T WANT TO SAVE AS A NEW NAME, COMMENT OUT NEXT LINE
        'oWord.ActiveDocument.SaveAs2 wdOutputName & ".docx", 16
    
        ' SHOW THE DOCUMENT
        oWord.Visible = True
    
        ' Close the template file
        If oWord.Documents(1).FullName = strDocPath Then
            oWord.Documents(1).Close savechanges:=False
        ElseIf oWord.Documents(2).FullName = strDocPath Then
            oWord.Documents(2).Close savechanges:=False
        Else
            MsgBox "Well, this should never happen! Only expected two documents to be open"
        End If
    
        ' Quit Word to Save Memory
        'oWord.Quit savechanges:=False
    
        ' Clean up memory
        '------------------------------------------------
        Set oWord = Nothing
        Set oWdoc = Nothing
    
    End Function


    • Edited by alendd Saturday, December 3, 2016 5:20 PM
    Saturday, December 3, 2016 5:17 PM

All replies

  • I simply want to have just the current record (the record on my screen at the time) to only make a pdf.



    The simplest way to output a record as a PDF file is to create a report whose RecordSource property is a query which references a control on the form bound to the primary key of the relevant table as a parameter.  Then call the OutputTo method of the DoCmd object.  You'll find an example in 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.

     

    This little demo file uses the following code to output the current invoice as a PDF file named with the customer name and invoice number.  In my case the file is saved to a subfolder in a folder whose path is entered in the opening form at first start-up.

     

    Private Sub cmdPDF_Click()

     

      On Error GoTo Err_Handler

       

        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.InvoiceNumber) 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.Customer.Column(1)

                MkDir varFolder

                strFullPath = varFolder & "\" & Me.Customer.Column(1) & " " & Me.InvoiceNumber & ".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

     

    End Sub

     



    Ken Sheridan, Stafford, England

    Saturday, December 3, 2016 6:40 PM