Super easy mail merge

[Guest]

Hi

I have used Albert Kallal super easy mail merge, and it worked really well
for one of my forms.

I have another form, which show report info. This form has a subform of the
associated actions. These two forms, are based on 2 related tables.

I wanted to mail merge both the form and subform into one word document.
But after a day of trying different things, I have not been able to work our
how to do this.

Does any one know of a way to merge both into one mail merge document.

 

[Albert D. Kallal]

Are you needing to display/show "many" records of the sub-form?

If you ONLY need to show/merge a PARTICULAR RECORD from the sub-form, then
you can use my merge to work.

however, if you need display "many" records from the sub-form in the ONE
document, then the built in word merge does not support this. And, since my
code actually does NOT do the merge but uses the built in word merge then my
code example IS OF NO USE in this case.

So,

my word merge = good for one record
my word merge = good for one main record, AND ALSO include ONE OF those
records from the child form

however:

my wordmerge -- no good for showing "many" records from the sub-form,
and ONE record from the main.

So, either use a report, or considering writing your own code.

There are samples and ideas on how to do this at:

http://homepage.swissonline.ch/cindymeister/MergFram.htm

look on the left side for special merges. The one you want is

Multiple items per condition

 

[Guest]

One way would be to return a computed column in the parent form's underlying
query by putting a function along the following lines in a standard module:

Function ListActions(lngID as Long) As String

Dim rst As ADODB.Recordset
Dim strSQL As String
Dim strActions As String

strSQL = "SELECT Action FROM qryActions " & _
"WHERE MyID = " & lngID

Set rst = New ADODB.Recordset

With rst
.ActiveConnection = CurrentProject.Connection
.Open _
Source:=strSQL, _
CursorType:=adOpenKeyset, _
Options:=adCmdText

Do While Not .EOF
strActions = strActions & vbNewLine & _
.Fields("Action")
.MoveNext
Loop
.Close
' remove leading carriage return/line feed
strActions = Mid$(strActions, 3)
End With

Set rst = Nothing

ListActions = strActions

End Function

where Actions is the name of a query (it could be a table but a query is
more likely in this context I'd guess) underlying the subform, Action is a
text field returned by the query and MyID is the name of each of the fields
on which the subform and parent form are linked.

If there is more than one field shown in the subform you'd have a similar
function and extra column in the query for each. With the function(s) in a
module you can then add an extra column(s) to the parent form's underlying
query by putting something like the following in the 'field' row of a blank
column in query design view:

ActionList:ListActions([MyID])

A merge field in the Word document can then draw upon the ActionList column
in the query just like any other column. You could of course have a deep
text box on the parent form with the ActionList column as its ControlSource,
rather than having a subform, provided that you don't need to update the list
of actions for each parent form record and you.

With the function(s) in

 

[Guest]

The end of my last reply got a little mangled, but noting of any importance
got lost. The method I suggested would of course handle both the situations
Albert referred to for which his is not suitable.

Ken Sheridan
Stafford, England

 

[Guest]

Thanks everyone, I will give try these options out and let you know how it
goes.

regards

Joanne