Substitute a text string and loop

[Archie]

The story so far:-

I am creating a mail merge source. Each record contains 20 cells which are
the results of 4 Access queries which each return 5 columns of data. Because
each query result may be up to 500 rows, I bring each query result into a
separate Word doc (using Insert/Field/Database), from which I then paste
special (as unformatted text) each column into the applicable cell in the
merge source. I then delete the table imported from Access and do the same
again for the next query.

The goal is a letter which includes 4 tables listing transaction data for
the client.

The Access queries will be named in the style "ABC DEF XXXX(XX)", "ABC GHI
XXXX(XX)", "JKL DEF XXXX(XX)" and "JKL GHI XXXX(XX)", where XXXX(XX) is 4 or
6 alpha characters which identify the client. There are currently 65 and
codes will be added and deleted over time i.e. 260 queries to start with.

I have this working fairly crudely by recording macros, but I'm sure a VBA
routine would be much more economical. In particular, I would like a routine
which includes a list of the client codes, and uses each of them in turn to
create the name of the 4 queries to get. Secondly, to know where in the mail
merge source to insert the data (alongside the corresponding client account
no., name and email address, I've thought of using bookmarks but it will be
pretty tedious), and finally to repeat the full sequence (get data, select
column, copy column, paste special to another doc, return to the other doc,
select next column and so on 4 more times per query, delete table, get data
etc 3 more times per client).

I suspect the process is far from ideal in any case, but do want to stick
with Word for the time being for various reasons. Thanks for your indulgence
and help.

 

[Chuck]

You're talking about a lot of coding to mimic built-in functionality. You
say you want to stay in Word -- it can't be for ease of query manipulation,
so it must be for formatting reasons.

Why not use Access to generate the mail merge as a report, export/save the
report to Word and then do your cosmetic work there?

 

[Archie]

Thanks for the sanity check, Chuck. Sometimes you can't see the wood for the
trees! You're right, sticking with Word is mainly about formatting, as well
as consistency with other elements and user comfort. So what I now propose
is:-

Create report from each query.
Create macro (using OutputTo action) to export the reports as rtf,
specifying save filenames.
In my mail merge source table in Word, I have inserted the field "Insert
text" as a link to the rtf file.
Now, with some additional cosmetics in my mail merge main doc, I have a nice
result.

This is certainly much more effective; the only issue is that, when I run
the macro, I get asked if I want to replace the existing file. The answer is
"Yes" every time. I realise this is probably controlled by the Access Macro,
which may not be your forte.

Can you help with this minor aspect, and is there anything else I'm being
dumb about?

Many thanks

 

[Chuck]

To avoid the overwrite file prompt when saving the new report, you'll
probably need to run the KILL command to delete the existing file before
saving the new one.

As for simplifying the merge process. Assuming that your merge information
is in your database, you could set up your merge letter as a report, with the
table query results as an embedded sub-report, with the sub-report's results
filtered based on the each record in the main report. That way you could set
up your query-report-export as a single command (button) and open the
resulting RTF in Word for final cosmetic touches, without having to run a
separate merge in Word. They key thing is to design your query(ies) for the
sub-report to include fields that match identifying (key) fields in the main
report, so the sub-report receives it's filter criteria from the main report.

HTH
Chuck

 

[Archie]

Thanks for the tips, Chuck. I'll give your suggestions about the sub-reports
a try.

As a point of interest, when I use a macro with OutputTo, I get the error
message about replacing the existing file. However, I now find that when I
run this from a module as DoCmd.OutputTo, there is no message and it does
overwrite, so all's good.

Thanks again for your input; this is a great community!

 

[Chuck]

You're welcome -- I've had a lot of great help here myself.