Mail Merge issues 1

[BJZeak]

Using Office 2k3 Pro SP3 application to print single labels

Word 2k3 Template is setup with SQL MAIL MERGE Labels where the merge Fields are linked to an ACCESS 2k3 MDB table (one record) Single user access

Access form is used to enter values into this record with a form button that saves the record and calls the Word document

Users have been using this app for over a year with minimal complaints and then today I was bombarded with 2 issues ... I did some investigation and found the following:

Issues:

1) if Word is already open there is a conflict with the Print Margin Settings which halts the print process warning the user with a continue Yes/No dialog ... the Access application explicitly sets the active Word printer to a label printer that has the correct Margin settings prior to printing so not sure why this is occurring

2) even though the record should be flushed to disk prior to the call to Word, it seems Word is not always picking up the current values causing the user to waste a label ... wondering if I need to set a timer event to ensure the record is completely flushed to disk

If Word is closed then this application appears to work for most users ... one of my power users has 30+ screens/apps open at any given time appears to have the most problems with issue 2 (the machine has 4G of Ram but has never maxed out even with all the apps running) I suspect that issue 2 in this case may be related to disk Cache

-----------------------------------------------------------
Private Sub frmPrint_Click()
On Error GoTo Err_frmPrint_Click

Dim chActivePrinter As String
Dim dbo As DAO.Database
Dim rs As DAO.Recordset
Dim objWord As Object

Set dbo = CurrentDb
Set rs = dbpenRecordset("Label", dbOpenDynaset)

rs.MoveFirst
rs.Edit
rs!Field1 = Me!txtField1.Value
rs!Field2 = Me!txtField2.Value
rs!Field3 = Me!txtField3.Value
'.
'.
'.
rs!FieldN = Me!txtFieldN.Value
rs.Update
rs.Close

Set objWord = GetObject("C:\templates\Label.doc", "word.document")

' Add registry entry to stop Word displaying SQL warning
' Dword 0 SQLSecurityCheck Current User Software Microsoft Office 11.0 Word Options

objWord.Application.Visible = False
objWord.Activate

' Preserve current Word Printer
chActivePrinter = objWord.Application.activeprinter

' Point Word at Label Printer
objWord.Application.activeprinter = "\\Server\Label on LPT1:"

' Print Label
objWord.PrintOut

' Restore Original Word Printer
objWord.Application.activeprinter = chActivePrinter

' If more then one Word document is open then don't shut down word
If objWord.Application.Documents.Count > 1 Then

objWord.Application.Visible = True

objWord.Close False

Else

' otherwise shut down Word
objWord.Application.Quit False

End If

Set objWord = Nothing

Exit_frmPrint_Click:
Exit Sub

Err_frmPrint_Click:
MsgBox Err.Description
Resume Exit_frmPrint_Click

End Sub

 

[PHV]

I'd try this:

...
objWord.Application.Visible = False
[!]objWord.DisplayAlerts = 0[/!]
objWord.Activate
...
objWord.PrintOut [!]False[/!]
objWord.Application.activeprinter = chActivePrinter
[!]objWord.DisplayAlerts = -1[/!]
...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJZeak]

Sorry no this doesn't work ...

Access 2K3 SP3 doesn't recognize "DisplayAlerts" ... it isn't in the object list (although I have functions that work in this situation) however at runtime it bails as a not available/recognized option.

I have added a timer which appears to have resolved the write before print issue however the problem of margins still exists when another Word instance is open prior to running this app.

Not sure where the error occurs:

Given that the open document has margins set for the default printer ... and the label template has margins set for the Label printer.

The sequence of events are:

1) the app opens a document with conflicting margins as the active document

2) the app changes the printer for the active document

3) the app prints the current/active document

4) the app restores the active Printer

5) the app closes the active document

The only thing I know for sure is the margin error message occurs somewhere within the first 3 steps

I will either have to add some message boxes or create a backend db to narrow this down as the mdb is not allowing word to connect while in debug mode

 

[BJZeak]

Added messages and the error occurs at the PrintOut Command

Being that Label document is the active Document selected with the correct Printer not sure why this error should occur unless the word object has kept the default printer margin settings ... I will see if I can explicity set the margins.

 

[PHV]

Sorry for the typo:
objWord.Application.DisplayAlerts = 0

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BJZeak]

As usual you rock PHV!!!

Thanks