merge multiple fields from multiple tables in one word document/ V

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

dear All,

I posed a question saturday evening on mailmerging....thanks for all the
answer and advise, I am way further but still not completely there.

I am presently trying to do it with VBA but do not manage to use fields from
different tables in the word template. see enclosed the VBA part

(see below for problem definition)

--------Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

Dim objWord As Word.Document
Set objWord = GetObject("C:\Doc1.doc", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the CV database.
objWord.MailMerge.OpenDataSource _
Name:="S:\Access\actuele versie van cvs.mdb", _
LinkToSource:=True, _
Connection:="TABLE personalia", _
SQLStatement:="SELECT personalia.*, personalia.[reference number]
FROM personalia WHERE personalia.[reference number]=16"


'here i try to call for the second table
'bjWord.MailMerge.OpenDataSource _
'Name:="S:\Access\actuele versie van cvs.mdb", _
'LinkToSource:=True, _
'Connection:="TABLE tbl_language", _
'SQLStatement:="Select * from [tbl_language]"

' Execute the mail merge.
objWord.MailMerge.Execute

Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

End Sub
--------------------------------------------------

When I use fields in the doc1.doc that come from the second, third etc.
table there are problems when the first table is called for. At that moment,
access-word does not recognize the fields from the other tables yet....
I do not know how to work this out

Bye the bye, queries I can not use due to the nature of the tables (I have
enclosed the original first question).

I have been looking at a problem the entire week in access, I have
still not been able to find a solution. Hope that you could maybe tell
where to look
Concerns the link between Access and Word. I can not transfer a report
to word without losing out on the lay-out (RTF format). I understand
that there is no way out

ok, mail merge I thought. But here I have the problem that I need to
merge multiple tables and that I can just include one in the mailmerge
within word. A query would seem likely but there I have a problem. I
have one master table containing personal information on staff and 8
subtables around the master table with specifying language, workexp,
education for each person. The number of entries in the subtables
varies per person.
In case I use a query to combine all these tables I get # x # x # x #
x# (9 times) number of records an extreme number of records for each
person.
An example: person A
knows 8 languages
has 3 educational references
10 publications
took 8 courses in his career

This would already result in 1 X 8 X 10 X 8 = 640 records
Do I miss a certain function with queries or how can I work this out? I
can not imagine that it is not possible

Thanks and wishing you a good day
Stefan van den Hark
The Netherlands
 
In case I use a query to combine all these tables I get # x # x # x #
x# (9 times) number of records an extreme number of records for each
person.
Click to expand...

i think you're probably not building the query correctly. go ahead and
create this query again, and the post the complete SQL so we can look at it.
i'm betting somebody will be able to fix it, so that you can use a query for
your Word doc instead of multiple table sources.

hth


svdh said:
dear All,

I posed a question saturday evening on mailmerging....thanks for all the
answer and advise, I am way further but still not completely there.

I am presently trying to do it with VBA but do not manage to use fields from
different tables in the word template. see enclosed the VBA part

(see below for problem definition)

--------Private Sub Command90_Click()
On Error GoTo Err_Command90_Click

Dim objWord As Word.Document
Set objWord = GetObject("C:\Doc1.doc", "Word.Document")

' Make Word visible.
objWord.Application.Visible = True

' Set the mail merge data source as the CV database.
objWord.MailMerge.OpenDataSource _
Name:="S:\Access\actuele versie van cvs.mdb", _
LinkToSource:=True, _
Connection:="TABLE personalia", _
SQLStatement:="SELECT personalia.*, personalia.[reference number]
FROM personalia WHERE personalia.[reference number]=16"


'here i try to call for the second table
'bjWord.MailMerge.OpenDataSource _
'Name:="S:\Access\actuele versie van cvs.mdb", _
'LinkToSource:=True, _
'Connection:="TABLE tbl_language", _
'SQLStatement:="Select * from [tbl_language]"

' Execute the mail merge.
objWord.MailMerge.Execute

Exit_Command90_Click:
Exit Sub

Err_Command90_Click:
MsgBox Err.Description
Resume Exit_Command90_Click

End Sub
--------------------------------------------------

When I use fields in the doc1.doc that come from the second, third etc.
table there are problems when the first table is called for. At that moment,
access-word does not recognize the fields from the other tables yet....
I do not know how to work this out

Bye the bye, queries I can not use due to the nature of the tables (I have
enclosed the original first question).

I have been looking at a problem the entire week in access, I have
still not been able to find a solution. Hope that you could maybe tell
where to look
Concerns the link between Access and Word. I can not transfer a report
to word without losing out on the lay-out (RTF format). I understand
that there is no way out

ok, mail merge I thought. But here I have the problem that I need to
merge multiple tables and that I can just include one in the mailmerge
within word. A query would seem likely but there I have a problem. I
have one master table containing personal information on staff and 8
subtables around the master table with specifying language, workexp,
education for each person. The number of entries in the subtables
varies per person.
In case I use a query to combine all these tables I get # x # x # x #
x# (9 times) number of records an extreme number of records for each
person.
An example: person A
knows 8 languages
has 3 educational references
10 publications
took 8 courses in his career

This would already result in 1 X 8 X 10 X 8 = 640 records
Do I miss a certain function with queries or how can I work this out? I
can not imagine that it is not possible

Thanks and wishing you a good day
Stefan van den Hark
The Netherlands
Click to expand...
 
Back
Top