Insert 'subform' in Word mail-merge?

[brynne]

I run a recruitment service for which, years ago, I set up an Access database to record details of job applications and run various mail merge functions including interview invitations, reference requests and panel letters.

However, my letters contain only the details that can be recorded in single fields. e.g. title, first name, last name, address1, address2, etc. Currently, where lists are involved, e.g. names of the interview panel, lists of applicants and interview times, we just have to type them in for each batch of letters.

These details are displayed on the database as a subform. My question is, is there any way to insert a subform straight into a Word document as part of the mail-merge - or does one have to pre-define the number of possible list items, e.g. applicant 1, applicant 2, or panel member1, panel member2, etc?

Any help would be appreciated.

Brigid

[MMcCarthy]

Hi Brigid

This is not my area of expertise but I would guess that the only way of doing this would be with complicated Word Automation VBA code using recordsets.

Mary

[brynne]

Hi Mary,

Thanks. I subsequently wondered whether I might get round the problem by creating a field that contains an array, e.g. AppID, AppName, IntTime, new line. I am sure I did something like this for a programming exercise (in Pascal!!) years ago, but I am not familiar enough with VBA to know if it is possible.

Does this sound feasible?

B

[Denburt]

I started to give this one a go earlier however like Mary stated this is not an area I have dealt with very much. I think you hit the nail on the head or at least real close. My first thought was to create a query based on the main table and the child table however this would probably send out many emails to each person. However if you created the query and converted it to a crosstab query it just might work. Word does recognize queries when you want to do a mailmerge.

[Denburt]

As an alternative you could send out the info straight from access. Create a report with the info you want. If you add something like the following to a button click event you could be good to go.

DoCmd.SendObject acSendTable, "Employees", acFormatXLS, _
"Nancy Davolio; Andrew Fuller", "Joan Weber", , _
"Current Spreadsheet of Employees", , False

Let me know if you need more details or if this does or doesn't help you out.
BTW if you paste this in the VBA window then highlight the word sendobject press F1 and get the full syntax for this method.

[MMcCarthy]

Regarding Deburt's idea of a Crosstab query ...

Something like ...

TRANSFORM ApplicantName
SELECT JobRef
FROM TableName
GROUP BY JobRef
PIVOT ApplicantName;

[brynne]

No reason why I need to print letters in Word at all really. Seems the simplest solution will be to recreate the letter as a report in Access.

Thanks to all.

B

[Denburt]

Your quite welcome, also there are other methods in MS Access that are even more robust. Let us know if we can be of any more assistance.