Directing/Specifying a Print Request/Location

[Doctorjones_md]

I'm pretty sure this is possible, but I'm lost in trying to figure it out
....

I have (2) pieces of code which work individually -- I need to
modify/incorporate the two so that they print data into the same WORD
template

Here's my code:
================
(This sub prints the data on an ACCESS form)
Private Sub Command83_Click()
On Error GoTo Err_Command83_Click

Dim stDocName As String
Dim MyForm As Form

stDocName = "PrintDetails"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True
DoCmd.PrintOut (I need to be able to specify the document to print to --
at a bookmarked location)
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_Command83_Click:
Exit Sub

Err_Command83_Click:
MsgBox Err.Description
Resume Exit_Command83_Click

End Sub
===========================================
(This piece of code inserts some data into bookmarks -- no problem with
this -- it effectively print into the WORD template)
Sub CreateLetter(strTemplate As String)

' Opens a document in Word and inserts values from
' current record at bookmarks in Word document.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim frm As Form
Dim strAddress As String

' return reference to form
Set frm = Forms!frmAddresses

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
objWord.WindowState = wdWindowStateMaximize

' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "FirstName", frm!FirstName
InsertAtBookmarks objWord, objDoc, "LastName", frm!LastName
strAddress = (frm!Address + vbNewLine) & (frm!Address2 + vbNewLine) & _
(frm!City.Column(1) + vbNewLine) & (frm!County + vbNewLine) &
frm!PostCode
InsertAtBookmarks objWord, objDoc, "Address", strAddress
InsertAtBookmarks objWord, objDoc, "CurrentDate", Format(VBA.Date(), "d
mmmm yyyy")
InsertAtBookmarks objWord, objDoc, "ToName", frm!FirstName

Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub
==========================================

What I need is to be able to change the "DoCmd.PrintOut" in the 1st piece of
code so that the data on the "Print Details" form will print at a Bookmarked
location in an EXISTING Word Template.

How would I go about doing this?

Thanks in advance ...

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

I'm pretty sure this is possible, but I'm lost in trying to figure it
out ...

I have (2) pieces of code which work individually -- I need to
modify/incorporate the two so that they print data into the same WORD
template

Here's my code:

What I need is to be able to change the "DoCmd.PrintOut" in the 1st
piece of code so that the data on the "Print Details" form will print
at a Bookmarked location in an EXISTING Word Template.

How would I go about doing this?

Thanks in advance ...

I have difficulty understanding your problem:

The first sub prints a document on paper with a printer.
The second sub insert text at bookmark locations using code "on a computer
screen."

How can you " print at a Bookmarked location in an EXISTING Word Template",
I mean how can you print a full document at a bookmarked location in a
document that exists only on a computer screen?

Sorry, but I am not seeing exactly what you are trying to do.

If your wording is not quite right and you meant to say that the first piece
of code should insert some information at a bookmark in the document created
by the second piece of code so that you can print that second document
later, then yes, it can be done.

When using the first piece of code, just make sure that a document created
from the second piece of code is actually open. You could do that by
checking the attached template of each opened document in all Word instances
currently open.

If you find one, just set a document object to that found document and use
the same kind of code that you used in the second sub.
If you do not find such a document, either create it or abort.

Alternatively, if it has to be the same specific document, you could set a
flag when the second sub is run, then, the first sub would check for the
flag state to find out if the document was created. The flag could be a
string with the document name, or an object pointing to that document.



--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Jean,

Thank you for your assistance on this --

You're correct in that my wording was a bit off in my initial post -- > If
your wording is not quite right and you meant to say that the first piece
of code should insert some information at a bookmark in the document created
by the second piece of code so that you can print that second document
later, then yes, it can be done.

I'm not sure of the BEST way to handle this issue.

I have a Form with several pages/tabs on it. On one of the pages/tabs,
there is a Sub-Form (which is linked to the Master Form on OrderID and
VersionNumber fields from 2 different tables). There are several products
(recordsets) on the Sub-Form for each OrderID/VersionNumber combination on
the Master Form. I have the Sub-Form set up as a Continuous Form (so that
all the records show).

I use the FormFields method to insert data from my Master From to my WORD
template (and this portion works well), but I can't see this method working
for the Sub-Form (since it involves numerous recordsets) -- am I correct in
this assumption? (See Code Below)

I've tried to modify the following sub (using the RecordSetClone method in
Sub#2: Private Sub Command88_Click() shown below) to insert the Sub-Form
data into a bookmark in my WORD template -- this prints just fine to the
Immediate Window in the VBE, but I'm not sure what I need to do to get it to
insert the data into a Table or bookmark in the same template that the data
from the Master Form is going to)

Here's my code:
=================================
DECLARATIONS:



Option Compare Database

Option Explicit

Dim path As String

Const DOC_PATH1 As String = "\\Fileserver\TEST\"

Const DOC_NAME1 As String = _

"Product1.dot"

Const DOC_PATH2 As String = "\\ Fileserver\TEST\"

Const DOC_NAME2 As String = _

" Product2.dot"

Const DOC_PATH3 As String = "\\ Fileserver\TEST\"

Const DOC_NAME3 As String = _

" Product3.dot "

Private Sub cmdPrintProductInfo_Click()

Dim appWord As Word.Application

Dim doc As Word.Document

Dim rst As ADODB.Recordset

Dim strSQL As String



On Error Resume Next

Set appWord = GetObject(, "Word.application")

If Err = 429 Then

Set appWord = New Word.Application

Err = 0

End If



With appWord

Set doc = .Documents(DOC_NAME1)

If Err = 0 Then

If MsgBox("Do you want to save the current document " _

& "before updating the data?", vbYesNo) = vbYes Then

.Dialogs(wdDialogFileSaveAs).Show

End If

doc.Close False

End If



On Error GoTo ErrorHandler



Set doc = .Documents.Open(DOC_PATH1 & DOC_NAME1, , True)



With doc

.FormFields("fldCompanyName").result = Nz(Me!CompanyName)

.FormFields("fldAddress1").result = Nz(Me!Address1)

.FormFields("fldAddress2").result = Nz(Me!Address2)

.FormFields("fldCity").result = Nz(Me!City)

.FormFields("fldRegion").result = Nz(Me!Region)

.FormFields("fldPostalCode").result = Nz(Me!PostalCode)

.FormFields("fldPrice").result = Nz(Me!Price)

.FormFields("fldDeliveryCost").result = Nz(Me!DeliveryCost)



End With

.Visible = True

.Activate

End With



Set rst = Nothing

Set doc = Nothing

Set appWord = Nothing

Exit Sub



ErrorHandler:

MsgBox Err & Err.Description



The (3) subs below present my Sub-Form data - which method would work BEST
for inserting the data into my WORD template (DOC_PATH1 & DOC_NAME1)
referenced in my DECLARATIONS, and how would I modify the code (in either)
so that it inserts the Sub-Form data into a bookmark location in the WORD
template?

Sub#1 -- this seems to be a good option -- it simply captures the
information on the ACCESS form just fine -- what's the BEST way to insert at
a bookmark location in my template (DOC_PATH1 & DOC_NAME1) referenced in my
DECLARATIONS?



Private Sub Command83_Click()

On Error GoTo Err_Command83_Click



Dim stDocName As String

Dim MyForm As Form



stDocName = "PrintDetails"

Set MyForm = Screen.ActiveForm

DoCmd.SelectObject acForm, stDocName, True

DoCmd.PrintOut

DoCmd.SelectObject acForm, MyForm.Name, False



Exit_Command83_Click:

Exit Sub



Err_Command83_Click:

MsgBox Err.Description

Resume Exit_Command83_Click



End Sub

Sub#2 -- not the BEST method, since the output is not formatted -- this
would need to go into a Table Structure in my WORD template



Private Sub Command88_Click()

With Me.RecordsetClone

..MoveFirst

Me.Bookmark = .Bookmark

Do While Not .EOF

Debug.Print ![Product Type], ![Product Name], !Quantity, ![Price],
![Delivery Cost]

.MoveNext

Loop

End With



End Sub

=========================

Sub#3 -- This sub prints my underlying ACCESS table -- how would I get this
to insert this Table into my WORD Template (DOC_PATH1 & DOC_NAME1)
referenced in my DECLARATIONS?



Private Sub Command87_Click()
On Error GoTo Err_Command87_Click

Dim stDocName As String
Dim MyForm As Form

stDocName = "Order Specifics"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acTable, stDocName, True
DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_Command87_Click:
Exit Sub

Err_Command87_Click:
MsgBox Err.Description
Resume Exit_Command87_Click

End Sub

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

Jean,

Thank you for your assistance on this --

You're correct in that my wording was a bit off in my initial post --
first piece of code should insert some information at a bookmark in
the document created by the second piece of code so that you can
print that second document later, then yes, it can be done.

I'm not sure of the BEST way to handle this issue.

I have a Form with several pages/tabs on it. On one of the
pages/tabs, there is a Sub-Form (which is linked to the Master Form
on OrderID and VersionNumber fields from 2 different tables). There
are several products (recordsets) on the Sub-Form for each
OrderID/VersionNumber combination on the Master Form. I have the
Sub-Form set up as a Continuous Form (so that all the records show).

I use the FormFields method to insert data from my Master From to my
WORD template (and this portion works well), but I can't see this
method working for the Sub-Form (since it involves numerous
recordsets) -- am I correct in this assumption? (See Code Below)

I've tried to modify the following sub (using the RecordSetClone
method in Sub#2: Private Sub Command88_Click() shown below) to
insert the Sub-Form data into a bookmark in my WORD template -- this
prints just fine to the Immediate Window in the VBE, but I'm not sure
what I need to do to get it to insert the data into a Table or
bookmark in the same template that the data from the Master Form is
going to)

Why are you opening the template itself?
Shouldn't you be creating a document form the template?
I would certainly not recommend opening the template itself.

The only way to make sure that the sub-form code inserts information in the
same document that was created by the master form, is to make sure that it
exists first.
This means that you would expect the user to have already created the
document before using the sub-form.

The way I would do it is create a global object:

Private docForm As Word.Document

in the declaration space.

Then, inside the first sub, use something like:

Set docForm = .Documents.Add(DOC_PATH1 & DOC_NAME1)

Now, any of the sub-form sub, as long as they are in the same module as the
master form sub, can use that document object.
But first, use code like:

If docForm Is Nothing Then
MsgBox "You must first use the master form to create a document"
'or, depending on what you are doing, you could create the document
here...
Else
'Do what you have to do here...
End If

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

Why are you opening the template itself?

**** Thanks for spotting this -- you're absolutely right -- I overlooked
changing this to Add (rather than Open)

-- here's another issue -- On my Template - .dot - I use a Header and a
Footer -- when I changed to Set docForm = .Documents.Add(DOC_PATH1 &
DOC_NAME1, , True), I lost the Header/Footer in the Document1 that was
created. How do I ensure that the Header/Footer remain visible from the
Main Template (when a New Document is created)?

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

**** Thanks for spotting this -- you're absolutely right -- I
overlooked changing this to Add (rather than Open)

-- here's another issue -- On my Template - .dot - I use a Header and
a Footer -- when I changed to Set docForm = .Documents.Add(DOC_PATH1 &
DOC_NAME1, , True), I lost the Header/Footer in the Document1 that was
created. How do I ensure that the Header/Footer remain visible from
the Main Template (when a New Document is created)?

There is something else going on here. Just creating a document from a
template will not cause headers/footers from being changed or wiped out
entirely.

Make sure you are only creating a document from a template, that your are
using the right template and that your code does not do anything funky with
the headers/footers.

Try debugging step by step to see when the headers/footers disappear, the
you will know which line of code creates this situation and you will be able
to fix it.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Jean,

Thanks for the "Sanity-Check" -- somehow, the default view for my WORD
application changed from "Print Layout" to "Web Layout" -- that's what
caused my Header/Footer not to show -- is it Friday yet?

While chasing that gremlin, I discovered that when using the FormField
method I wasn't able to print the data -- the data showed in the FormFields,
but when I tried to print, the data disappeared. I decided to use the
BookMark Method (below) -- which NOW prints my Master Form data just fine --
I'm still stuck on getting the data from my Sub-Form to print -- could you
step me through your suggestion (RE: Now, any of the sub-form sub, as long
as they are in the same module as the master form sub, can use that document
object)
====================================================================
Option Compare Database
Option Explicit
Private docForm As Word.Document
============================
'My Print Sub
Private Sub cmdPrintInvoice_Click()
On Error GoTo Err_Handler

Dim strTemplateDoc As String

strTemplateDoc = CurrentFolder() & "Product1.dot"

If Not IsNull(Me!ProductNumber) Then
' ensure record is saved
'RunCommand acCmdSaveRecord

CreateMyInvoice strTemplateDoc
Else
MsgBox "No current record.", vbInformation, "Create Invoice"
End If

Exit_here:
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")", vbExclamation, "Error"
Resume Exit_here

'Jean I need to modify the following code to get it to print in my NEW
document -- could you walk me through the step to modify this portion
please?

On Error GoTo Err_Command83_Click

If docForm Is Nothing Then
MsgBox "You must first use the master form to create a document"

Dim stDocName As String
Dim MyForm As Form

stDocName = "PrintDetails"
Set MyForm = Screen.ActiveForm
DoCmd.SelectObject acForm, stDocName, True

DoCmd.PrintOut
DoCmd.SelectObject acForm, MyForm.Name, False

Exit_Command83_Click:
Exit Sub

Err_Command83_Click:
MsgBox Err.Description
Resume Exit_Command83_Click
End If

Set docForm = Nothing


End Sub
================================
'My Module to Create My Invoice
Sub CreateMyInvoice(strTemplate As String)

' Opens a document in Word and inserts values from
' current record at bookmarks in Word document.
' Accepts: path to Word template file - String

On Error GoTo Err_Handler

Dim objWord As Object
Dim objDoc As Object
Dim frm As Form
Dim strAddress As String

' return reference to form
Set frm = Forms!Addresses

' if Word open return reference to it
' else establish reference to it
On Error Resume Next
Set objWord = GetObject(, "Word.Application")
If Err.Number = 429 Then
Set objWord = CreateObject("Word.Application")
End If

AppActivate "Microsoft Word"
On Error GoTo Err_Handler

' open Word document in maximised window
objWord.Visible = True
Set objDoc = objWord.Documents.Add(strTemplate)
'objWord.WindowState = wdWindowStateMaximize

' insert text at bookmarks, getting values from form
InsertAtBookmarks objWord, objDoc, "CompanyName", frm!CompanyName
'InsertAtBookmarks objWord, objDoc, "Address1", frm!Address1
strAddress = (frm!Address1 + vbCrLf) & (frm!Address2 + vbCrLf) & _
(frm!City + "," + " ") & (frm!Region + vbCrLf) & frm!PostalCode
InsertAtBookmarks objWord, objDoc, "Address1", strAddress
InsertAtBookmarks objWord, objDoc, "SalesRep", frm!SalesRep
InsertAtBookmarks objWord, objDoc, "Term", frm!Term
InsertAtBookmarks objWord, objDoc, "Price", frm!Price
InsertAtBookmarks objWord, objDoc, "DeliverCost", frm!DeliveryCost
Set objDoc = Nothing
Set objWord = Nothing

Exit_here:
On Error GoTo 0
Exit Sub

Err_Handler:
MsgBox Err.Description & " (" & Err.Number & ")"
Resume Exit_here

End Sub

Thank you for all your patience and assistance

Shane

 

[Jean-Guy Marcil]

Doctorjones_md was telling us:
Doctorjones_md nous racontait que :

Jean,

Thanks for the "Sanity-Check" -- somehow, the default view for my WORD
application changed from "Print Layout" to "Web Layout" -- that's what
caused my Header/Footer not to show -- is it Friday yet?

While chasing that gremlin, I discovered that when using the FormField
method I wasn't able to print the data -- the data showed in the
FormFields, but when I tried to print, the data disappeared. I
decided to use the BookMark Method (below) -- which NOW prints my
Master Form data just fine -- I'm still stuck on getting the data
from my Sub-Form to print -- could you step me through your
suggestion (RE: Now, any of the sub-form sub, as long as they are in
the same module as the master form sub, can use that document object)

I cannot really help you because I have no idea what you are really doing.

For any of the sub-forms to use the same document created by the master
form, the master form must first create this document.
I am not sure this is what is happening with your code.

The solution hinges on how this document gets to be created, when it comes
into existence and how long it needs to "live".

If in the sub were you create this document you have at the end:
Set dodFrom = Nothing
then the object will cease to exits an no other subs will be able to refer
to it directly. Each sub will have to somehow find the right opened document
if there are more than one currently open.

So, when I saw that line of code in your Print sub, it just reinforced the
fact that I have no idea what you are doing over all.

You maybe able to fix this yourself by writing down the logic behind the
document creation.
Then implement that logic in your subs and add error checking to make sure
that if users try to do something with a non-existing document that they are
either warned or prevented from even trying in the first place. For example,
you could disable buttons on the sub-forms as a default, and only activate
the buttons that cause the sub-form to interact with the document only when
the master form actually creates the document, if the document needs to be
created from the master form.

There are many ways to handle objects in interacting subs, but basically it
boils down to logic.
Figure out the logic behind the document creation and then I think you will
be able to code what you need, if not, you will be at least in a position to
ask precise questions.

--

Salut!
_______________________________________
Jean-Guy Marcil - Word MVP
(e-mail address removed)
Word MVP site: http://www.word.mvps.org

 

[Doctorjones_md]

Jean,

I really appreciate all your advice and assistance -- you're a true
gentleman and a scholar (in the truest sense) Thanks again you for the
recommendation -- I feel that I've gotten (a bit) off-track splicing snipits
of code together from different sources. I was taking various subs that
worked independently (in their own right) and modifying code to integrate
them into a larger task. I'll spend some time going over the logic-trail in
my subs.

Have a GREAT weekend!
=========================

 

[Albert D. Kallal]

You can't send a form, or report to word.

So, must either do:

a) build the whole thing a an access report, and NOT use word.

b) insert the data using books marks, but that means you CAN NOT use a
report, or form for the source, you MUST WRITE CODE to do this.

So, you CAN NOT send a report, or form to word. You can however send query
data to a word document.

If you don't use any graphics, then you can send that *whole* report as word
document, but it is a all, or nothing proposition (you can NOT send parts of
a form or report to a bookmark as you ask).

So, your best bets here:

1) if you only need to send ONE record (or a ONE record that joins in other
tables), then use the standard word merge, and dump the use of bookmarks
(they are hard to use, and wore, just think what happens when the person
using this system finds out that you have to modify code for each new word
document....that makes you look VERY bad. Can you imagine that for each new
word document you had to go back to Microsoft? So, using bookmarks is a less
then ideal approach because those bookmarks require HARD CODING of your
code...

2) If you need one main record, and need to display "many" child records,
then you either need to use a report, and convert that report to word after
it made.

Or, if you must or have to insert he data direct into word, then you can't
use reports or forms, and must use sql, and code to place the fields into
the document (likely using bookmarks). There is some sample example here
that does this:


There are samples and ideas on how to do this at:

http://homepage.swissonline.ch/cindymeister/MergFram.htm

look on the left side for special merges. The one you want is

Multiple items per condition