Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


Access 2007 Printer Commands

Access 2007 Printer Commands

Access 2007 Printer Commands

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

' 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

RE: Access 2007 Printer Commands


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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

RE: Access 2007 Printer Commands

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

RE: Access 2007 Printer Commands

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.


glassesJust traded in my OLD subtlety...
for a NUance!tongue

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close