Access recordset (from 2 columns) to Word template (or empty document)

Question

I'm trying to export (button click) data from Access recordset (data from 2 columns - "Question" & "Answer") to Word template which has FormFields "Question" & "Answer".

I managed to do it but only until Word document runs out of FormFields (which I added - 100), if recordset has more than 100 rows, it will result with an error "error 5941 the requested member of the collection does not exist" - because it run out of available FormFields (at this time max number of questions is 1100, and it will grow). I can, btw, add several thousand FormFields, but then user will have to delete the empty ones when he uses this option.

I feel like there is an easy way to implement some other kind of loop which will take a document which has only these two FormFields and copy or duplicate first FormField - "Question" and insert data in second, but I still didn't find any way of doing that.

Exporting data from 2 columns in Access recordset, one is "Question" and other "Answer". Code on ButtonClick:

Private Sub cmdToWordMultiple_Click()
Dim db As DAO.Database
Dim rst As DAO.Recordset

Set db = CurrentDb()
Set rst = Me.RecordsetClone
Set Wd = New Word.Application
Set myDoc = Wd.Documents.Add("C:\Users\User\Desktop\ReportQuestions.docx")

Wd.Visible = True
rst.MoveFirst

Do Until rst.EOF

    myDoc.FormFields("Question").Range.Text = Nz(rst!Question, "")
    myDoc.FormFields("Answer").Range.Text = Nz(rst!Answer, "")

rst.MoveNext
Loop

rst.Close
Set rst = Nothing

objWord.Application.Quit

End Sub

I would like to make this button export data from Access recordset to Word Document, without leaving excess FormFields.

Why use Word and not Access report? Possibly instead of FormFields, build a table in Word. — June7
I use Word template because before FormField, I have word "Question: " & "Answer: ", I am using Access report when one Q & A is needed, I just don't have any clue how to export 100 or 1000 Q's and A's in same document, if you have any idea please let me know. — Saša Kristić
Sorry, the limitation you expressed makes no sense to me. I can see no reason why Access report could not accomplish. Would have to know more about your db structure and desired output. But if you are happy with Word merge performance then problem solved. — June7

Cindy Meister Cindy Meister · Accepted Answer · 2019-08-08T10:48:05

Most efficient is to save the form fields as Building Blocks which can be inserted as often as required. Building Blocks can only be saved in a template, so open ReportQuestions.docx then use File/Save As to save it as a dotx.

Select the pair of form fields, then use Insert->Quick Parts->Save selection to quick part gallery. Fill out the fields in the dialog box being sure to select the template ReportQuestions.dotx for "Save in".

To insert the form fields, the code would look something like below. Note, also, how to get to the end of the document on each iteration, before inserting the next pair of form fields. (See also note below the code!)

Dim tmpl as Word.Template
Dim sTmplPath as String
sTmplPath = "C:\Users\User\Desktop\ReportQuestions.dotx"
Set myDoc = Wd.Documents.Add(sTmplPath)
Set tmpl = myDoc.AttachedTemplate

Dim rngEndOfDoc as Word.Range
Set rngEndOfDoc = myDoc.Content
rngEndOfDoc.Collapse wdWollapseEnd
tmpl.BuildingBlockEntries("Name of Entry").Insert Where:=rngEndOfDoc, RichText:=True

This builds on the code shown in the question. Based on my experience with form fields, I suspect this code, as shown, does not work for more than one pair of form fields because form field names are also Word bookmarks. And bookmark names must be unique (can only appear once) in a document. So Question and Answer can only be used once. I don't know what you're actually naming the form fields, but you'll probably need to add code to rename the form fields once they've been inserted.

Added from OP's comment: The suggestion was successfully implemented like this

Do Until rst.EOF 
  tmpl.BuildingBlockEntries("Answer").Insert Where:=rngEndOfDoc, RichText:=True myDoc.FormFields("Answer").Range.Text = "ANSWER: " & Nz(rst!Answer, "") 
  rst.MoveNext 
Loop

Access recordset (from 2 columns) to Word template (or empty document)

1 Answers