Merge data from Access into a Word template

[currentdb]

Hi all,

I'm trying to merge my Access data (specific fields) to a World template containing also the same specific fields.

The problem is that my code does not run and there's no any error message that appears. The button I put on the form will not react on the first click...but on the second click it opens the word application with the template document, but it does not fill the required fields.

Here's the code:

Private Sub Merge_Word_Click()
'Declare Word variables
Dim appWord As Word.Application
Dim doc As Word.Document
'Avoid error 429, when Word isn't open.
On Error Resume Next
Err.Clear
'Set appWord object variable to running instance of Word.
Set appWord = GetObject(, "Word.Application")
If Err.Number <> 0 Then
'If Word isn't open, create a new instance of Word.
Set appWord = New Word.Application
End If
Set doc = appWord.Documents.Open("C:\Documents and Settings\Mes documents\Documents Access\DEV\rappel2.doc", , True)
With doc
.FormFields("DESTINATAIRE").Result = DESTINATAIRE
.FormFields("ADRESSE").Result = ADRESSE
.FormFields("MUNICIPALITE").Result = MUNICIPALITE
.FormFields("PROVINCE").Result = PROVINCE
.FormFields("CODE_POSTAL").Result = CODE_POSTAL
.FormFields("NO_DOSSIER").Result = NO_DOSSIER
.FormFields("AGENT").Result = AGENT
.FormFields("NO_TEL_AGENT").Result = NO_TEL_AGENT
.Visible = True
.Activate
End With
Set doc = Nothing
Set appWord = Nothing
Exit Sub
errHandler:
MsgBox Err.Number & ": " & Err.Description
End Sub

 

[MarkK]

You need to remove ...

On Error Resume Next

... from your code. This statement has the effect that if an error occurs execution simply continues without indication or warning, and you deny yourself the opportunity to understand and deal with what is actually wrong.
Also, when posting code, notice the '#' in the toolbar above the text window. If you highlight code and click that button, or wrap text you type in CODE tags, your indents are preserved and your code is easier to read.
Cheers,

 

[currentdb]

Hi Lagbolt,

I commented out the ''On Resume Next'' line. Now I have an error ''5941: The required field does not exist'' and the line highlighted is .FormFields("DESTINATAIRE").Result = DESTINATAIRE
Even if I comment this line out, it moves on to the second line.

And btw, commenting out the ''on resume next'' line does not open the Word application.
But all the field names are the same in the Word template document and the Access table.

I'll remember to use '#' next time when posting the code..

How I can make it work ? I mean the entire code. Maybe it's not the right way to do a Word merge...

Thanks.

 

[MarkK]

CurrentDB:
The process of debugging is sometimes incremental, that is, you solve the error in front of you, but this may reveal a new error. It looks like you've been writing your code with 'On Error Resume Next' hiding all the errors so I expect you are now going to find a lot of them.

I don't know anything significant about Word or Mail Merge.

I would clean up your code like this, which creates and opens a new instance of Word ...

Private Sub Merge_Word_Click()
   Dim app As New Word.Application
   Dim doc As Word.Document
   
   app.Visible = True
   
   Set doc = app.Documents.Open("C:\Documents and Settings\Mes documents\Documents Access\DEV\rappel2.doc", , True)
   With doc
      .FormFields("DESTINATAIRE").Result = DESTINATAIRE
      .FormFields("ADRESSE").Result = ADRESSE
      .FormFields("MUNICIPALITE").Result = MUNICIPALITE
      .FormFields("PROVINCE").Result = PROVINCE
      .FormFields("CODE_POSTAL").Result = CODE_POSTAL
      .FormFields("NO_DOSSIER").Result = NO_DOSSIER
      .FormFields("AGENT").Result = AGENT
      .FormFields("NO_TEL_AGENT").Result = NO_TEL_AGENT
   End With
   
   Set doc = Nothing
   Set app = Nothing

End Sub

... but I expect you'll still get your 'required field does not exist' error since this doesn't address that.
Where I think you have moved things ahead is that you are dealing with an actual error. But I'll spend a little more time on it and post back with findings.
Cheers,

 

[MarkK]

I just created a mail merge document and that document has a Fields collection, not a FormFields collection. (Well, it has FormFields, but it's empty) But Fields can only be addressed using a long integer. Probably not the same thing you are working with...
But I'm out of time with this,
Best,

 

[currentdb]

Hi Lagbolt,

Thank you for your help. Even after cleaning my code with the one you suggested, I'm still having the same error. I don't know what else to do to make it work.

The other code I wrote, I put it in a module. Maybe writing the rest in a module would be a lot easier than if I execute the code from a button on a form. For now the code is only opening Word. I'm still trying to figure how I can adapt it to open the Word template document and do the merge.

Option Compare Database
Public WordApp As Word.Application
Public doc As Word.Document
Public sel As Word.Selection

Public Sub WordEx()
Set WordApp = New Word.Application
WordApp.Documents.Add
Set doc = WordApp.ActiveDocument
Set sel = WordApp.Selection
WordApp.Visible = True
End Sub

If you have any ideas, hope you can help.

Thanks again.

 

[straykat]

CurrentDB:
The process of debugging is sometimes incremental, that is, you solve the error in front of you, but this may reveal a new error. It looks like you've been writing your code with 'On Error Resume Next' hiding all the errors so I expect you are now going to find a lot of them.

I don't know anything significant about Word or Mail Merge.

I would clean up your code like this, which creates and opens a new instance of Word ...

Private Sub Merge_Word_Click()
   Dim app As New Word.Application
   Dim doc As Word.Document
 
   app.Visible = True
 
   Set doc = app.Documents.Open("C:\Documents and Settings\Mes documents\Documents Access\DEV\rappel2.doc", , True)
   With doc
      .FormFields("DESTINATAIRE").Result = DESTINATAIRE
      .FormFields("ADRESSE").Result = ADRESSE
      .FormFields("MUNICIPALITE").Result = MUNICIPALITE
      .FormFields("PROVINCE").Result = PROVINCE
      .FormFields("CODE_POSTAL").Result = CODE_POSTAL
      .FormFields("NO_DOSSIER").Result = NO_DOSSIER
      .FormFields("AGENT").Result = AGENT
      .FormFields("NO_TEL_AGENT").Result = NO_TEL_AGENT
   End With
 
   Set doc = Nothing
   Set app = Nothing
 
End Sub

... but I expect you'll still get your 'required field does not exist' error since this doesn't address that.
Where I think you have moved things ahead is that you are dealing with an actual error. But I'll spend a little more time on it and post back with findings.
Cheers,

I am working on a similar Access --> Word type product as the original poster and I've gotten this code to work for me (thanks for posting it!). I'm thinking of incorporating drop-down combo boxes in my Access form in addition to the Form Fields. This code should work for combo boxes as well, right? If so, how would I manipulate the code to pull from the selected data in the Combo Box vice the Form Fields? Thanks for your help!

 

[magster06]

I commented out the ''On Resume Next'' line. Now I have an error ''5941: The required field does not exist'' and the line highlighted is .FormFields("DESTINATAIRE").Result = DESTINATAIRE
Even if I comment this line out, it moves on to the second line.

I dont know if this will help or not, but this is how I have the textbox from my access form linked to the word doc:

  .FormFields("field on your word form here").Result = Nz(Me![whatever your textbox is name goes here])

The field DESTINATAIRE, is this named in your word document under "Field Settings" and then under "Bookmark"?

 

[magster06]

Crap! I didnt see where this post was 2 yrs old. Sorry!

This code should work for combo boxes as well, right? If so, how would I manipulate the code to pull from the selected data in the Combo Box vice the Form Fields?

Maybe:

.FormFields("fldInvestigator").Result = Me.comboboxname

 

[straykat]

Crap! I didnt see where this post was 2 yrs old. Sorry!



Maybe:

.FormFields("fldInvestigator").Result = Me.comboboxname

Awesome! I was almost there. I didn't have the Me. in front of the combo box name, but once I added that, like you suggested, it worked!!! Thanks so much!!!

 

[magster06]

I am glad I could help!

 

[straykat]

I keep wanting to get fancier and fancier with this project...so now I've added a SubForm to my original form. How would I manipulate the above code to include a formfield or combobox from the subform in my push to Word? Thanks so much for your help!!