473,695 Members | 1,960 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

merge multiple tables in one word-document: how to do it ?

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

Nov 13 '05 #1
3 5195
I am very new to Access and VBA but this is how I opened 7 tables using
a command button and control values on a single form and opened a
letter in Word. It opens it in DOC format, not RTF.

Some of this code was copied from other sources on this newsgroup.

I'm sure some of the experts on this newsgroup will have a much easier
way to do the same.

I'm glad I can give a little back to the newsgroup that has helped me
so much.

*************** *************** *************** **************
Private Sub cmdConsultPrint _Click()

Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeF ields

Dim StrToAdd As String

If IsNull(Me![cboTitleofCourt esy]) Or IsNull(Me![tboFirstName]) Or
IsNull(Me![tboLastName]) Then
MsgBox "You MUST enter Patient Name and Courtesy Title to
continue!", vbOKOnly
Exit Sub
End If

' Make Referring Doctors table available and select proper record.
If IsNull(Me![cboReferringDoc tor]) = False Then

Dim cnn As ADODB.Connectio n
Dim rst As New ADODB.Recordset
Set cnn = CurrentProject. Connection
rst.ActiveConne ction = cnn

rst.Open "SELECT * FROM [tblReferringDoc tors]" & _
" WHERE tblReferringDoc tors.ReferringD octorID = " &
Me![cboReferringDoc tor]
Else
MsgBox "You MUST enter Referring Doctor to continue!", vbOKOnly
Exit Sub
End If
rst.MoveFirst

' Make Surgeons table available and select proper record.
If IsNull(Me![cboSurgeon]) = False Then

Dim cnn1 As ADODB.Connectio n
Dim rst1 As New ADODB.Recordset
Set cnn1 = CurrentProject. Connection
rst1.ActiveConn ection = cnn1

rst1.Open "SELECT * FROM [tblSurgeons]" & _
" WHERE tblSurgeons.Sur geonID = " & Me![cboSurgeon]
Else
MsgBox "You MUST enter Surgeon to continue!", vbOKOnly
Exit Sub
End If
rst1.MoveFirst

' Make Diagnosis table available and select proper record.
If IsNull(Me![cboDiagnosis]) = False Then

Dim cnn2 As ADODB.Connectio n
Dim rst2 As New ADODB.Recordset
Set cnn2 = CurrentProject. Connection
rst2.ActiveConn ection = cnn2

rst2.Open "SELECT * FROM [tblDiagnosis]" & _
" WHERE tblDiagnosis.Di agnosisID = " & Me![cboDiagnosis]
Else
MsgBox "You MUST enter a Diagnosis to continue!", vbOKOnly
Exit Sub
rst2.MoveFirst
End If
' Make TypeofFlap table available and select proper record.
If IsNull(Me![cboTypeofFlap]) = False Then

Dim cnn3 As ADODB.Connectio n
Dim rst3 As New ADODB.Recordset
Set cnn3 = CurrentProject. Connection
rst3.ActiveConn ection = cnn3

rst3.Open "SELECT * FROM [tblTypeofFlap]" & _
" WHERE tblTypeofFlap.T ypeofFlapID = " & Me![cboTypeofFlap]
Else
MsgBox "You MUST enter a Type of Flap to continue!", vbOKOnly
Exit Sub
rst3.MoveFirst
End If

' Make Region table available and select proper record.
If IsNull(Me![cboRegion]) = False Then

Dim cnn4 As ADODB.Connectio n
Dim rst4 As New ADODB.Recordset
Set cnn4 = CurrentProject. Connection
rst4.ActiveConn ection = cnn4

rst4.Open "SELECT * FROM [tblRegion]" & _
" WHERE tblRegion.Regio nID = " & Me![cboRegion]
Else
MsgBox "You MUST enter a Site of Tumor to continue!", vbOKOnly
Exit Sub
rst4.MoveFirst
End If

' Make Aspect table available and select proper record.
If IsNull(Me![cboAspect]) = False Then

Dim cnn5 As ADODB.Connectio n
Dim rst5 As New ADODB.Recordset
Set cnn5 = CurrentProject. Connection
rst5.ActiveConn ection = cnn5

rst5.Open "SELECT * FROM [tblAspect]" & _
" WHERE tblAspect.Aspec tID = " & Me![cboAspect]
Else
MsgBox "You MUST enter a Site Aspect to continue!", vbOKOnly
Exit Sub
rst5.MoveFirst
End If

' Make SurgeryType table available and select proper record.
If IsNull(Me![cboSurgeryType]) = False Then

Dim cnn6 As ADODB.Connectio n
Dim rst6 As New ADODB.Recordset
Set cnn6 = CurrentProject. Connection
rst6.ActiveConn ection = cnn6

rst6.Open "SELECT * FROM [tblSurgeryType]" & _
" WHERE tblSurgeryType. SurgeryTypeID = " & Me![cboSurgeryType]
Else
MsgBox "You MUST enter a Surgery Type to continue!", vbOKOnly
Exit Sub
rst6.MoveFirst
End If

MsgBox "Be sure to REVIEW CONSULTATION LETTER before sending!",
vbOKOnly

' Create an instance of Word, and make it visible.
Set wrdApp = CreateObject("W ord.Application ")
wrdApp.Visible = True

' Add a new document and uniquely name the document.
Set wrdDoc = wrdApp.Document s.Add
wrdDoc.Select

Set wrdSelection = wrdApp.Selectio n
Set wrdMailMerge = wrdDoc.MailMerg e

' Set left margin.
wrdSelection.Pa ragraphFormat.L eftIndent = 85

wrdApp.Selectio n.TypeParagraph
' Left align the line, and insert a date field
' with the current date.

wrdSelection.Pa ragraphFormat.A lignment = wdAlignParagrap hLeft
wrdSelection.In sertDateTime _
DateTimeFormat: ="MMMM dd, yyyy", InsertAsField:= False

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

' Print name and address.
StrToAdd = rst.Fields("Fir stName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Las tName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Add ress")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Cit y")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText ", "
StrToAdd = rst.Fields("Sta te")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Zip ")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

' Align the rest of the document.
wrdSelection.Pa ragraphFormat.A lignment = _
wdAlignParagrap hJustify

wrdSelection.Ty peText "RE: " & Me![tboFirstName] & " " &
Me![tboLastName]

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

wrdSelection.Ty peText "Dear " & rst.Fields("Fir stName") & ","

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Thank you for allowing me to assist you in the care
of your patient, " & _
Me![cboTitleofCourt esy] & " " & _
Me![tboFirstName] & " " & _
Me![tboLastName] & _
". Today I saw " & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "him", "her"), "Dr. " &
Me![tboLastName]) & _
" in consultation for a " & _
rst2.Fields("Di agnosis") & _
", " & _
IIf(IsNull(Me![cboAspect]), "", rst5.Fields("As pect") & " ") & _
rst4.Fields("Re gion") & ", with " & _
rst6.Fields("Su rgeryType") & _
" scheduled to follow. The risks and benefits were explained to
" & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "him", "her"), "Dr. " &
Me![tboLastName]) & _
" and all questions were answered. " & _
Me![cboTitleofCourt esy] & " " & Me![tboLastName] & _
" elected to proceed with the surgery as scheduled under local
anesthesia." & _
" The details of " & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "his", "her"), "Dr. " &
Me![tboLastName] & "'s") & _
" surgery are described below."

wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Diagnosis: " & rst2.Fields("Di agnosis")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Stage: " & Me![tboStage]
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Wound Size: " & Me![tboWoundSize]
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Closure: " & rst3.Fields("Ty peofFlap")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Thank you again for your kind referral." & _
" If I can be of any further assistance to you" & _
" in the care of your patients by providing " & _
rst6.Fields("Su rgeryType") & ", please do not hesitate to call."
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = "Sincerely, "
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = rst1.Fields("Fi rstName") & " " & rst1.Fields("La stName") &
", " & rst1.Fields("Cr edentials")

wrdSelection.Ty peText StrToAdd

' Insert pictures
' Call fLoadPictures(F orms!frmPatient s!tboPictureFol der)

' Go to the end of the document.
wrdApp.Selectio n.GoTo wdGoToLine, wdGoToLast

' wrdDoc.SaveAs "C:\SurgeonData base\Documents\ " &
rst.Fields("Las tName") & rst.Fields("Fir stName") & _
Me![tboLastName] & "Surgery" & Format(Date, "mmmdyyyy")

' Close the original document.
' wrdDoc.Saved = True
' wrdDoc.Close False

rst.Close
Set rst = Nothing
Set cnn = Nothing

rst1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

Set wrdSelection = Nothing
Set wrdDoc = Nothing
Set wrdApp = Nothing

End Sub

Nov 13 '05 #2
sv***@yahoo.com wrote:
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. An example: person A
knows 8 languages
has 3 educational references
10 publications
took 8 courses in his career
Maybe a crosstab query (kruistabel) can help you out. Have the person as
row header, the information as column header, and as a value simply an "X".
Do I miss a certain function with queries or how can I work this out? I
can not imagine that it is not possible


An alternative is to search on the net for 'query concatenate' I think
that will point out several solutions where you get, say, the eight
languages (who is this? like to meet the person :-) ) will be in one
field. But that is more complicated so more prone to errors.

Mazzel en als je er in de groep niet uitkomt vanwege taalproblemen kun
je me mailen
--
Bas Cost Budde, Holland
http://www.heuveltop.nl/BasCB/msac_index.html
I prefer human mail above automated so in my address
replace the queue with a tea
Nov 13 '05 #3
Just a few miscellaneous notes if you're interested:

1. Put all your Dim statements at the top, where you can find them easier,
and where you can be sure they are in scope wher you need them.

2. Re-use variables, particularly ADO variables, since ADO objects seem to
take longer to create and initialize. You have used several
ADODB.Connectio n objects, however, they can all be included in one
re-usable:

Dim cnn As ADODB.Connectio n
Set cnn = CurrentProject. Connection

3. You don't need the brackets around control names. Your code will be
easier to read if you replace

Me![cboTitleofCourt esy]

with

Me.cboTitleofCo urtesy
4. Your ADOBDB.Recordse t.Open method will operate faster if you specify all
the available parameters. In fact, using only the default Recordset will
cause problems in many situations.

To open a editable Recordset using an SQL statement:

rst.Open "SELECT * FROM tblReferringDoc tors" & _
" WHERE ReferringDoctor ID = " &
Me.cboReferring Doctor, cnn, adOpenKeyset, adLockOptimisti c,
adCmdText

To open a table for fast, direct table access, but with a forward only
cursor, in read only mode:

rst.Open "tblRegion" , cnxn, adOpenForwardOn ly, adLockReadOnly,
adCmdTableDirec t

5. Use the With/End With construction:

Instead of:
StrToAdd = rst.Fields("Fir stName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Las tName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Add ress")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Cit y")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText ", "
StrToAdd = rst.Fields("Sta te")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Zip ")
wrdSelection.Ty peText StrToAdd
Use:

With wrdSelection
StrToAdd = rst.Fields("Fir stName")
.TypeText StrToAdd
.TypeText " "
StrToAdd = rst.Fields("Las tName")
.TypeText StrToAdd
.TypeParagraph
StrToAdd = rst.Fields("Add ress")
.TypeText StrToAdd
.TypeParagraph
StrToAdd = rst.Fields("Cit y")
.TypeText StrToAdd
.TypeText ", "
StrToAdd = rst.Fields("Sta te")
.TypeText StrToAdd
.TypeText " "
StrToAdd = rst.Fields("Zip ")
.TypeText StrToAdd
End With

This may run marginally faster and is easier to read
6. Avoid assigning variables when you don't need to. MS examples often
create String variables unnecessarily. Why not just write the above from #5
as:

With wrdSelection
.TypeText rst.Fields("Fir stName")
.TypeText " "
.TypeText rst.Fields("Las tName")
.TypeParagraph
.TypeText rst.Fields("Add ress")
.TypeParagraph
.TypeText rst.Fields("Cit y")
.TypeText ", "
.TypeText rst.Fields("Sta te")
.TypeText " "
.TypeText rst.Fields("Zip ")
End With
Darryl Kerkeslager
"bb*******@NoSp amgmail.com" <bb*******@gmai l.com> wrote: *************** *************** *************** **************
Private Sub cmdConsultPrint _Click()

Dim wrdSelection As Word.Selection
Dim wrdMailMerge As Word.MailMerge
Dim wrdMergeFields As Word.MailMergeF ields

Dim StrToAdd As String

If IsNull(Me![cboTitleofCourt esy]) Or IsNull(Me![tboFirstName]) Or
IsNull(Me![tboLastName]) Then
MsgBox "You MUST enter Patient Name and Courtesy Title to
continue!", vbOKOnly
Exit Sub
End If

' Make Referring Doctors table available and select proper record.
If IsNull(Me![cboReferringDoc tor]) = False Then

Dim cnn As ADODB.Connectio n
Dim rst As New ADODB.Recordset
Set cnn = CurrentProject. Connection
rst.ActiveConne ction = cnn

rst.Open "SELECT * FROM [tblReferringDoc tors]" & _
" WHERE tblReferringDoc tors.ReferringD octorID = " &
Me![cboReferringDoc tor]
Else
MsgBox "You MUST enter Referring Doctor to continue!", vbOKOnly
Exit Sub
End If
rst.MoveFirst

' Make Surgeons table available and select proper record.
If IsNull(Me![cboSurgeon]) = False Then

Dim cnn1 As ADODB.Connectio n
Dim rst1 As New ADODB.Recordset
Set cnn1 = CurrentProject. Connection
rst1.ActiveConn ection = cnn1

rst1.Open "SELECT * FROM [tblSurgeons]" & _
" WHERE tblSurgeons.Sur geonID = " & Me![cboSurgeon]
Else
MsgBox "You MUST enter Surgeon to continue!", vbOKOnly
Exit Sub
End If
rst1.MoveFirst

' Make Diagnosis table available and select proper record.
If IsNull(Me![cboDiagnosis]) = False Then

Dim cnn2 As ADODB.Connectio n
Dim rst2 As New ADODB.Recordset
Set cnn2 = CurrentProject. Connection
rst2.ActiveConn ection = cnn2

rst2.Open "SELECT * FROM [tblDiagnosis]" & _
" WHERE tblDiagnosis.Di agnosisID = " & Me![cboDiagnosis]
Else
MsgBox "You MUST enter a Diagnosis to continue!", vbOKOnly
Exit Sub
rst2.MoveFirst
End If
' Make TypeofFlap table available and select proper record.
If IsNull(Me![cboTypeofFlap]) = False Then

Dim cnn3 As ADODB.Connectio n
Dim rst3 As New ADODB.Recordset
Set cnn3 = CurrentProject. Connection
rst3.ActiveConn ection = cnn3

rst3.Open "SELECT * FROM [tblTypeofFlap]" & _
" WHERE tblTypeofFlap.T ypeofFlapID = " & Me![cboTypeofFlap]
Else
MsgBox "You MUST enter a Type of Flap to continue!", vbOKOnly
Exit Sub
rst3.MoveFirst
End If

' Make Region table available and select proper record.
If IsNull(Me![cboRegion]) = False Then

Dim cnn4 As ADODB.Connectio n
Dim rst4 As New ADODB.Recordset
Set cnn4 = CurrentProject. Connection
rst4.ActiveConn ection = cnn4

rst4.Open "SELECT * FROM [tblRegion]" & _
" WHERE tblRegion.Regio nID = " & Me![cboRegion]
Else
MsgBox "You MUST enter a Site of Tumor to continue!", vbOKOnly
Exit Sub
rst4.MoveFirst
End If

' Make Aspect table available and select proper record.
If IsNull(Me![cboAspect]) = False Then

Dim cnn5 As ADODB.Connectio n
Dim rst5 As New ADODB.Recordset
Set cnn5 = CurrentProject. Connection
rst5.ActiveConn ection = cnn5

rst5.Open "SELECT * FROM [tblAspect]" & _
" WHERE tblAspect.Aspec tID = " & Me![cboAspect]
Else
MsgBox "You MUST enter a Site Aspect to continue!", vbOKOnly
Exit Sub
rst5.MoveFirst
End If

' Make SurgeryType table available and select proper record.
If IsNull(Me![cboSurgeryType]) = False Then

Dim cnn6 As ADODB.Connectio n
Dim rst6 As New ADODB.Recordset
Set cnn6 = CurrentProject. Connection
rst6.ActiveConn ection = cnn6

rst6.Open "SELECT * FROM [tblSurgeryType]" & _
" WHERE tblSurgeryType. SurgeryTypeID = " & Me![cboSurgeryType]
Else
MsgBox "You MUST enter a Surgery Type to continue!", vbOKOnly
Exit Sub
rst6.MoveFirst
End If

MsgBox "Be sure to REVIEW CONSULTATION LETTER before sending!",
vbOKOnly

' Create an instance of Word, and make it visible.
Set wrdApp = CreateObject("W ord.Application ")
wrdApp.Visible = True

' Add a new document and uniquely name the document.
Set wrdDoc = wrdApp.Document s.Add
wrdDoc.Select

Set wrdSelection = wrdApp.Selectio n
Set wrdMailMerge = wrdDoc.MailMerg e

' Set left margin.
wrdSelection.Pa ragraphFormat.L eftIndent = 85

wrdApp.Selectio n.TypeParagraph
' Left align the line, and insert a date field
' with the current date.

wrdSelection.Pa ragraphFormat.A lignment = wdAlignParagrap hLeft
wrdSelection.In sertDateTime _
DateTimeFormat: ="MMMM dd, yyyy", InsertAsField:= False

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

' Print name and address.
StrToAdd = rst.Fields("Fir stName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Las tName")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Add ress")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peParagraph
StrToAdd = rst.Fields("Cit y")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText ", "
StrToAdd = rst.Fields("Sta te")
wrdSelection.Ty peText StrToAdd
wrdSelection.Ty peText " "
StrToAdd = rst.Fields("Zip ")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

' Align the rest of the document.
wrdSelection.Pa ragraphFormat.A lignment = _
wdAlignParagrap hJustify

wrdSelection.Ty peText "RE: " & Me![tboFirstName] & " " &
Me![tboLastName]

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

wrdSelection.Ty peText "Dear " & rst.Fields("Fir stName") & ","

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Thank you for allowing me to assist you in the care
of your patient, " & _
Me![cboTitleofCourt esy] & " " & _
Me![tboFirstName] & " " & _
Me![tboLastName] & _
". Today I saw " & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "him", "her"), "Dr. " &
Me![tboLastName]) & _
" in consultation for a " & _
rst2.Fields("Di agnosis") & _
", " & _
IIf(IsNull(Me![cboAspect]), "", rst5.Fields("As pect") & " ") & _
rst4.Fields("Re gion") & ", with " & _
rst6.Fields("Su rgeryType") & _
" scheduled to follow. The risks and benefits were explained to
" & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "him", "her"), "Dr. " &
Me![tboLastName]) & _
" and all questions were answered. " & _
Me![cboTitleofCourt esy] & " " & Me![tboLastName] & _
" elected to proceed with the surgery as scheduled under local
anesthesia." & _
" The details of " & _
IIf(Me![cboTitleofCourt esy] <> "Dr.", _
IIf(Me![cboTitleofCourt esy] = "Mr.", "his", "her"), "Dr. " &
Me![tboLastName] & "'s") & _
" surgery are described below."

wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Diagnosis: " & rst2.Fields("Di agnosis")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Stage: " & Me![tboStage]
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Wound Size: " & Me![tboWoundSize]
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Closure: " & rst3.Fields("Ty peofFlap")
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = " Thank you again for your kind referral." & _
" If I can be of any further assistance to you" & _
" in the care of your patients by providing " & _
rst6.Fields("Su rgeryType") & ", please do not hesitate to call."
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = "Sincerely, "
wrdSelection.Ty peText StrToAdd

wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph
wrdApp.Selectio n.TypeParagraph

StrToAdd = rst1.Fields("Fi rstName") & " " & rst1.Fields("La stName") &
", " & rst1.Fields("Cr edentials")

wrdSelection.Ty peText StrToAdd

' Insert pictures
' Call fLoadPictures(F orms!frmPatient s!tboPictureFol der)

' Go to the end of the document.
wrdApp.Selectio n.GoTo wdGoToLine, wdGoToLast

' wrdDoc.SaveAs "C:\SurgeonData base\Documents\ " &
rst.Fields("Las tName") & rst.Fields("Fir stName") & _
Me![tboLastName] & "Surgery" & Format(Date, "mmmdyyyy")

' Close the original document.
' wrdDoc.Saved = True
' wrdDoc.Close False

rst.Close
Set rst = Nothing
Set cnn = Nothing

rst1.Close
Set rst1 = Nothing
Set cnn1 = Nothing

Set wrdSelection = Nothing
Set wrdDoc = Nothing
Set wrdApp = Nothing

End Sub

Nov 13 '05 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
3166
by: Morris | last post by:
Does anyone know whether it is possible to effect a mail merge to MSWord using VBScript in an asp file? I know it is possible to produce a doc file and force a download using Response.ContentType = "application/msword" Response.AddHeader content-disposition","attachment;filename=myfile.doc" but can I interate through a bunch of records and produce, say, a separate invoice for each one by merging? I know how to iterate through the...
4
8875
by: Tom Dauria | last post by:
I have an application that will be distributed remotely. In the Access application I am opening Word documents and mail merging. The Word documents are linked to a tmpLetter table. In my code I write the records to be merged into that table. The Word documents are using a system dsn (ODBC) to connect to the data source. In my code I simply open the Word documents after writing to the table do a mail merge and print. It runs pretty...
3
2569
by: Traci | last post by:
I need to do a mail merge letter from my database. The letter will be addressed to small companies and in the body of the letter I need to list employees of the company. There will be from 1 to 15 employees. My database is all set up to provide the names and addresses of the companies and the list of employees of each company. I need help on how to do the employee merge field when there will be multiple employees to be listed in this field....
1
3812
by: Tom Keane | last post by:
Hi, I need some helpings again! I have a query from one of my tables that gets a total amount of money and gets a percentage from it ie, the field I create in the query is invGSTAmount from a field in my table called invAmount, so I but in the design view of my query: invGSTAmount: *1/11
8
4824
by: dd_bdlm | last post by:
Hi I wonder if anyone can help? I have looked through prevous posts and cant find any answers that fit what I need to do. For reference I am using Access 97 and Word 97. I want to be able to have a client record open in a form and click a button and for that particular record to merge with a preformatted word doc. I have created a query that gives me all the fields I need (as they are over multiple tables) and I can successfully combine...
1
2454
by: achintya.jha | last post by:
Hi, I am currently working hard to print Word labels from ASP.NET I have multiple records which I need to print on to a word label print functionality. I know that Word Label printing prints the same label multiple times on the printer. I have to print multiple labels on to the same page and then the printer. So, I think I have to use Mail-Merge and then Word Label Print. I also need the standard label printing dialog to open before...
0
1536
by: pnr | last post by:
I transferred my database to a new computer. I can open it in 2003 Access, but am keeping it in the 2000 version until I get it working correctly. I have dozens of Word documents where I use various Access queries as my datasource. Now when I try to mail merge in Word (for some, not all, of my documents.) Word mail merge message may say it can't find the data source. So it gives me a data selection box and I choose my transferred...
3
11350
by: cdelarte | last post by:
I would like to be able to mail merge records from multiple mysql tables using a simple template, preferably via a command line script. MSWord mail merge via ODBC will not work for me as it only supports a single table. Are there any utilities or simple scripts avaialble that would accomplish this?
6
4643
by: crealesmith | last post by:
Firstly, I have no problem with mail merging to Word, VB code for that works perfectly. On one mail merge I need to merge 15 fields of data that are from 3 seperate records. The 3 records are all in the same table. If I use a control source that is selecting the 3 records, all of the data is shown but spread over 3 pages in the mail merge, but needs to be listed together in one paragraph. Is there anyway of looping through the 3...
7
7236
by: giladp1 | last post by:
I found Albert Kallal's great "Super easy Word Merge" code in his site at: http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html Thanks Albert so much for sharing this. I am looking for any comments about the use of the docmd.transfertext method instead of the code Albert used for creating the text file. Also, perhaps some ideas for coding the Subject Line of each email so
0
8574
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9116
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
8990
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
0
7664
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
1
6493
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
4580
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
3007
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
2276
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
1977
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.