Mail merge with Word

[riri]

Hello,

I m trying to write the code for a mail merge that takes the data
from
sql server and I want to prompt the user with a textbox and then
query
the database what is the syntax for that?


Sub Macro1()
'
' Macro1 Macro


'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\My Documents\My Data
Sources\sldnor01.odc" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security
Info=True;Initial Catalog=orca;Data Source=sldnor01;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=7YNDT2JXP;Use Encryption for Data=False;Tag with column colla" _
, SQLStatement:="SELECT * FROM
""Tbl_ContractPlacementDetails""", _
SQLStatement1:="", SubType:=wdMergeSubTypeOther
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range,
Name:=
_
"Client_ContactName"
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub


I would like to put a where after the select with equal to a
textbox.value


Is that possible? Please help


Thanks


Ria

 

[Cindy M.]

Hi Riri,

I m trying to write the code for a mail merge that takes the data
from
sql server and I want to prompt the user with a textbox and then
query
the database what is the syntax for that?


Sub Macro1()
'
' Macro1 Macro


'
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:= _
"C:\Documents and Settings\My Documents\My Data
Sources\sldnor01.odc" _
, ConfirmConversions:=False, ReadOnly:=False,
LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="",
PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="",
Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist
Security
Info=True;Initial Catalog=orca;Data Source=sldnor01;Use Procedure for
Prepare=1;Auto Translate=True;Packet Size=4096;Workstation
ID=7YNDT2JXP;Use Encryption for Data=False;Tag with column colla" _
, SQLStatement:="SELECT * FROM
""Tbl_ContractPlacementDetails""", _
SQLStatement1:="", SubType:=wdMergeSubTypeOther
ActiveDocument.MailMerge.Fields.Add Range:=Selection.Range,
Name:=
_
"Client_ContactName"
ActiveDocument.MailMerge.ViewMailMergeFieldCodes = wdToggle
End Sub


I would like to put a where after the select with equal to a
textbox.value


Is that possible?

Yes, it's possible. The simplest form for getting the user response is
an InputBox. Note that the following sample code has no kind of error
check, it's just to give you an idea. It also assumes that the field
you want to filter on is of the String data type.

SUserResponse = InputBox("Please specify which record by typing
xyz")
ActiveDocument.MailMerge.DataSource.QueryString = "SELECT * FROM

""Tbl_ContractPlacementDetails""" & " WHERE [fieldname]='" &

sUserResponse & "'"

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 17 2005)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail