Updating formfield before saving a pdf through vba

Question

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

Did you consider combining both Subroutines into one which (rather) should be triggered from Access? In my opinion it will solve this problem. So, no Document_Open event action but only one macro in Access — Kazimierz Jawor
The On Error Resume Next command clears all existing error conditions. Therefore it need not be followed (or preceded) by Err.Clear. — Variatus
Your Access routine opens the document, fills the content of the formfields in doc and then sets Doc = Nothing. The saved version of Doc is never changed. Obviously, if you wish to have the changes in the saved document you would have to save them. — Variatus
@KazimierzJawor I tried merging both macro but couldnt display the msgbox with word through the vba with access. — franckosd
@Variatus i tried taking "doc = nothing" out but it stgill doesnt work. — franckosd

Variatus Variatus · Accepted Answer · 2017-12-23T01:27:47

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.

Updating formfield before saving a pdf through vba

1 Answers