[CDAVIS]

This is my first time posting in a tech forum and Im a self-taught Office
user, so I apologize in advance. I am using MS Office 2007 and my OS is XP.
My project: creating a payment coupon book. My database is created in
Access 2007. I am attempting to create a payment coupon book with a Mail
Merge directory. I have the first coupon designed exactly how I want it
using a single row table. My problem: I have a field in Access that tells
the total number of payments that need to be made. I would like MailMerge to
know only to make that specified number of coupons and also for the coupons
to read 1 of #, 2 of #, etc. Also, I would like a running subtotal to be
displayed on each coupon. Any advice would be greatly appreciated. Thanks!

[Doug Robbins - Word MVP]

I think that I would do the whole thing in Access. However to do it in
Word, I think that you will need to use a "roll-your-own" VBA equivalent to
mailmerge.

Such an approach is used in the following which allows the "use" of
formfields as used in protected documents with mailmerge. It may be enough
to get you started.

Sub MailMergefromAccesswithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of the
'fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"CDavis" wrote in message
...
This is my first time posting in a tech forum and Im a self-taught Office
user, so I apologize in advance. I am using MS Office 2007 and my OS is
XP.
My project: creating a payment coupon book. My database is created in
Access 2007. I am attempting to create a payment coupon book with a Mail
Merge directory. I have the first coupon designed exactly how I want it
using a single row table. My problem: I have a field in Access that tells
the total number of payments that need to be made. I would like MailMerge
to
know only to make that specified number of coupons and also for the
coupons
to read 1 of #, 2 of #, etc. Also, I would like a running subtotal to be
displayed on each coupon. Any advice would be greatly appreciated.
Thanks!

[CDAVIS]

Way over my head with the whole VBA aspect, but I am going to try it. What
website should I head to for beginner VBA tutorials?

"Doug Robbins - Word MVP" wrote:

I think that I would do the whole thing in Access. However to do it in
Word, I think that you will need to use a "roll-your-own" VBA equivalent to
mailmerge.

Such an approach is used in the following which allows the "use" of
formfields as used in protected documents with mailmerge. It may be enough
to get you started.

Sub MailMergefromAccesswithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of the
'fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"CDavis" wrote in message
...
This is my first time posting in a tech forum and Im a self-taught Office
user, so I apologize in advance. I am using MS Office 2007 and my OS is
XP.
My project: creating a payment coupon book. My database is created in
Access 2007. I am attempting to create a payment coupon book with a Mail
Merge directory. I have the first coupon designed exactly how I want it
using a single row table. My problem: I have a field in Access that tells
the total number of payments that need to be made. I would like MailMerge
to
know only to make that specified number of coupons and also for the
coupons
to read 1 of #, 2 of #, etc. Also, I would like a running subtotal to be
displayed on each coupon. Any advice would be greatly appreciated.
Thanks!

.

[Doug Robbins - Word MVP]

See http://word.mvps.org/faqs/MacrosVBA/index.htm

--
Hope this helps,

Doug Robbins - Word MVP

Please reply only to the newsgroups unless you wish to obtain my services on
a paid professional basis.

"CDavis" wrote in message
...
Way over my head with the whole VBA aspect, but I am going to try it.
What
website should I head to for beginner VBA tutorials?

"Doug Robbins - Word MVP" wrote:

I think that I would do the whole thing in Access. However to do it in
Word, I think that you will need to use a "roll-your-own" VBA equivalent
to
mailmerge.

Such an approach is used in the following which allows the "use" of
formfields as used in protected documents with mailmerge. It may be
enough
to get you started.

Sub MailMergefromAccesswithFormFields()
Dim dSource As String
Dim qryStr As String
Dim mfCode As Range
Dim i As Long, j As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset
With ActiveDocument
'Get the details of the datasource
With .MailMerge.DataSource
dSource = .Name
qryStr = .QueryString
End With
'Convert the MERGEFIELDS to DOCVARIABLE fields
For i = 1 To .Fields.Count
If .Fields(i).Type = wdFieldMergeField Then
Set mfCode = .Fields(i).code
mfCode = Replace(mfCode, "MERGEFIELD", "DOCVARIABLE")
End If
Next i
'Convert the Mail Merge Main document to a normal Word document
.MailMerge.MainDocumentType = wdNotAMergeDocument
.Protect wdAllowOnlyFormFields, NoReset
End With
' Open the database
Set db = OpenDatabase(dSource)
' Retrieve the recordset
Set rs = db.OpenRecordset(qryStr)
With rs
' Move to the first record
.MoveFirst
j = 1
Do While Not .EOF
'Create variables in the document with the names and values of
the
'fields in each record
For i = 0 To .Fields.Count - 1
If .Fields(i).Value "" Then
ActiveDocument.Variables(.Fields(i).Name).Value =
..Fields(i).Value
End If
Next i
With ActiveDocument
.Fields.Update
.SaveAs "C:\Test\MwithFF" & j
End With
.MoveNext
j = j + 1
Loop
End With
rs.Close
db.Close
Set rs = Nothing
Set db = Nothing
End Sub


--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP, originally posted via msnews.microsoft.com

"CDavis" wrote in message
...
This is my first time posting in a tech forum and Im a self-taught
Office
user, so I apologize in advance. I am using MS Office 2007 and my OS
is
XP.
My project: creating a payment coupon book. My database is created in
Access 2007. I am attempting to create a payment coupon book with a
Mail
Merge directory. I have the first coupon designed exactly how I want
it
using a single row table. My problem: I have a field in Access that
tells
the total number of payments that need to be made. I would like
MailMerge
to
know only to make that specified number of coupons and also for the
coupons
to read 1 of #, 2 of #, etc. Also, I would like a running subtotal to
be
displayed on each coupon. Any advice would be greatly appreciated.
Thanks!

.