Access 2007 Printer Commands

Question

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

Answer 1

wdPrinterDefaultBin is defined in the Word object model. What you should do is select Tools > References, and add a reference to Microsoft Word [version] Object Library.

That's called "Early Binding" when you search for it online. It is the preferred method if all users will have Word installed. The opposite is "Late Binding" which is what you were doing with CreateObject. Replace that with:

Dim objApp as Word.Application
Set objApp = New Word.Application

Now you have intellisense, and the constants such as wd* are defined.

---

-Tom. Microsoft Access MVP

Answer 2

Hi Tom

Sorry for the delay with thanks, I thought I would get an email but didn't.
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

Answer 3

Hi Keith,

Did you want to change the print in the Access? If I understood correctly, we can use achieve the goal by changing Application.Printer Property. The following example makes the first printer in the Printers collection the default printer for the system, and then reports its name, driver information, and port information.

Dim prtDefault As Printer 
 
Set Application.Printer = Application.Printers(0) 
 
Set prtDefault = Application.Printer 
 
With prtDefault 
 MsgBox "Device name: " & .DeviceName & vbCr _ 
 & "Driver name: " & .DriverName & vbCr _ 
 & "Port: " & .Port 
End With 

 

However, the Access object mode doesn't provide the function to change the tray of the printer. As a workaround, we can define multiple printers base on the tray and shoes the different printer based on the tray we wanted. More detail about print settings please refer to link below:

Printer Object (Access)

In addition, you can refer to the link below about Specifying a Paper Tray in a Macro:
http://excel.tips.net/T002213_Specifying_a_Paper_Tray_in_a_Macro.html

Disclaimer: This response contains a reference to a third party World Wide Web site. Microsoft is providing this information as a convenience to you. Microsoft does not control these sites and has not tested any software or information found on these sites; therefore, Microsoft cannot make any representations regarding the quality, safety, or suitability of any software or information found there. There are inherent dangers in the use of any software found on the Internet, and Microsoft cautions you to make sure that you completely understand the risk before retrieving any software from the Internet.

Regards & Fei

---

We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
Click HERE to participate the survey.