Sending Query Results to Word Doc

  • Thread starter Thread starter S Jackson
  • Start date Start date
S

S Jackson

I want to send the results of a query to a word document in the form of a
table. Originally, I tried setting up the word document as a merge
(catalogue) template file using the query as the data source. I built a
form in Access that requests that the user to select dates as parameters for
the query. The following is the code behind the "Enter" button on the form
which does not work since you get a message from Word saying the data source
is "in use" (Access is open when Word is attempting to open it as a data
source to complete the merge):

Private Sub cmdOk_Click()
On Error GoTo Err_cmdOk_Click

Dim stDocName As String

stDocName = "qryMonthEnd-Status"

DoCmd.OpenQuery stDocName

Dim objWord As Word.Application
Set objWord = New Word.Application
objWord.Documents.Add Application.CurrentProject.Path & "\Status_Rpt.dot"
objWord.Visible = True

With ActiveDocument.MailMerge
.Destination = wdSendToNewDocument
.MailAsAttachment = False
.MailAddressFieldName = ""
.MailSubject = ""
.SuppressBlankLines = True
With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord
End With
.Execute Pause:=True
End With
Windows("STATUS_RPT.dot").Activate
ActiveWindow.Close

'Release the Word object
Set objWord = Nothing
Exit_cmdOk_Click:
Exit Sub

Err_cmdOk_Click:
MsgBox Err.Description
Resume Exit_cmdOk_Click

End Sub


I have in the past figured out how to send the current record to Word using
automation by creating a word template, and inserting bookmarks. But in
this instance, I want to send all of the records contained in the query to
one Word document (a table). Do I have to transfer the query results to an
Excel spreadsheet and then use that as the Word data source? Seems like an
unnecessary extra step and there must be an easier way.
TIA
S. Jackson
 
S:

1.) There's no need to open your query in Access in the code

2.) Link to your query from the MailMerge call like so:

Const wdUseQuery = 1

With objWordDoc.MailMerge
.OpenDataSource Name:=strTargetDbName
.Linktosource:=False
.ReadOnly:=True
.AddtoRecentFiles:=False
.Connection:= wdUseQuery & " " & strQueryName
.Destination = wdSendToNewDocument
End With

HTH
 
Thanks for your response. Unfortunately, I cannot make your suggestion
work. FYI, I am using Office 2k and I am placing this code behind a command
button on a form within the database.

Here is how I tried to incorporate your sugggestion. First I tried setting
the variables you gave me. Did I do it right?
--------------
Const wdUseQuery = 1
Dim strQueryName As String
strQueryName = "qryMonthEnd-Status"
Dim strTargetDBName As String
strTargetDBName = "Case Management Database.mdb"
Dim objWord As Word.Application

Set objWord = New Word.Application
objWord.Documents.Add Application.CurrentProject.Path &
"\Status_Rpt.dot"
objWord.Visible = True

With objWord.ActiveDocument.MailMerge
.OpenDataSource Name:=strTargetDBName
.Linktosource:=False
.ReadOnly:=True
.AddtoRecentFiles:=False
.Connection:=wdUseQuery & " " & strQueryName
.Destination = wdSendToNewDocument
End With
--------------
VB gave me a compile error (Expected: expression) for the following:
.Linktosource:=False
.ReadOnly:=True
.AddtoRecentFiles:=False
.Connection:=wdUseQuery & " " & strQueryName

VB says that it cannot find data members: Linktosource | ReadOnly |
AddtoRecentFiles. I tried finding alternates thinking that maybe you gave
me code appropriate to a newer version of Office, but I cannot figure it
out. (FYI, although I've been attempting to learn VB for several years now,
I am still struggling mightily.)

TIA
S. Jackson
 
Removing the colons does not eliminate the following error:

Compile Error: Method or data member not found
for:
..Linktosource
..ReadOnly
..AddtoRecentFiles
..Connection

Is it because I am using Office 2k? Are they only found in newer versions
of Office?
S. Jackson
 
Here is how I got rid of the compile errors:
Const wdUseQuery = 1
Dim strQueryName As String
strQueryName = "qryMonthEnd-Status"
Dim strTargetDBName As String
strTargetDBName = "Case Management Database.mdb"
Dim objWord As Word.Application
Set objWord = New Word.Application
objWord.Documents.Add Application.CurrentProject.Path &
"\Status_Rpt.dot"
objWord.Visible = True

With objWord.ActiveDocument.MailMerge
.OpenDataSource Name:=strTargetDBName, _
ReadOnly:=True, LinkToSource:=False, _
AddToRecentFiles:=False, _
Connection:=wdUseQuery & " " & strQueryName
.Destination = wdSendToNewDocument
End With
'Release Word object
Set objWord = Nothing

However, this code does not work. After pressing the command button on the
form in the database which executes this code, I get a message that the
database has been locked by another user. I am guessing this is because of
this line in the code: .OpenDataSource Name:=strTargetDBName

How do I tell Word that the datasource is already open?

TIA
S. Jackson
 
Back
Top