Solved: Track Individual Records That Are Merged To Printer

[CreganTur]

I've got an issue I'm trying to figure out on a big project I'm working on. I'm going to try to provide concise logic on what I need without being verbose.

I've got an Access database that contains customer records. This data needs to be printed on a form letter that is already setup in Word. I've got a Mail Merge connection setup between the document and a query in the database so that only those records that are ready to be printed will be sent over. The easiest way to print these letters, that I know of, is to do a Merge To Printer.

Here's the issue I'm trying to figure out: I want to setup some code that will update the [Printed] field in the table (a yes/no field) to yes when each record is printed. This will serve as real-time tracking of every document that is printed. I want to set this up so that if the print is interrupted for some reason, the associate will be able to pick up printing where they left off. Doing this will also allow the user to Merge To Printer a range of documents, instead of all of them, if they choose.

My original thought was to update the records in Access by executing an update query via ADO, grabbing the account number off of the mail merge document as the WHERE condition. My issue is that I have no idea if this can be done while the records are actually being printed. I also have no idea what event I could use to trigger this code. My research of the MailMerge object model hasn't provided any insights, so I'm really hoping for a nudge in the right direction.

[fumei]

Whooooo-boy. This is not my area of expertise. Tony????

"My issue is that I have no idea if this can be done while the records are actually being printed. "

I think this is where the issue/problem/solution will be, but geez, I don't know. I can not see how you could do that without some sort of loop-back testing.

Could get ugly. I will watch this thread in the hope I can learn something.

[CreganTur]

If this isn't possible, then the only other option I can think of is to build code that will move through each mail merged record, execute the SQL update query, and then print the merged document. My only concern with this option is that I imagine it would be a lot slower than Merge To Printer.

Plus I'm having trouble getting my document to merge with the Access query while the database is open :(

[CreganTur]

I am using the following code to open a form letter document and the populate it via MailMerge with data from a query in Access:

Private Sub btnPrint_Click()
Dim objWord As Word.Application
Dim objDoc As Word.Document
Set objWord = New Word.Application
objWord.Visible = True
Set objDoc = objWord.Documents.Open("C:\FC_Letters\MA FC Letter.doc")
'mail merge
objDoc.MailMerge.OpenDataSource Name:="C:\FC_Letters\FC_Letters.mdb", LinkToSource:=True, _
AddToRecentFiles:=False, Connection:="QUERY qryMA_LettersToPrint", _
SQLStatement:="SELECT * FROM [qryMA_LettersToPrint]"
objDoc.MailMerge.Execute
Set objDoc = Nothing
Set objWord = Nothing
End Sub

This is yielding an unexpted result- it is opening the original document where the records are merged and you can navigate between them using the navigation buttons, and it is also opening a Merge To New Document copy as well.

I do not want the Merge To New Document copy. I just want the original- the one where you can navigate between the records using the navigation buttons. The reason for this is that I've decided to setup code to navigate between each record and print it out indivdually so I can run an update query back to the Access Database.

[CreganTur]

Okay... I've made a lot of progress on this issue, so I'm going to mark it as solved.

About the problem in my previous post: I found that removing this line:
objDoc.MailMerge.Execute
stopped Word from created a new document with all of the merged records.

I've given up on trying to read anything from the printer, and I think that's for the best.

I've setup a printer control in Access that mirrors the Merge To Printer control, by providing the same three optins: Print All, Current Record, or a specified range. I've got code setup for each eventuality- it uses PrintOut to send the current record to the printer. It will also move to the next record if needed, and continue in a loop until the desired number of records have been printed.

I know I'm being vague, but I've got a lot of error checking/trapping to do before I feel comfortable sharing this code. Too many opportunities for something to go wrong!

I'm planning on creating a kb article on this when I'm done, so I'll update this thread in the future when/if the article is approved.