Word Fill VBA in MS Access for various fields in table

Question

For our incident management side of our database I am trying to have data from fields in my table(s) generate within the 149 Investigative Report, a Word document template provided by the state (see link here).

I made a read-only version of the document to preserve its integrity by forcing a save as by the user and loaded it with text form fields with bookmarks to reference (example: txtcaseintroduction).

I modified code I found in the internet for working with form fields and assigned it to a button on one of my forms to assist in generating the report (the Open reference is modified for security reasons):

Private Sub cmdPrint_Click()

'Export 149 Report.

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.Add("Y:\ABC\2018\Case Files\2018 - Incident Forms\OPWDD 149 - Access Database Reference.docx", , True)

With doc
    .FormFields("txtNIMRS").Result = Me.NIMRSID
    .FormFields("txtInternalID").Result = Me.InternalIncidentID
    .FormFields("txtIncidentDate").Result = Me.[IncidentOccurrenceDate]
    .FormFields("txtDiscoverydate").Result = Me.[IncidentReportDate]
    .FormFields("txtCaseIntroduction").Result = Me.CaseIntroduction
    .FormFields("txtIncidentLocation").Result = Me.Location
    .FormFields("txtBackground").Result = Me.BackgroundInfo
    .FormFields("txtProtections").Result = Me.ImmedProtec
    .FormFields("txtQuestion").Result = Me.InvestQuestion
    .FormFields("txtTestName").Result = Me.[TestimonialEvidence]
    .FormFields("txtDocumentaryE").Result = Me.[DocumentaryEvidence]
    .FormFields("txtDemonstrativeE").Result = Me.[DemonstrativeEvidence]
    .FormFields("txtPhysicalE").Result = Me.[PhysicalEvidence]
    .FormFields("txtWSName").Result = Me.[WrittenStatements]
    .FormFields("txtSummary").Result = Me.SummaryEvidence
    .FormFields("txtConclusions").Result = Me.Text409
    .FormFields("txtRecommendations").Result = Me.Text411
    .FormFields("txtInvestigator").Result = Me.Investigator_s__Assigned
    .FormFields("txtdatereport").Result = Me.Investigative_Report_Completion_Date
.Visible = True

.Activate

End With

Set doc = Nothing

Set appWord = Nothing

Exit Sub

errHandler:

MsgBox Err.Number & ": " & Err.Description

End Sub

The following fields work:

 .FormFields("txtNIMRS").Result = Me.NIMRSID
        .FormFields("txtInternalID").Result = Me.InternalIncidentID
        .FormFields("txtIncidentDate").Result = Me.[IncidentOccurrenceDate]
        .FormFields("txtDiscoverydate").Result = Me.[IncidentReportDate]
.FormFields("txtIncidentLocation").Result = Me.Location
        .FormFields("txtBackground").Result = Me.BackgroundInfo
        .FormFields("txtProtections").Result = Me.ImmedProtec
        .FormFields("txtQuestion").Result = Me.InvestQuestion
 .FormFields("txtConclusions").Result = Me.Text409
        .FormFields("txtRecommendations").Result = Me.Text411
.FormFields("txtdatereport").Result = Me.Investigative_Report_Completion_Date

The remaining fields (case introduction, investigator, and the attachment fields) do not. All of these fields exist on the same table. It is also noted that case introduction used to work, but stopped working as I tried to figure out more form fields to apply to the document and reference. The goal was to have the investigator essentially do all of their work in the database and then export it to the required format for submission to the state.

My question: what do I need to do to the above code to get the non-working fields functional in populating the Word document?

Responding to questions in comments

• No error that occurs; the text-boxes are simply not populating when I engage the button.

• The form fields do not need to be present in the result document. They are simply "targets" for the data.

Answer 1

Since the form fields do not need to be retained in the result document the simplest approach would be to simply insert the data to the FormField.Range, which will replace (remove) the form field. The entire code can be written in this manner if consistency is important (how the end result looks to the user), but from a programming stand-point need not be.

Note: If Forms protection is activated, it needs to be turned off for this approach to work

If doc.ProtectionType <> -1 Then doc.Unprotect  '-1 = wdNoProtection

Sample code line for a string longer than 255 characters

.FormFields("txtCaseIntroduction").Range = Me.CaseIntroduction