Word - Access data source problem

[74Stag]

Hi

I am trying to create a template in Word which uses an Access query as its data source.

The problem seems to stem from the fact the query uses form data as criteria. If the form data criteria is removed all works well, if it is put back in however, the word document is unable to see the data source.

The merge will be performed using code in the access database. Does anyone know how I can get around this problem either in Word or Access?

 

[MoiraT]

Hi
It sounds like there could be something wrong with your criteria in the query. I do the same thing (I think), ie when a user clicks a button the code runs a query with the form criteria and outputs a document based on a template in Word. This is my code if it helps:

' set the path to the templates folder
NewDocPath = "S:\CMS\Template\" & DocumentName

DoCmd.OutputTo acOutputQuery, "qStudentMergeData", acFormatRTF, "S:\CMS\MergeDB.doc"
objWord.Documents.Add (NewDocPath)
DocName = objWord.ActiveDocument.Name
With objWord.ActiveDocument.MailMerge
.OpenDataSource "S:\CMS\MergeDB.doc"
.Destination = wdSendToNewDocument
.Execute
End With

objWord.Documents(DocName).Close False

DoCmd.Hourglass False

objWord.Visible = True
objWord.Activate
Set objWord = Nothing

In the query I have the criteria to select only the record with the ID that matches the form record:
[Forms]![MyForm]![ID]

Hope that helps

 

[74Stag]

Thanks for that I was starting to think I would never get a reply!

The problem is more to do with actually setting up the word template in the first place. I can't select the query I want to use for the merge from inside word as it doesn't appear on the list of tables and queries.

If I try to run the merge without the data source pre-set it asks for the source to be selected, but once again won't display the query I need to use.

 

[MoiraT]

Which version of Word and Access are you using?

How are you trying to link to the Access query from Word? I've just tried it with 2002 (XP) and it works fine but you need to remove the criteria from your query, then set up the mail merge template in Word linking to the query then put the criteria back into the query.

 

[74Stag]

I am using Word and Access 2002. I have created the template without the criteria in the Query as you said. I have also tried running the code with the criteria missing and it is still asking me to select the data source.

I am also trying it with your code now. I have posted my version below. Am I just missing something obvious?

Dim DocumentName As String
Dim objWord As New Word.Application

DocumentName = "ClubMembersAccepted.doc"

' set the path to the templates folder
NewDocPath = "C:\Documents and Settings\stephens\Desktop\" & DocumentName

DoCmd.OutputTo acOutputQuery, "queryClubMembersAccepted", acFormatRTF, DocumentName
objWord.Documents.Add (NewDocPath)
DocName = objWord.ActiveDocument.name
With objWord.ActiveDocument.MailMerge
.OpenDataSource "\\Sbs-server\Users\StephenS\Database backup\CRB Project.doc1.mdb"
.Destination = wdSendToNewDocument
.Execute
End With

objWord.Documents(DocName).Close False

DoCmd.Hourglass False

objWord.Visible = True
objWord.Activate
Set objWord = Nothing

 

[MoiraT]

Hi 74Stag
This is how I have it set up in my db:
When user has the form open and wants to produce the letter for the record showing in the form, they click a command button which has this code as the Event Procedure for the OnClick event:

Private Sub CmdButton_Click()
DoCmd.RunCommand acCmdSaveRecord
MergeDocument "MyLetter.dot"
End Sub

The MergeDocument function is in full below and is in the Modules section of the db:

Public Sub MergeDocument(DocumentName)

' function to produce selected letters based on the qStudentMergeData query

Dim NewDocPath As String

Dim objWord As Word.Application, DocName As String
DoCmd.Hourglass True
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If objWord Is Nothing Then
Set objWord = New Word.Application
If objWord Is Nothing Then
MsgBox "MS Word is not installed on your computer"
End If
End If
On Error GoTo ErrorHandler

' set the path to the templates folder
NewDocPath = "S:\CMS\Template\" & DocumentName
DoCmd.OutputTo acOutputQuery, "qStudentMergeData", acFormatRTF, "S:\CMS\MergeDB.doc"

objWord.Documents.Add (NewDocPath)
DocName = objWord.ActiveDocument.Name
With objWord.ActiveDocument.MailMerge
.OpenDataSource "S:\CMS\MergeDB.doc"
.Destination = wdSendToNewDocument
.Execute
End With

objWord.Documents(DocName).Close False

DoCmd.Hourglass False

objWord.Visible = True
objWord.Activate
Set objWord = Nothing
Exit Sub
ErrorHandler:
DoCmd.Hourglass False
If Err.Number = 2302 Then
MsgBox "It looks like you are already using the data needed for the mail merge in another application." _
& vbCrLf & vbCrLf & "Try closing all your MS Word documents and try again.", vbInformation, "Nothing to worry about"
Else
MsgBox "It looks like we have a problem. Please write down the error number and what you were doing then call technical support." _
& vbCrLf & vbCrLf & "Error number: " & Err.Number & " and Description: " & Error, vbExclamation, "This could be serious"
End If
objWord.Visible = True
Set objWord = Nothing
End Sub


Just a thought - did you put the criteria back in the query after you had set up the template?

HTH

 

[lupins46]

Are you trying to use a parameter query as the source?

Parameter queries are not accessible through the standard mail-merge menus in Word.

 

[74Stag]

I have tried running the code with the criteria and without it but it still keeps asking me to select the data source.

It also says "This recordset is not updateable" in the bottom left of the screen just before asking me to select the data source.

If I debug it the code seems to be failing on the following line:

.OpenDataSource "\\Sbs-server\Users\StephenS\CRB Project.doc1.mdb"

I have to admit I am reaching the limit of my knowledge here with VBA. I much prefer C++!

 

[MoiraT]

That definitely sounds like it is your query that is causing the problem, how is it set up, ie tables, links, etc?