Mail Merge issue

[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

 

[Peter Jamieson]

You have to do it separately - e.g. you can use

Dim strResponse As String
strResponse = inputbox("What is the value of...")

However, if you do that, you should ideally check the data they enter, e.g.
so they enter something that does not make the syntax of the eventual SQL
statement wrong.

e.g. if you need WHERE x = 'something'

if the user enters text like

abc'd

reject it.

If you need to get several parameters, it is probably best to use a VBA
userform - see

http://word.mvps.org/FAQs/Userforms/index.htm

You can probably also simplify your OpenDataSource, e.g.

ActiveDocument.MailMerge.OpenDataSource _
Name:="C:\Documents and Settings\My Documents\My Data
Sources\sldnor01.odc", _
Connection:="Provider=SQLOLEDB.1;Integrated Security=SSPI;Persist Security
Info=True;Initial Catalog=orca;Data Source=sldnor01;", _
SQLStatement:="SELECT * FROM ""Tbl_ContractPlacementDetails"""

will probably be enough. If the connection information in Connection will
always be the same as the connection information in the .odc file, you can
probably remove the Connection parameter as well.

Peter Jamieson