Help with creating / merging cover sheets using Access and Word

[Guest]

From a newbie…

In my Access database I have over 100 records of individual forms that are
issued in packages of 10 or more on a regular basis. Each of the forms uses
the same Word document for its cover sheet. When issuing a new package of
forms the cover sheet needs to have the form’s name, its number and the
unique number of each form issued For example:
Form Name: ABCDE
Form Number: 07-BB-123
Numbers issued: 0001 - 0010

I also need Access to store a record of each of the cover sheets issued and
the corresponding last number issued in each package (Form Name ABCDE, Form
Number 07-BB-123, Ending number: 0010). Ideally, I would be able to generate
a cover sheet by clicking on the record of the last cover sheet issued for a
particular form.

Is this feasible? If so, how can I set this up?

 

[Arvin Meyer [MVP]]

You can use a form and automation to fill Word bookmarks. Have a look at the
code at:

http://www.datastrat.com/Code/WordMerge.txt

 

[Guest]

Thanks, Arvin.

Can you recommend a tutorial/reference source that will walk me through
exactly what steps I need to take? For instance, do a create a new form
which holds the fields that will populate the Word document, or can I use my
data entry form that holds those fields?

Thanks, again.

 

[Arvin Meyer [MVP]]

The example I've supplied in the code below uses controls on an existing
form. The Word bookmarks must be built in a Word file. While not necessary,
the Word file I use in the code mentioned below uses a Word Template file
(Letter.dot) so it can be used over and over again. It prints the changes
and doesn't save them. That can be modified to print to a PDF or save to
another file (I think).
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

 

[Guest]

Thanks, Arvin, for your help.

I edited the code to reflect my project and added the revised code to the
Form_Number field in my Cover Sheet subform. After doing that the list of
all previously issued forms disappeared. Obviously, I've done something
wrong. Can you look at my revised code at the end of this message and see
where I went wrong?

Will using this code allow me to generate a cover sheet by clicking on the
record of the last cover sheet issued for a particular form? (Example:
Assuming I am issuing another 25 forms for AS-007-001 and the last number
issued was 350, will clicking on the form number AS-007-001 / 350, generate a
cover sheet that also merges with an Excel document to fill in numbers issued
(351 through 375)? Also, will this code track the cover sheets issued and
the last number used?

Here's exactly what I pasted in as the Expression:

Private Sub cmdMergeLetter_Click()
' © 1999 Arvin Meyer
' This code was written by Arvin Meyer
' It is not to be altered or distributed,
' except as part of an application.
' You are free to use it in any application,
' provided the copyright notice is left unchanged.

On Error GoTo Err_cmdMergeLetter_Click

Dim WordTemplate As String
Dim strFullName As String
Dim objWord As Word.Application

Set objWord = CreateObject("Word.Application")

WordTemplate = Word.C:\Documents and Settings\Etta\Desktop\& "\
Test_Cover_Sheet_Merge.dot "
strFullName = Me.txtForm_Number & " " & Me.txtForm_Title

With objWord
.Visible = True
.Documents.Add (WordTemplate)
.Caption = "Cover Sheet " & strFullName & "

' If document is protected, Unprotect it.
If .ActiveDocument.ProtectionType <> wdNoProtection Then
.ActiveDocument.Unprotect Password:=""
End If

.ActiveDocument.Bookmarks("Form_ Number").Select
.Selection.Text = (CStr(Me.txtForm_Number))
.ActiveDocument.Bookmarks("Form_Title").Select
.Selection.Text = (CStr(Me.txtForm_Title))
. .Activate

' ReProtect the document.
If .ActiveDocument.ProtectionType = wdNoProtection Then
.ActiveDocument.Protect Type:=wdAllowOnlyFormFields, NoReset:=True
End If

.ActiveDocument.PrintOut
.ActiveDocument.Close (wdDoNotSaveChanges)

End With

Exit_cmdMergeLetter_Click:
objWord.Quit
Set objWord = Nothing
Exit Sub

Err_cmdMergeLetter_Click:
MsgBox Err.Number & ": " & Err.Description, vbInformation, "Error"
Resume Exit_cmdMergeLetter_Click

End Sub


~ Etta

 

[Arvin Meyer [MVP]]

No, what this code does is open a Word document based on your template, and
write the data from your current record to 2 bookmarks:

Form_ Number
and
Form_Title

It only merges the current record, nothing else. To use a merge of 25
records, I usually start the merge from Word and connect it to an Access
Query. Word MVP Cindy Meister has an excellent FAQ on Word merging:

http://homepage.hispeed.ch/cindymeister/mergfaq.htm
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com