2 Record Sources For Word Merge 1

[lars7]

Hi Guys,
I have the following code to merge a word document and the results of a query (code and Job Title) which works fine.

Set WordApp = New Word.Application
If (Me.TxtQA.Value = "Safe Increase") Or (Me.TxtQA.Value = "Unsafe Increase") Then

Set WordDoc = WordApp.Documents.Open(FileName:="X:\ReviewOutcomes\Review letters\Review outcome increase letter.doc", _
ConfirmConversions:=False, ReadOnly:=False, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", Revert:=False, _
WritePasswordDocument:="", WritePasswordTemplate:="", Format:= _
wdOpenFormatAuto)

Set WordMerge = WordDoc.MailMerge
WordMerge.OpenDataSource _
Name:=CurrentProject().FullName, _
LinkToSource:=True, AddToRecentFiles:=False, _
SQLStatement:="SELECT * From[QueryReviewOutcomeMerge]"

With WordMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With

Set WordMerge = Nothing
WordDoc.Close
Set WordDoc = Nothing

WordApp.Visible = True


What I would also like to do is add the results of another query (a list of staff names attached to the code) into the merge but I can't work out how to do it.

As always any help on this would be greatly appreciated.

 

[PHV]

Use a query that combines the 2 queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Hi PHV,

I tried that but my code puts each record on a different page but I want a list of names like this:

Dear Manager,
You supplied the following list of staff for the code "A1" job title "Doctor":

Paynumber Forename Surname.
A001 John Brown
A002 Jack Green
A003 Jim Black

The code and Job tile are only needed once but the staff list will vary in records.

Hope this is clear and thanks for responding.

 

[PHV]

I'd use a report with subreport instead of a word mailmerge

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[lars7]

Hi PHV,
I had a look at a report but couldn't get the formatting
right (the job title wouldn't go onto the beginning of the next line if required) as the letter needs to be to an approved format.

Thanks for having a look at this for me I guess the user will have to stick to copying and pasting for now.

 

[dhookom]

You can use faq701-4233 with a tab (chr(9) I think) between fields and a carriage return line feed as the delimiter.

Concatenate("Select Paynumber & Chr(9) & Forename  & Chr(9) & Surname From ... where ....", Chr(13) & Chr(10))

There is a sample of how this might work at

http://www.rogersaccesslibrary.com/forum/forum_posts.asp?TID=16.

Duane
Hook'D on Access
MS Access MVP

 

[lars7]

Hi Duane,

Thanks for this it works great.