Printing Merged MSWord Doc

Hi to everyone…again

I have been trying to learn how to print a merged word document from a button on a form. Have read the MS knowledge base site and numerous posts and I thought I had come up with a winner…but I was wrong, so looking for your tutelage once again.

Name of DB: Test.mbd
Form: FrmSampleNotice
Fields on Form: Surname
Location
Report Number
Button Name:BtnSampleNotice
Query: QrySampleNotice
Word Document Path: C:\My Documents\Learning Access\Merge Sample Notice.doc

I know the Query works fine and I know that when I run the merge from the .doc it works ok also, but now I am trying to get to do it from the button on the form. I also know that the event procedure is linked to the OnClick properties. I do not get any error messages or VB errors either so not sure where else to look.

Here is the code that I am using:

Private Sub BtnSampleNotice_OnClick()
Call MergeToWord("C:\My Documents\Learning Access\Merge Sample Notice.doc", "QrySampleNotice") 'For example
End Sub

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

'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
.ActiveDocument.MailMerge.OpenDataSource Name:=CurrentDb.Name, Connection:="QrySampleNotice" & MyQuery

End With

'Create, print and close Document
With objApp
.ActiveDocument.MailMerge.Execute 'execute mailmerge
.ActiveDocument.PrintOut Background:=False, Copies:=1 'print out 1 copy
.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
End Sub

When this is working I would like to also incorporate the code that wayneryan helped me with the other day using the Nz(Dcount…) so if the query is null then no “Notices” will print which I think will look like this but not sure:
'
' If no related records, msgbox, then exit sub
'
If Nz(DCount("[Surname] or [Report Number] or [Location]", _
"[QrySampleNotices]", _
"[Surname] or [Report Number] or [Location]", = '" & Me.[Surname] or [Report Number] or [Location]", & "'"), 0) = 0 Then
MsgBox "There Are No Person(s) Listed Under That Name", vbOKOnly, "Invalid Search Criterion!"
Me.[Report Number].SetFocus
Exit Sub
End If

As always thanks for the help.

BTW Wayne if you are reading, you enjoyed the Rums and Coke!!!

Navyguy

update on this

I tried copying and pasting the word document into a Access Report and I thought Ah Ha got it now, but when I opened the report the text was all misaligned and where I only left so much room for a memo field on the report, it expanded over the pasted word document and cut off the next paragraph.

I think that I am back to trying to print the word document from access and using the code I posted earlier.

I searched the forum again and found many references both to printing and merging word documents but in any of them I did not find a complete resoultion.

As always tanks for your help...

Navyguy

Another Update

I spent the good part of the day working on this and this is what I have come up with...

I created a "module" and called it ModSampleNotice and used this code that I got from the MSAccess Knowledge Base site...

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\SampleNotice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = False
' Set the mail merge data source as the Current database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Test.mdb", _
LinkToSource:=True, _
Connection:="QUERY QrySampleNotice", _
SQLStatement:="SELECT * FROM [QrySampleNotice]"
' Execute the mail merge.
objWord.MailMerge.Execute

objWord.MailMerge.Destination = wdSendToNewDocument
objWord.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 = False
objWord.Application.ActiveDocument.PrintOut

End Function

I also completed the suggestion in the code to remove the excute statement and add the four lines of code.

It works good!!!

On my form "FrmSampleNotice" I created a button "CmdSampleNotice" and behind this button I pased the following lines of code:

Private Sub BtnSampleNotice_Click()
DoCmd.OpenModule , MergeIt()
End Sub

I attached this to the click event procedure. The only issue that I have now is when I click the button, the MSWord document merges and prints fine but after it is all done I get an error "Run-Time Error 2520 Name arguement required" and the debugger opens the VB on the button and highlights the DOCmd line.

Once I get this working I will add some of the other items like the Nz(DCount...).

Thanks to everybody

Navyguy

Navyguy said:

I spent the good part of the day working on this and this is what I have come up with...

I created a "module" and called it ModSampleNotice and used this code that I got from the MSAccess Knowledge Base site...

Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("C:\SampleNotice.doc", "Word.Document")
' Make Word visible.
objWord.Application.Visible = False
' Set the mail merge data source as the Current database.
objWord.MailMerge.OpenDataSource _
Name:="C:\Test.mdb", _
LinkToSource:=True, _
Connection:="QUERY QrySampleNotice", _
SQLStatement:="SELECT * FROM [QrySampleNotice]"
' Execute the mail merge.
objWord.MailMerge.Execute

objWord.MailMerge.Destination = wdSendToNewDocument
objWord.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 = False
objWord.Application.ActiveDocument.PrintOut

End Function

I also completed the suggestion in the code to remove the excute statement and add the four lines of code.

It works good!!!

On my form "FrmSampleNotice" I created a button "CmdSampleNotice" and behind this button I pased the following lines of code:

Private Sub BtnSampleNotice_Click()
DoCmd.OpenModule , MergeIt()
End Sub

I attached this to the click event procedure. The only issue that I have now is when I click the button, the MSWord document merges and prints fine but after it is all done I get an error "Run-Time Error 2520 Name arguement required" and the debugger opens the VB on the button and highlights the DOCmd line.

Once I get this working I will add some of the other items like the Nz(DCount...).

Thanks to everybody

Navyguy

Click to expand...

Hi and Thanks for your reply.

Did you solve the Run-time error 2520, if so how.

Many Thanks

Dereck

I guess I dropped the ball...

Hi Dereck

That was quite awhile ago an I forgot to follow-up I think I had to reset my references to Word 9 Object Library and had to relink my merged document to the right query. I believe it was because the MergeIt() module needs these specific references. That was the only thing that sticks out from the entire experience.

I think that was it...I guess I shouldn't make promises I can't keep!!!

Navyguy