Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

2 Record Sources For Word Merge 1

Status
Not open for further replies.

lars7

Technical User
Aug 16, 2005
817
GB
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.
 
Use a query that combines the 2 queries.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
I'd use a report with subreport instead of a word mailmerge

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886
 
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.
 
You can use faq701-4233 with a tab (chr(9) I think) between fields and a carriage return line feed as the delimiter.
Code:
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
Duane
Hook'D on Access
MS Access MVP
 
Hi Duane,

Thanks for this it works great.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top