Export Multiple records to MailMerge

  • Thread starter Thread starter 4charity
  • Start date Start date
4

4charity

I have an Access database that I have successfully automated to create
MailMerge letters in Word.

However, I also have some letters that need to contain information from more
than one record. I have added a [Print] field to my form, so that users can
select which records they would like to be used in the letter. Then I have
created a query which displays the [InvoiceNumber] and [AmountInvoiced] from
only those records with the [Print] field selected.

So the question: How do I get the info from access to display in my letter
in Word? I would like something like this:

"Attached are the audit reports for the following [vendor name] invoices:
[InvoiceNumber1] in the amount of [AmountInvoiced1]; [InvoiceNumber2] in the
amount of [[AmountInvoiced2]; and [InvoiceNumber3] in the amount of
[AmountInvoiced3]. "

The rest of the fields are consistent amongst the records.
Thanks.
 
I have an Access database that I have successfully automated to create
MailMerge letters in Word.

However, I also have some letters that need to contain information from more
than one record. I have added a [Print] field to my form, so that users can
select which records they would like to be used in the letter. Then I have
created a query which displays the [InvoiceNumber] and [AmountInvoiced] from
only those records with the [Print] field selected.

So the question: How do I get the info from access to display in my letter
in Word? I would like something like this:

"Attached are the audit reports for the following [vendor name] invoices:
[InvoiceNumber1] in the amount of [AmountInvoiced1]; [InvoiceNumber2] in the
amount of [[AmountInvoiced2]; and [InvoiceNumber3] in the amount of
[AmountInvoiced3]. "

The rest of the fields are consistent amongst the records.
Thanks.
Click to expand...

There's sample code in Access Developer's Handbook... in a nutshell,
create an ADO recordset of the InvoiceNumber and AmountInvoiced.
Write a function that uses the GetString method of the ADO recordset
to create a delimited string, pass that to Word, stuff it in your
document somewhere and then convert it to a table. Format your table,
and you're good to go.
 
I have an Access database that I have successfully automated to create
MailMerge letters in Word.

However, I also have some letters that need to contain information from more
than one record. I have added a [Print] field to my form, so that users can
select which records they would like to be used in the letter. Then I have
created a query which displays the [InvoiceNumber] and [AmountInvoiced] from
only those records with the [Print] field selected.

So the question: How do I get the info from access to display in my letter
in Word? I would like something like this:

"Attached are the audit reports for the following [vendor name] invoices:
[InvoiceNumber1] in the amount of [AmountInvoiced1]; [InvoiceNumber2] in the
amount of [[AmountInvoiced2]; and [InvoiceNumber3] in the amount of
[AmountInvoiced3]. "

The rest of the fields are consistent amongst the records.
Thanks.
Click to expand...

Now that I think about it, you can create a query and then use the
fConcatChild function at Access Web, which is here:
http://www.mvps.org/access/modules/mdl0004.htm

Then use the query as the source for your mail merge. You might want
to change the delimiter in there from a semi-colon to something
else... like a comma...

'Are we sure that 'sub' records exist
With rs
If .RecordCount <> 0 Then
'start concatenating records
Do While Not rs.EOF
varConcat = varConcat & rs(strFldConcat) & ";" '<---
Change the semi-colon to something else, e.g. a comma
.MoveNext
Loop
End If
End With
 
4charity,
I'm trying to do a similar thing with scheduled dates and sites. Were you
able to figure something out?
 
Back
Top