Merged with Word Report

[Joan]

Hi,

I created a query to build a report from called "CompletePedigreeQuery",
however I built the report as a Word template. A sub procedure declares a
DAO.Recordset and sets it to the QueryDef of "CompletePedigreeQuery".
Essentially, I merged the data in the recordset with the Word template's
bookmarks.

When I attempt to print a single record's report my code works great.
However, I would like to print reports for a range of records and don't know
how to go about this. Currently I have a form with a textbox where the user
enters a Dog Number and this Dog Number is passed to the sub-procedure,
PedigreeMergetoWord, when it is called. How can I pass a range of Dog
Numbers so that my code will print reports for each Dog Number in the range?
Any advice on how to go about this will be most appreciated.

Joan



Below is a portion of my code:

Private Sub cboDogNumbers_Click()
Dim DogNum As Long

DogNum = Me.DogNumber2
Call PedigreeMergetoWord(DogNum)
End Sub


Private Sub PedigreeMergetoWord(DogNumb As Long)
'This module creates a new document, the Pedigree Document in MS Word 2000
using Automation.

Dim dbs As Database
Dim rsLitter As DAO.Recordset
Dim WordObj As Word.Application
Dim qryPedigree As QueryDef
Dim DocObj As Object

Set dbs = CurrentDb()
'Referencing the query
Set qryPedigree = dbs.QueryDefs("CompletePedigreeQuery")
qryPedigree.Parameters("PDN") = DogNumb
Set rsLitter = qryPedigree.OpenRecordset()

Set WordObj = GetObject("", "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True
Set DocObj = WordObj.Documents.Add("C:\Templates\PEDIGREETREE1.dot")

InsertTextAtBookmark WordObj, DocObj, "Litter_Reg_Number",
rsLitter("[Litter Reg Number]")
InsertTextAtBookmark WordObj, DocObj, "Reg_Suffix", rsLitter("[Reg
Suffix]")
. . . . .
(inserts many data items similar to the two lines above)

WordObj.Activate
Set WordObj = Nothing
Set rsLitter = Nothing
Set DocObj = Nothing
End Sub

Private Sub InsertTextAtBookmark(objW As Object, objD As Object, strBkmk As
String, varText As Variant)
'select the required bookmark, and set the selection text

objD.Bookmarks(strBkmk).Select
objW.Selection.Text = Nz(varText, "")
End Sub

 

[Joan]

Kindly disregard my earlier post. I figured out how to print a range of
records by doing a For/Next loop and incorporating the Call statement within
the loop. I guess I just needed to think it through logically first.

Joan

 

[Albert D. Kallal]

The problem is using book marks. They really are horrible. You will have to
"loop" for each merge, and that really is not required.

I would suggest that you dump the book marks, and use actually word merge
fields. You will find this much better.

Try downloading my word merge example. It has provisions for you to send the
word merge a sql string. (so, likely, you might not even have to bother with
the record set)

So, for example to merge all the dog numbers from 22 to 34, you could use:

MergeAllWord ("select * from CompletePedQry where dognum between 22 and 34")

In addition, you can word enable any of your forms to merge ONE RECORD to a
word template with ONE line of code!

Check out:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

Hi,

I created a query to build a report from called "CompletePedigreeQuery",
however I built the report as a Word template. A sub procedure declares a
DAO.Recordset and sets it to the QueryDef of "CompletePedigreeQuery".
Essentially, I merged the data in the recordset with the Word template's
bookmarks.

When I attempt to print a single record's report my code works great.
However, I would like to print reports for a range of records and don't know
how to go about this. Currently I have a form with a textbox where the user
enters a Dog Number and this Dog Number is passed to the sub-procedure,
PedigreeMergetoWord, when it is called. How can I pass a range of Dog
Numbers so that my code will print reports for each Dog Number in the range?
Any advice on how to go about this will be most appreciated.

Joan



Below is a portion of my code:

Private Sub cboDogNumbers_Click()
Dim DogNum As Long

DogNum = Me.DogNumber2
Call PedigreeMergetoWord(DogNum)
End Sub


Private Sub PedigreeMergetoWord(DogNumb As Long)
'This module creates a new document, the Pedigree Document in MS Word 2000
using Automation.

Dim dbs As Database
Dim rsLitter As DAO.Recordset
Dim WordObj As Word.Application
Dim qryPedigree As QueryDef
Dim DocObj As Object

Set dbs = CurrentDb()
'Referencing the query
Set qryPedigree = dbs.QueryDefs("CompletePedigreeQuery")
qryPedigree.Parameters("PDN") = DogNumb
Set rsLitter = qryPedigree.OpenRecordset()

Set WordObj = GetObject("", "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True
Set DocObj = WordObj.Documents.Add("C:\Templates\PEDIGREETREE1.dot")

InsertTextAtBookmark WordObj, DocObj, "Litter_Reg_Number",
rsLitter("[Litter Reg Number]")
InsertTextAtBookmark WordObj, DocObj, "Reg_Suffix", rsLitter("[Reg
Suffix]")
. . . . .
(inserts many data items similar to the two lines above)

WordObj.Activate
Set WordObj = Nothing
Set rsLitter = Nothing
Set DocObj = Nothing
End Sub

Private Sub InsertTextAtBookmark(objW As Object, objD As Object, strBkmk As
String, varText As Variant)
'select the required bookmark, and set the selection text

objD.Bookmarks(strBkmk).Select
objW.Selection.Text = Nz(varText, "")
End Sub

 

[Joan]

Thanks for your reply Albert. I will try this but will have to learn how to
use merge word fields first. I may ask you more questions later after I
have time to research and experiment with this.
Thanks,
Joan

The problem is using book marks. They really are horrible. You will have to
"loop" for each merge, and that really is not required.

I would suggest that you dump the book marks, and use actually word merge
fields. You will find this much better.

Try downloading my word merge example. It has provisions for you to send the
word merge a sql string. (so, likely, you might not even have to bother with
the record set)

So, for example to merge all the dog numbers from 22 to 34, you could use:

MergeAllWord ("select * from CompletePedQry where dognum between 22 and 34")

In addition, you can word enable any of your forms to merge ONE RECORD to a
word template with ONE line of code!

Check out:
http://www.attcanada.net/~kallal.msn/msaccess/msaccess.html

--
Albert D. Kallal (MVP)
Edmonton, Alberta Canada
(e-mail address removed)
http://www.attcanada.net/~kallal.msn

Hi,

I created a query to build a report from called "CompletePedigreeQuery",
however I built the report as a Word template. A sub procedure

declares

a
DAO.Recordset and sets it to the QueryDef of "CompletePedigreeQuery".
Essentially, I merged the data in the recordset with the Word template's
bookmarks.

When I attempt to print a single record's report my code works great.
However, I would like to print reports for a range of records and don't know
how to go about this. Currently I have a form with a textbox where the user
enters a Dog Number and this Dog Number is passed to the sub-procedure,
PedigreeMergetoWord, when it is called. How can I pass a range of Dog
Numbers so that my code will print reports for each Dog Number in the range?
Any advice on how to go about this will be most appreciated.

Joan



Below is a portion of my code:

Private Sub cboDogNumbers_Click()
Dim DogNum As Long

DogNum = Me.DogNumber2
Call PedigreeMergetoWord(DogNum)
End Sub


Private Sub PedigreeMergetoWord(DogNumb As Long)
'This module creates a new document, the Pedigree Document in MS Word 2000
using Automation.

Dim dbs As Database
Dim rsLitter As DAO.Recordset
Dim WordObj As Word.Application
Dim qryPedigree As QueryDef
Dim DocObj As Object

Set dbs = CurrentDb()
'Referencing the query
Set qryPedigree = dbs.QueryDefs("CompletePedigreeQuery")
qryPedigree.Parameters("PDN") = DogNumb
Set rsLitter = qryPedigree.OpenRecordset()

Set WordObj = GetObject("", "Word.Application")
If Err.Number <> 0 Then
Set WordObj = CreateObject("Word.Application")
End If

WordObj.Visible = True
Set DocObj = WordObj.Documents.Add("C:\Templates\PEDIGREETREE1.dot")

InsertTextAtBookmark WordObj, DocObj, "Litter_Reg_Number",
rsLitter("[Litter Reg Number]")
InsertTextAtBookmark WordObj, DocObj, "Reg_Suffix", rsLitter("[Reg
Suffix]")
. . . . .
(inserts many data items similar to the two lines above)

WordObj.Activate
Set WordObj = Nothing
Set rsLitter = Nothing
Set DocObj = Nothing
End Sub

Private Sub InsertTextAtBookmark(objW As Object, objD As Object, strBkmk As
String, varText As Variant)
'select the required bookmark, and set the selection text

objD.Bookmarks(strBkmk).Select
objW.Selection.Text = Nz(varText, "")
End Sub