Access 2007 Printer Commands

[notconfident]

I am having problems with Access 2007 which opens a Word Document then does a Mailmerge.
The following code works fine to the default printer (which I want to use)
BUT !!!!!!!!!!!!!!!!!!
I want to set the printer to duplex and use Tray 2 (which is the main bulk paper tray)
The tray selection is in case the front tray (1) is open for an envelope, I still want this document to print to Tray 2

I tried setting a macro in the Word document but had problems and I
really want the Access VBA to do all the work so that tis could work with various documents.

I have tried lots of "googling" without success.
Anything using wdxxxxxxxx constants like wdPrinterDefaultBin failed in Access as
the contant was not recognised in Access vba.

If anybody can help could they show some code AND at what point to insert it in my working code below.

Thanks (hopefully)

@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
Here is the WORKING code

Private Sub BtnPrintSigningOnForm_Click()

' Save record
' in case the button is used after editing Marshal detail
If Me.Dirty Then
'MsgBox ("In Dirty")
Me.Dirty = False
End If

Dim objApp As Object
Dim c As Integer
Dim RealDBfolder, strDocName, strConnect As String

' finds the RealDBfolder from CurrentDb.name
' As the Mail Merge document will sit in the same folder
For c = Len(CurrentDb.Name) To 1 Step -1
If Mid$(CurrentDb.Name, c, 1) = "\" Then
RealDBfolder = Left$(CurrentDb.Name, c)
Exit For
End If
Next

'@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@
' File Name below must be the Current Signing On Form
' NOT "\Signing On Form GP15.doc" this was for testing
' strDocName = RealDBfolder & "\Signing On Form GP15.doc" ' For Testing 1 page
strDocName = RealDBfolder & "\2 Page Signing On Form.doc" ' For Testing 2 Page
' strDocName = RealDBfolder & "\S??????????" ' The real thing
' @@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@@


'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
.activedocument.MailMerge.OpenDataSource _
Name:=CurrentDb.Name, _
ReadOnly:=True, _
LinkToSource:=True, _
Connection:="TABLE Current_Marshal", _
SQLStatement:="SELECT * " & _
"FROM [Current_Marshal] " & _
"WHERE [Current_Marshal.Ref_No] = " & [Forms]![Normal View Form]![Ref_No]
End With

'print and close Document
With objApp
.activedocument.MailMerge.Execute Pause:=True
.activedocument.PrintOut Background:=False
' MsgBox ("before close") ' This will keep the document on screen to read it for testing
.activedocument.Close SaveChanges:=False 'Avoid Saving over your template
.Quit SaveChanges:=False 'close all documents
End With

Set objApp = Nothing

DoCmd.Hourglass False 'Cursor back to normal
End Sub

 

[SkipVought]

HI,

First PLEASE get rid of those PESKY NEEDLESS ANNOYING string of @ characters!!!

You stated, "Anything using wdxxxxxxxx constants like wdPrinterDefaultBin failed in Access as
the contant was not recognised in Access VBA."

In the VB editor, MENU, Tools > References... scroll down to Microsoft Word Object Library,
And select that reference. The Word constants can then be used.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]

 

[notconfident]

Hi SkipVought
Sorry for the delay with thanks, I thought I would get an email but didn't.
Also Sorry for the @ characters. I use them in my code to highlight "test" areas where I edit a bit of code to open a report to Screen for testing instead of printing and wasting paper. Your comments show that it does stand out !!!
Anyway thanks for the tip about ticking the Ms Word Object Library that was a good start.
The info I really still need is what command I need to change to duplex and selecting a printer tray AND WHERE TO PUT THE CODE. When I find stuff with google I sometimes waste loads of time with failures because the code is in the wrong place and just fails !!!!
I am self taught as a hobby but the database runs quite well on a small network with over 10,000 records
Hope you can point me in the right direction with my request
Thanks

 

[SkipVought]

Turn on you macro recorder and record doing the printer setup you want.

Post back with your recorded code if you're u need help customizing your macro.

Skip,
[sub]
Just traded in my OLD subtlety...
for a NUance![/sub]