hello,

I have a db whit a query, now when a user is clicking on a button in a form there most be print a number of letters whit some data of the query.

I have now the following code:

the word file has all the correct merg reference and no connection.

only when i am running the code it will stop in word and ask me to select the correct table/query. How ever i give the query in the line: Connection:="QUERY " & MyQuery

I have try moste options under ActiveDocument.MailMerge.OpenDataSource but don't get it to work. Does someone now how to get round this message form word?

Richard Karelse

Hi Richard,

To find out what is hapening, you may like to include the following code to help debug the problem:

'Use the Query defined in the arguments as the datasource
xx = Connection:= & "QUERY " & MyQuery
msgbox xx
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:= & xx

This should atleast allow you to confirm that the name of the query is being correctly passed to this subroutine.

Hope it helps.

Alan

Alan i all ready now that the query name is correct. That is not the problem. when i run the procedure i give the query In hard code. and there i have typed it right.

I had that same problem when I tried to do the same thing as you but I could never found out the problem either. I still have the problem, but mine is with an excel file. Asks what sheet I want to merge with. Very annoying.

Well hope someone will answer this question. I am in the same boat as you Karel.

Kevin

Richard,

Could you just confirm that the code you originally typed is correct?

What I'm referring to is the line ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QUERY " & MyQuery

Should it read ...

.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:= & "QUERY " & MyQuery

Notice the extra '&'.

Regards,

Alan

Alan when i insert the extra & i get a syntax error.
Why should you use a & if there is no other string in front of it?
The & just puts them together not more than that.

All right then,
i have found the solution for my problem.
Kevinsharrison here special for you the correct code how it works:

Public Sub MergeToWord(strDocName As String, MyQuery As String)
Dim objApp As Object

'DoCmd.Close

'Change cursor to hourglass
DoCmd.Hourglass True

'Open Mailmerge Document
'Start Word


Set objApp = CreateObject("Word.Application")
With objApp
    .Visible = True 'Make it visible
    .Documents.Open strDocName 'Open the Mailmerge Document

    'Use the Query defined in the arguments as the datasource

   strConnect = "DSN=MS Access Database;DBQ=" & CurrentDb.Name & "FIL=MS Access;"
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, _
ReadOnly:=True, LinkToSource:=True, _
          Connection:=strConnect, _
          SQLStatement:="SELECT * FROM [" & MyQuery & "]"




End With

'print and close Document
With objApp
   .ActiveDocument.MailMerge.Execute Pause:=True
   .ActiveDocument.PrintOut Background:=False
   .ActiveDocument.Close SaveChanges:=wdDoNotSaveChanges 'Avoid Saving over your template
   .Quit SaveChanges:=wdDoNotSaveChanges 'close all documents
End With


Set objApp = Nothing

ErrorHandler:
Select Case Err.Number
Case 4157
End Select

DoCmd.Hourglass False 'Cursor back to normal

 ' stDocName = "FRMbrfwaarschuwingNL"
  ' DoCmd.OpenForm stDocName, , , stLinkCriteria
End Sub