Mail merge to Word from Access 2000

[Anthony Dowd]

Hi

I am trying to create code for a command button on a MS Access 2000 form,
which, when clicked, uses mail merge to create a Word document for the
active record only.

The code I am using (see below) is contained within a Module in Access. It
successfully creates merges Word docs for ALL of the records, but I want to
create a single document for the active record only.

*****************start code*********************
Function MergeIt()

Dim objWord As Word.Document
Set objWord = GetObject("C:\My Doc.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="C:\My Database File.mdb", _
LinkToSource:=True, _
Connection:="QUERY Patient Details Query", _
SQLStatement:="SELECT * FROM [Patient Details Query]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

****************end code************************

I tried inserting a WHERE statement within the SQLStatement

ie. SQLStatement:="SELECT * FROM [Patient Details Query]" & _
"WHERE [PatientID]= Me!PatientID"

but this didn't work.

Any ideas for a WHERE statement that will merge only a single record into
Word? If not, is there perhaps another approach?

Thanks in Advance
Anthony

 

[JulieD]

Hi Anthony

you could try modifing your where statement to
WHERE (PatientID=[Forms]![FRM_Patient]![PatientID])

(using your form name, of course)

Regards
julieD

 

[Peter Jamieson]

One of the problems here is that when /Word/executes the SQL, I don't think
either Me!PatientID or [Forms]|[FRM_Patient]![PatientID] will be correctly
interpreted. If not, you would need something more like

SQLStatement:="SELECT * FROM [Patient Details Query]" & _
" WHERE [PatientID]=" & CStr(Me!PatientID)

or

SQLStatement:="SELECT * FROM [Patient Details Query]" & _
" WHERE (PatientID=" & CStr([Forms]![FRM_Patient]![PatientID]) & ")"

or soe such thing, assuming that the PatientID is numeric, otherwise you
need to remove the CStr(), which is probably overkill anyway, and
single-quote the ID, e.g.

SQLStatement:="SELECT * FROM [Patient Details Query]" & _
" WHERE [PatientID]='" & Me!PatientID & "'"



--
Peter Jamieson

Hi Anthony

you could try modifing your where statement to
WHERE (PatientID=[Forms]![FRM_Patient]![PatientID])

(using your form name, of course)

Regards
julieD

Hi

I am trying to create code for a command button on a MS Access 2000 form,
which, when clicked, uses mail merge to create a Word document for the
active record only.

The code I am using (see below) is contained within a Module in Access. It
successfully creates merges Word docs for ALL of the records, but I want to
create a single document for the active record only.

*****************start code*********************
Function MergeIt()

Dim objWord As Word.Document
Set objWord = GetObject("C:\My Doc.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the Northwind database.
objWord.MailMerge.OpenDataSource _
Name:="C:\My Database File.mdb", _
LinkToSource:=True, _
Connection:="QUERY Patient Details Query", _
SQLStatement:="SELECT * FROM [Patient Details Query]"
' Execute the mail merge.
objWord.MailMerge.Execute
End Function

****************end code************************

I tried inserting a WHERE statement within the SQLStatement

ie. SQLStatement:="SELECT * FROM [Patient Details Query]" & _
"WHERE [PatientID]= Me!PatientID"

but this didn't work.

Any ideas for a WHERE statement that will merge only a single record into
Word? If not, is there perhaps another approach?

Thanks in Advance
Anthony