notconfident
Programmer
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
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