get data from access

[Jon LeBaugh]

I have a couple of different questions to address with this post, so please
bear with me.

First, I will explain the objective. I am looking to automate creation of a
word doc with VBA from two queries in Access.

I currently having it performing a mail merge from within my vba code,
however, I don't think this is necessary as it is only creating one form at a
time. I would rather use bookmarks so that I can format the information
within the code as I need. I do not know how to do this. If someone could
provide a sample piece of code to put open the database connection and run
the query as I need, then place it into a bookmark, I'm sure I can go from
there. I believe this will solve my second issue of wanting to pull
information from two seperate queries to put into this document.

Here is the code I have currently, to give you a slightly better idea of
what I have going on

Private Sub Document_New()
merchnum = InputBox$("MID:", "Merchant Number Input")
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.MainDocumentType = wdNotAMergeDocument
ActiveDocument.MailMerge.MainDocumentType = wdFormLetters
ActiveDocument.MailMerge.OpenDataSource Name:="c:\testdatabase.mdb", _
ConfirmConversions:=False, ReadOnly:=False, LinkToSource:=True, _
AddToRecentFiles:=False, PasswordDocument:="", PasswordTemplate:="", _
WritePasswordDocument:="", WritePasswordTemplate:="", Revert:=False, _
Format:=wdOpenFormatAuto, Connection:= _
"DSN=MS Access Database;DBQ=testdatabase.mdb;DriverId=25;FIL=MS
Access;MaxBufferSize=2048;PageTimeout=5;PWD=test;UID=admin;" _
, SQLStatement:="SELECT * FROM `qry - test` Where `Merchant number`
= " + merchnum, SQLStatement1:="", _
SubType:=wdMergeSubTypeOther
With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
With .DataSource
.FirstRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
.LastRecord = ActiveDocument.MailMerge.DataSource.ActiveRecord
End With
.Execute Pause:=False
End With
Application.Move Left:=387, Top:=1
ChangeFileOpenDirectory "F:\Shared Drive Main\apriva\"
ActiveDocument.SaveAs FileName:="merchnum.doc", FileFormat:= _
wdFormatDocument, LockComments:=False, Password:="",
AddToRecentFiles:= _
True, WritePassword:="", ReadOnlyRecommended:=False,
EmbedTrueTypeFonts:= _
False, SaveNativePictureFormat:=False, SaveFormsData:=False, _
SaveAsAOCELetter:=False
ActiveDocument.Close
ActiveDocument.Close (wdDoNotSaveChanges)
End Sub

THANKS

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?Sm9uIExlQmF1Z2g=?=,

I would rather use bookmarks so that I can format the information
within the code as I need. I do not know how to do this. If someone could
provide a sample piece of code to put open the database connection and run
the query as I need, then place it into a bookmark, I'm sure I can go from
there.

WdAcc97.zip in the Mail Merge section of my website. I think you'll find it in
the "Specila Merges" section, under the topic dealing with multiple items per
record (one-to-many). It's older, and uses a DAO rather than ADO connection.
But the Word part is exactly for later versions, and that's the part you're
interested in.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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