Access to Multiple Word Pages (1 Viewer)

[david_johnson]

Greetings,

My ultimate goal is to take many fields for many records I have in a query, export them into a fill-able document and email them to clients. The clients need to enter some text into a few boxes (not the fields I'm providing) and send it back. I don't need to collect that data they send in the database.

My thought was to export the records (1,148) to individual Word pages using bookmarks to put the field values where I want them, and then using Word VBA to separate that mega-document into 1,148 Word documents with individual names based on data in the bookmarks. I would use PDF because it's better and stays true with formatting (the boxes jump around on me) but I understand from reading these helpful forums that you can't have fillable forms with Access --> PDF anymore.

Before I ask my direct question, if there's a better method to accomplish my goal I'm all ears.

I've gotten the fields in the first record to export to Word successfully. The problem is it's only the first record. I can't get it to loop. I've included my code below. I'm new to VBA but a decently fast learner. Thanks in advance for your help!

Option Compare Database

Private Sub Command0_Click()
On Error GoTo MergeButton_Err

Dim objWord As Word.Application

'Start Microsoft Word.
Dim d As Database
Dim r As Recordset
Set d = CurrentDb()
Set r = d.OpenRecordset("qtrly_report_test_export")
Set objWord = CreateObject("Word.Application")
Set rs = db.OpenRecordset("qtrly_report_test_export", dbOpenDynaset)


With objWord
'Make the application visible.
.Visible = True

'Open the document.
.Documents.Open ("H:\pmo_processes\quarterly_reports\quarterly_report_mockup.docx")

'Move to each bookmark and insert text from the form.
.ActiveDocument.Bookmarks("applicantname").Select
.Selection.Text = (CStr(r.Fields("Applicant Name")))
.ActiveDocument.Bookmarks("date").Select
.Selection.Text = (CStr(r.Fields("date_today")))
.ActiveDocument.Bookmarks("disaster").Select
.Selection.Text = (CStr(r.Fields("Disaster")))
.ActiveDocument.Bookmarks("DUNS").Select
.Selection.Text = (CStr(r.Fields("DUNS#")))
.ActiveDocument.Bookmarks("estd_completion_date").Select
.Selection.Text = (CStr(r.Fields("estimated completion date")))
.ActiveDocument.Bookmarks("extended_date").Select
.Selection.Text = (CStr(r.Fields("extended date")))
.ActiveDocument.Bookmarks("PWAmount").Select
.Selection.Text = (CStr(r.Fields("PW Amount")))
.ActiveDocument.Bookmarks("pwcompletion_date").Select
.Selection.Text = (CStr(r.Fields("pw_completion_date")))
.ActiveDocument.Bookmarks("pwnbr").Select
.Selection.Text = (CStr(r.Fields("pw#")))
.ActiveDocument.Bookmarks("timeextensiongiven").Select
.Selection.Text = (CStr(r.Fields("Time Extension Given")))

End With

'Print the document in the foreground so Microsoft Word will not close
'until the document finishes printing.
objWord.ActiveDocument.PrintOut Background:=False

'Close the document without saving changes.
objWord.ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges

'Quit Microsoft Word and release the object variable.
objWord.Quit
Set objWord = Nothing
Exit Sub

MergeButton_Err:
'If a field on the form is empty, remove the bookmark text, and
'continue.
If Err.Number = 94 Then
objWord.Selection.Text = ""
Resume Next

'If the Photo field is empty.
ElseIf Err.Number = 2046 Then
MsgBox "Please add a photo to this record and try again."
Else
MsgBox Err.Number & vbCr & Err.Description
End If

Exit Sub
End Sub

 

[JHB]

.. The problem is it's only the first record. I can't get it to loop.

Because you haven't created any loop.

  If Not r.EOF Then
    Do
      'Your code here...
      r.MoveNext
    Loop Until r.EOF
  End If

 

[pbaldy]

You would need to loop the recordset:

Do While Not r.EOF
  'your code here
  r.MoveNext
Loop

Personally, I'd probably just create a new Word doc for each record, since that's you're end goal anyway.

 

[david_johnson]

You would need to loop the recordset:

Do While Not r.EOF
  'your code here
  r.MoveNext
Loop

Personally, I'd probably just create a new Word doc for each record, since that's you're end goal anyway.

---

How would I go about that? It does sound like it would be easier and it would it sounds like it would have the added advantage of staying in Access rather than having to dink around in Word.

 

[pbaldy]

Not at my desktop, but basically you do all the opening and saving of the Word doc inside the loop, saving with something unique to each record so the files don't overwrite each other.

 

[Cronk]

What I do is to use a Word template so that each time the loop is run, a fresh document is opened. When saving, save the output with a generic name and then rename it with perhaps the Client name in the document name. Then generate the email and add the document to the email.