Reply
 
Thread Tools Display Modes
  #1   Report Post  
Posted to microsoft.public.word.mailmerge.fields
CDAVIS CDAVIS is offline
external usenet poster
 
Posts: 4
Default Creating dependency between records in Mail Merge directories

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!
  #2   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Creating dependency between records in Mail Merge directories

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!


  #3   Report Post  
Posted to microsoft.public.word.mailmerge.fields
CDAVIS CDAVIS is offline
external usenet poster
 
Posts: 4
Default Creating dependency between records in Mail Merge directories

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!


.

  #4   Report Post  
Posted to microsoft.public.word.mailmerge.fields
Doug Robbins - Word MVP Doug Robbins - Word MVP is offline
external usenet poster
 
Posts: 8,832
Default Creating dependency between records in Mail Merge directories

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!


.


Reply
Thread Tools
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Merge Multiple Records from Excel into Word Mail Merge Jim[_6_] Mailmerge 1 January 8th 09 11:08 PM
Mail Merge and Directories fez Microsoft Word Help 3 February 28th 08 11:46 PM
mail merge missing records marti Mailmerge 1 June 7th 07 10:02 AM
Combine Mail Merge records when different records have 1 common fi TFFAV Mailmerge 1 December 13th 06 12:56 AM
Producing directories using mail merge nessy Mailmerge 3 August 18th 06 04:43 PM


All times are GMT +1. The time now is 11:27 PM.

Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 Microsoft Office Word Forum - WordBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Word"