Acccess & Word Automation - Merging records & a subquery to word

Evolve2k,

Lets assume that you have a main form (1 record for each <GivenName> that you want to merge to a word document), then on that form, you could have a subform (based on your sub-query), which enumerates each of the services to which each <GivenName> might subscribe; i.e. the relationship is a many-to-many between persons and/or organizations, and the services they may subscribe to.

Then in code you have already mentioned you have for the button, add some additional code, which iterates through the subform's records, and dumps the data to the Word document.

For example:

As before ... open Word and drop data into document

' insert some whitespace

' insert column headers for service, date initiated and est. hours.

' now, using DAO, drop remaining data on this merge document

Dim rs As DAO.Recordset
Set rs = me.<name of subform>.Form.RecordsetClone
rs.MoveFirst
While Not rs.EOF
    ' drop fields rs!Description, rs!DateInitiated and rs!estHours from sub-ordinate record into Word document
    rs.MoveNext
Wend
Set rs = Nothing


Regards.

Ok just to clarify, I don't have alot of experience with word automation, my code to date effectivley comes from the past EE Question mentioned above. As I understand it all I am doing when I press the button on the form, is shaping up the 'record set' to merge by creating a query on the fly, opening word, and then forcing a mail merge to an existing word document.

If possible could I get you to check the other Q and see where abouts the above code would best fit.

Im thinking that im not really dropping merge fields in the word doc, so Im getting confused how I'll drop this info in.

fredthered u still with us?

Ok the first site gave me this breakdown:

"Group multiple items for a single condition
-------------------------------------------------
Sometimes, you want to create a mail merge where you can list several items for one particular category, such as the invoices for each customer. Word does not have an automatic facility for this kind of merge, but there are workarounds.

1. You can use the InsertDatabase field to link the category information from the merge to the same category from the database. The list will be in a table. The main drawback of this method is the restricted possibilities for formatting.

2. A combination of IF and SET fields can check whether a merge record meets the criteria, if it does the information is listed, otherwise the merge moves to the next record. Note that you will only see the end results of this method after merging to either a new document or to the printer; you will not seem them when viewing the merge result in the main merge document.

3. You can create a user-defined function in your Access database that concatenates all items for the list into a single string. Place this function in an expression in the query you will be using as a datasource so that it can be selected as a single field in the mail merge. Please note that this method only works when the merge link method is DDE; ODBC drivers do not recognize user-defined functions.

4. If none of these methods is satisfactory, then you can forego the built-in mail merge process entirely and use OLE-Automation or DDE to create your Word documents from your database application or a VB or Delphi interface."

Thanks Dreamboat.

Ok I understand very generally from the text what the different options are. Ive never however tried any of these options. Anyone have comments as to which approach is most relevant or how I may go about achieving it?

Also regarding option 4 'forego the built-in mail merge process entirely' I would probably like to stick to the built in merge triggered from my access db, unless opt 4 really is the easiest/ most effective method.

I accomplished the non-vba method once--a long time ago. But it's time consuming.
I always send it to my coder because I don't code. :)

I've ALWAYS wanted to get an addin created, but the code must be so specific that it would be more like a $200 application (sell price) than a $29.95...

I will sometimes offer to help the person by setting it up in Access.
They send.
I import.
Build report.
Send back.
I realize this is no good for making separate, electronic documents.
What about PDF? I don't use Adobe enough to know if a multi-page report can be broken down into separate files easily.

Well, Im definatley needing to output to word, I need the user to be able to fully edit the report once generated, and this is not possible from Access Reports / PDF.  Although Ive taken your approach above on many occasions, "just send it to me and ill do the report" this time around the spec requires I get it to word.

Ok just to keep u updated, Im looking into option 1 above. The link below took me to the following article on 'Compound Mergers' which sounds like sorta what im looking for. Ill give this a go 1st.


http://www.knowhow.com/Guides/CompoundMerges/CompoundMerge.htm
"Compound Merges
Word has two main types of merge: Form Letters and Catalogs. A Form Letter merge creates a separate page for each record in the data file. A Catalog merge creates a separate paragraph for each record. If you are unfamiliar with this distinction, you should refer to Word’s help feature, or try some merges of both types.

There remains, however, a class of document that Word cannot easily create. These I call Compound Merges. A Compound Merge is one in which you need a separate page for each record in a data file, but each of those records is associated with a group of related records, all of which must appear on the same page. An invoice is a typical example. If you are creating invoices for all your customers, you want each invoice to start on a fresh page, but you want all invoice detail items for a customer to appear on that customer’s invoice."

As I said b4 any other ideas/ comments from anyone on a good approach still welcome.

Sorry, Still learning how this file sharing works.
Mailmerge.zip  at http://f2.pg.briefcase.yahoo.com/mdmackillop

Any update here, Evolve?

Yeah sorry for the delay, things have got real busy around here.
Ok, mdmackillop thanks heaps for your input ive downloaded your link and this seems to be on the right track. Basically I now understand how the nextRecord works so my idea is this build one big query with all the info I want and then use nextRecord to step down between records while still in the same merge doc.

Two Questions:
In your zip there were 6 files:
MergeAll.doc
MergeData.rtf
MergeFoot.doc
MergeHead.doc
MergeOne.doc
Services.mdb

1. I understand how MergeAll & MergeOne are linked into the DB but I couldnt see how u were using the other files ie MergeData, MergeHead and MergeFoot, are they needed?

2. Also you mentioned that by using a .rtf things go more efficient, I like the idea but can you help me a little to understand how it works..
Your code is as follows:
    Dim stDocName As String
    stDocName = "QueryOne"
    MergeDoc = "C:\Atest\MergeOne.doc"
    DoCmd.OutputTo acOutputQuery, stDocName, acFormatRTF, DATAFILE
    OpenWord MergeDoc, DATAFILE
    DoCmd.Close acQuery, stDocName

As I understand it stDocName refs your query amd MergeDoc refers to obviously the mergedoc.
So what happens from there?

Thanks so much.

Evolve2k

Hi,
I use “C:\Database\MergeData.rtf” as the output file for all my mail merge queries from access.  The code in access is therefore similar for all forms etc., only the query name changes to create the necessary information, and the name of the mailmerge document to be opened.
Any mailmerge file I create has the same MergeData file as the DataSource and contains the same code running within the document.  Creating new documents is therefore much simplified. I also find that Word/Access communication is very slow, and this way speeds up the end document creation.

The problem with the complex merge is getting the single “header” with the multiple records below for all the documents created.  The MergeOne option is straightforward.

My solution is to create two “associated” documents (MergeHead, MergeFoot), based on the MailMerge doc, and to insert these within a long string of identical NextRecord fields to create the final documents.  The tricky bit is analysing the MergeData file to get the correct “split” points.  I’m still working on this, to find the quickest/best solution.  Reading the MergeData file is OK for 20-30 records, but it slows down dramatically with 250 or so records.

This is a task I’ve been working on for a while, and any alternative approaches are welcome.
MD

OK Ill mull over all that and keep you posted..
Thanks again
Evolve2k

Ok Ive managed to mainly reproduce your results, the next Record is the main function which really enables me to utilise a whole query in one merge record.  

1. Next issue, is there any way after using next record to set it back to the first record.
2. Or otherwise is it possible to rerefer to the query again (or maybe even another query).
3. Do you think Im basically stuck merging against one big consolidated query and Ive got one run through utilisng nextRecord?
This is what Im hoping to achive from this..

Eg.
"Outlined below is a summary of our service proposition followed by a more detailed explaination of the various services being used..
"SUMMARY"
<ServiceDesc>  <estHours>
<nextRecord>   <ServiceDesc>  <estHours>
<nextRecord>   <ServiceDesc>  <estHours>
<nextRecord>   <ServiceDesc>  <estHours>

'And then reset to start so I can go something like
"DETAILED EXPLAINATION"
<ServiceDesc>
<DetailedExplaination>

<NextRecord><ServiceDesc>
<DetailedExplaination>

<NextRecord><ServiceDesc>
<DetailedExplaination>

<NextRecord><ServiceDesc>
<DetailedExplaination>


..and get something like..

"Outlined below is a summary of our service proposition followed by a more detailed explaination of the various services being used..

SUMMARY
Tax Preparation                    3hrs
Investment Analysis             10hrs
General Advice                    20hrs

DETAILED EXPLAINATION
Tax Preparation
Tax Preparation involves many hours of doing xyz...

Investment Analysis
We look at stocks , we analyse this and that and give you Results.

General Advice
Overtime we will assist you to do great things."

I think with a bit of work you might manage to create such a document for a single merge, possibly by creating two or more separate merge documents and then combining them into one consolidated final document.  Something along the lines of:
MergeLetter1 SendToNeWDocument Doc1
MergeLetter2 SendToNeWDocument Doc2
MergeLetter3 SendToNeWDocument Doc3
Then
Doc1:      goto Bookmark InsertFile Doc2
      goto Bookmark InsertFile Doc3
As for doing a multiple mail merge, if the above method works, I suppose one could rerun the code for each new addressee.  One step at a time I think.
Are you looking to produce single letters or a more than that.  If so, how many?
A completely different approach might be to export your data to excel, and manipulate your data there suitable for your mailmerge document.  It depends upon the variety and frequency of use which approach might serve best.
By the way, I used the MergeHead and MergeFoot documents as a way of distributing the code.  You could look at using AutoText instead

Basically Im looking to just produce single reports.
From my PlanBuilder form in the database the user picks the client, ticks some boxes and makes a few selections then presses the [Make Report] button and 'wham' the merged report pops out in word ready to print or edit as they desire.

I want the user not to have to fiddle to get the process working so code behind a cmd button as well as getting my word merge docs setup properly is the key as I see it.

A. Ok so just following on from your comments,  so Ive been able to merge separate docs with their own merge data, eg above Doc1, 2 & 3.
B. Ive read up on bookmarks and inserted some bookmarks into Doc1.
Q. How do I actually execute the procedure below to drop Docs 2 & 3 into Doc1. In my Access code, in word where? Where do I do it and how?

"Doc1:     goto Bookmark InsertFile Doc2
     goto Bookmark InsertFile Doc3"

Im finally starting to get ahead with this, thanks again.

Evolve2k

The code would need to be run from the MailMerge document. but I've not tried this as yet.

Evolve2K

Since you suggested looking at your question in response to my query, you may now want to look back at my question about updating a Word table from Access at
https://www.experts-exchange.com/questions/20930523/Access-Word-integration.html
now that I have resolved my query.

Hope it helps!

Robert

Sorry, that Ive been so slack with completing this question. As things go, work priorities change and Ive been moved on to more pressing projects.

Im happy to award the 75 points to mdmackillop, and I wanted to also award an extra 25 points to Dreamboat for the invaluable help and links when I 1st posted the question.

Note for those trying to solve a similar problem.
------------------------------------------------------
The main understanding I got was this.
Create one big query with everything you want to use in your word doc as the data source which has repeating data for entries that you want as listings in your merge Doc.
Ie in my case;
Client          Address               Service                      Date                  Hours
Joe Bloggs  1 Happyville Road   Tax Preparation         1/2/03                60
Joe Bloggs  1 Happyville Road   Consulting                 5/5/03                45
Joe Bloggs  1 Happyville Road   Business Coaching     11/11/03             12

Merge the 1st record to your merge doc like ususal and then each time you want to jump to the next record use the 'NextRecord' tag in your merge file.

The idea then is if you want to do this with a toally new list you merge in another query at that point in the Doc.

Evolve2k

Thanks so much for all your help guys.
It really is an area of its own this access>word integration.

Hopefully your happy with my comments above.

Im probably going to have to reopen these issues later down the track, are you guys happy to help then also If i drop u a line?

Thanks again.

Evolve2k