CODEWITHSUNDEEP
ms-accessvbams-word
franckosd
franckosd

Reputation: 23

Updating formfield before saving a pdf through vba

I'm a beginner with VBA and coding in general and I'm stuck with a problem with my VBA code. Here's what I want to do :

I have two fillable fields (f_autpar_nom and f_autpar_fiche) with my Access database who need to be on my Word file at two formfield (eleves_nom and eleves_numfiche) with a command_click(). Then, my Word document opens and prompts me with a "do you want to save this" and then the Word document save as a PDF and is sent by email.

Everything is working except one thing : The formfields aren't updated when I print the PDF and return the default message I set (which is "erreur").

What I need is to find a way to update the formfield before my messagebox prompt me to send the email.

Here's the code I have with Access

Function fillwordform()
    Dim appword As Word.Application
    Dim doc As Word.Document
    Dim Path As String

    On Error Resume Next
    Error.Clear
    Path = "P:\Commun\SECTEUR DU TRANSPORT SCOLAIRE\Harnais\Autorisations Parentales\Autorisation parentale vierge envoyée\Autorisation_blank.docm"
    Set appword = GetObject(, "word.application")

    If Err.Number <> 0 Then
        Set appword = New Word.Application
        appword.Visible = True
    End If
    Set doc = appword.Documents.Open(Path, , False)
    With doc
        .FormFields("eleves_nom").Result = Me.f_autpar_nom
        .FormFields("eleves_numfiche").Result = Me.f_autpar_fiche
        appword.Visible = True
        appword.Activate
    End With

    Set doc = Nothing
    Set appword = Nothing    
End Function

Private Sub Commande47_Click()
    Dim mydoc As String
    mydoc = "P:\Commun\SECTEUR DU TRANSPORT SCOLAIRE\Harnais\Autorisations Parentales\Autorisation_blank.docm"
    Call fillwordform
End Sub

and with Word 

Private Sub document_open()
    Dim outl As Object
    Dim Mail As Object
    Dim Msg, Style, Title, Help, Ctxt, Response, MyString
    Dim PDFname As String

    Msg = "L'autorisation sera sauvegardée et envoyée par email.  Continuer?"
    Style = vbOKCancel + vbQuestion + vbDefaultButton2
    Title = "Document"
    Ctxt = 1000
    Response = MsgBox(Msg, Style, Title, Help, Ctxt)
    If Response = vbOK Then
        ActiveDocument.Save
        PDFname = ActiveDocument.Path & "\" & "Autorisation Parentale " & FormFields("eleves_nom").Result & ".pdf"
       ActiveDocument.SaveAs2 FileName:=PDFname, FileFormat:=wdFormatPDF
        Set outl = CreateObject("Outlook.Application")
        Set Mail = outl.CreateItem(0)
        Mail.Subject = "Autorisation parentale " & FormFields("eleves_nom").Result & " " & FormFields("eleves_numfiche")
        Mail.To = ""
        Mail.Attachments.Add PDFname
        Mail.Display
        Application.Quit SaveChanges:=wdDoNotSaveChanges

    Else
        MsgBox "Le fichier ne sera pas envoyé."
        Cancel = True
    End If
End Sub

Upvotes: 2

Views: 386

Answers (1)

Variatus
Variatus

Reputation: 14383

I didn't mean to remove the Set Doc = Nothing. My intention was to point out that whatever changes you made before that command must be lost because they weren't saved. In the code below the document is closed and saved.

Private Sub Commande47_Click()
    Dim mydoc As String
    mydoc = "P:\Commun\SECTEUR DU TRANSPORT SCOLAIRE\Harnais\Autorisations Parentales\Autorisation_blank.docm"
    Call FillWordForm
End Sub

Function FillWordForm(Ffn As String)

    Dim appWord As Word.Application
    Dim Doc As Word.Document

    On Error Resume Next
    Set appWord = GetObject(, "word.application")
    If Err.Number Then Set appWord = New Word.Application
    appWord.Visible = True
    On Error GoTo 0

    Set Doc = appWord.Documents.Open(Ffn, , False)
    ' the newly opened document is activated by default
    With Doc
        .FormFields("eleves_nom").Result = Me.f_autpar_nom
        .FormFields("eleves_numfiche").Result = Me.f_autpar_fiche
        .Close True             ' close the file and save the changes made
    End With

    Set appWord = Nothing
End Function

However, I also agree with @Kazimierz Jawor that your construct is unfortunate. Basically, the document's On_Open procedure should run when you open the document from Access. Therefore the email is probably sent before you even get to setting the form fields. My suggestion to save the changes is likely to take effect only when you run the code the second time.

The better way should be to send the mail from either Access or Word. My preference would be the latter. It should be easy to extract two values from an Access table using Word, add them to a Word document and mail out the whole thing. I don't see, however, why you should use the Open event to do that. If that choice is the more logical one then doing everything from within Access would be the conclusion.

Upvotes: 0

Related Questions