Why not just use Word Mail Merge?
Hello
I have created a user form in word that helps automate the mail merge from a data base in access.
the problem I have is how to populate an sql statement with a textbox from the word user form.
this is what I have created:
Example: Mail Merge a Microsoft Access Query with a Word Document
The following example opens a Word document called C:\MyMerge.doc and runs a mail merge by using the Customers table in the Microsoft Access sample database Northwind.mdb as its data source. The following sample code assumes that the main document for the merge, C:\MyMerge.doc, already exists. 1.Start Microsoft Access and open any database, or create a new one. 2.Create a module and type the following procedure: Function MergeIt() Dim objWord As Word.Document Set objWord = GetObject("C:\MyMerge.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:\Program Files\Microsoft " & _ "Office\Office\Samples\Northwind.mdb", _ LinkToSource:=True, _ Connection:="TABLE Customers", _ SQLStatement:="SELECT * FROM [Customers]" ' Execute the mail merge. objWord.MailMerge.ExecuteEnd Function
NOTE: If you want to print the merged document, delete the Execute statement above and add the following four lines of code above the End Function statement: objWord.MailMerge.Destination = wdSendToNewDocumentobjWord.MailMerge.Execute'The following line must follow the Execute statement because the'PrintBackground property is available only when a document window is'active. Without this line of code, the function will end before Word'can print the merged document.objWord.Application.Options.PrintBackground = FalseobjWord.Application.ActiveDocument.PrintOut 3.With the module still open in Design view, click
References on the
Tools menu. Add the Word 9 Object Library to the list of available references. If the Object Library is not on the list, click the
Browse button and locate the file Msword9.olb. 4.To test this function, type the following line in the Immediate window, and then press ENTER: ?MergeIt()
An instance of Word opens, displays MyMerge.doc, and then merges it with the Customers table in the sample database Northwind.mdb.
Private Sub CommandButton1_Click()
Documents.Open FileName:="D:\" & ComboBox1.Text, ConfirmConversions:=False, _
ReadOnly:=False, AddToRecentFiles:=False, PasswordDocument:="", _
PasswordTemplate:="", Revert:=False, WritePasswordDocument:="", _
WritePasswordTemplate:="", Format:=wdOpenFormatAuto, DocumentDirection:= _
wdRightToLeft
ActiveDocument.MailMerge.OpenDataSource Name:="D:\managers.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=D:\managers.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDB:Database Password="""";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locki" _
, SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE 'boss'", SQLStatement1:="", SubType:= _
wdMergeSubTypeAccess
ActiveDocument.MailMerge.MainDocumentType = wdMailingLabels
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=False
End With
On Error Resume Next
Windows("madbekot.doc").Activate
Windows("michtav1.doc").Activate
ActiveDocument.Save
ActiveDocument.Close
Unload UserForm1
End Sub
in the sql statement:
, SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE
'boss'", SQLStatement1:="", SubType:= _
instead of boss I want to use whatever I choose in textbox2.text from the word user form
it shoud be something like:
, SQLStatement:="SELECT * FROM `managers` WHERE dereg LIKE
'textbox2.text'", SQLStatement1:="", SubType:= _
but I can't make it work....
any suggestions?
I thank you for your reply!
iris
