 |
● Merge To Word Version: 97 (8.0)
Posted on 12/29/04 12:24 PM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
I have created a word template (\\rdc1\Surety\Surety Reviews\SuretyReview.dot) and added all the required bookmarks.
I want to merge only the records that are found using the following criteria. "SELECT * FROM qryInspectionReport WHERE InspNumber = [Forms]![frmInspectionReportMenu]![txtIDNumber]"
I have placed some bookmarks in the Page Header of my Word Template (as I need these to print on each page) including:
Word Bookmark -- Access Field Name
InspNum -- InspNumber
Agency -- Agency
IStartDate -- ISDate
IEndDate -- IEDate
ITitle -- InspectionTitle
In the "Body" of the Word Document I have other bookmarks:
FNumber -- FindingNum
FDate -- FDate
Reviewer -- Reviewer
ControlNum -- ExternalControlNumber
and a few others.
Each record that is found matching the criteria needs to generate a new page in the Word Template (not a new document)...so if I have 10 records matching my criteria I will end up with at least 10 pages in my Word Document.
The information in the header (the one side of the relationship) will always be the same with the information in the "Body" (the many side of the relationship) changing.
I have an example of doing a merge with a word document, but it is more like a letter...not having to loop through a bunch of records.
How would I go about doing this?
Thanks!
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
 |
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 01:05 PM
Posted by Doris - New Member
Posts: 4 -
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
|
Someone please help...I would like to know how to do this as well.....
|
|
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 02:28 PM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
So far I have this:
Dim WordObj As Word.Application
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim strSQL As String
Set WordObj = CreateObject("Word.Application")
Set db = CurrentDb
strSQL = "SELECT * FROM qryInspectionReportAll WHERE [InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]"
With WordObj
.Visible = True
.Documents.Add ("\\rdc1\surety\surety reviews\SuretyReview.dot")
.ActiveDocument.Bookmarks("InspNum").Range.InsertAfter DLookup("[InspNumber]", "qryInspectionReportHeaderAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
.ActiveDocument.Bookmarks("Agency").Range.InsertAfter DLookup("[Agency]", "qryInspectionReportHeaderAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
.ActiveDocument.Bookmarks("IStartDate").Range.InsertAfter DLookup("[ISDate]", "qryInspectionReportHeaderAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
'.ActiveDocument.Bookmarks("IEndDate").Range.InsertAfter DLookup("[IEDate]", "qryInspectionReportHeaderAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
.ActiveDocument.Bookmarks("ITitle").Range.InsertAfter DLookup("[InspectionTitle]", "qryInspectionReportHeaderAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
Set rs = db.OpenRecordset(strSQL)
Do While Not rs.EOF
.ActiveDocument.Bookmarks("FNumber").Range.InsertAfter DLookup("[FindingNum]", "qryInspectionReportAll", "[InspNumber] = [Forms]![frmInspectionReportMenu]![txtIDNumber]")
rs.MoveNext
Loop
End With
rs.Close
Set rs = Nothing
I have the one line commented out because I get an invalid use of null error. I am not sure how to get past that...sometimes there won't be a value.
I get the following error when trying to run the above code:
Run-time error'3061':
Too few parameters. Expected 1.
Clicking on Debug highlights:
Set rs = db.OpenRecordset(strSQL)
Not sure where I am going wrong.
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
|
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 02:56 PM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
Fixed that problem...now how to I get it to create a new page when I come to a new record?
Thanks,
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
|
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 03:13 PM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
This method isn't going to work for me....must rethink the whole idea.
Thanks!
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
 |
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 04:51 PM
Posted by NoahP - UA Editor + Utterly Certified
Posts: 10404 - Loc: Lexington/Louisville KY USA
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
Lena,
I use Mail Merge for this. I think I have a couple of examples already posted on the subject, but, if you can't find one, post back and I'll put a demo together. The hardest part with the way I do it is setting up the word document. I use a temp table (one of the only times I'll use one is for a mail merge as I've had issues where Word errored out on queries) that is created by a make table query. The Word doc is set up and linked to the temp table. Once that's done it only takes about 6-8 lines of code to open a Word doc and merge it. It will automatically give you a new 'page' with each record.
HTH
Noah
--------------------
For normalization links look here.
The floggings will continue until morale improves...
|
|
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/29/04 04:58 PM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
I have been searching all kinds of sites all day trying to find a way to do what I need to do. People in my office here (who know nothing about making databases) have made several suggestions including:
Create a single document that contains all the findings
Create a single document for each finding but save them in a folder using the Inspection Number and Inspection Title (of course they want the folder creation to be automated for them).
I honestly can't imagine they will need to send out whole inspection reports but more likely to need to send out individual findings from those inspections...but apparently they know what they need (or at least I let them think they do).
If you could point me to a demo that would show me how to do what I am wanting to do I would really appreciate it. I have come a long ways since I started building these databases. Seems the more I learn they more there is to learn....I will never be ahead of the game haha.
Thanks again for your help. If you need anything further from me, please let me know.
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
|
Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 12/30/04 09:27 AM
Posted by NoahP - UA Editor + Utterly Certified
Posts: 10404 - Loc: Lexington/Louisville KY USA
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
See if the attached demo helps. You will have to do a couple of things to get it to work properly:
1) Unzip the db and the Word document.
2) Open the Word doc and link it to tblMailMerge in the database. Make sure you check the 'Select Method' box and choose ODBC as the method. Otherwise, you may get multiple instances of Access opened up during the mail merge.
3) In the code for the mail merge form, change strPathtoYourDocument to the actually path to the Word doc.
The code I provided does require that the Microsoft Word library reference be active.
HTH
Noah
--------------------
For normalization links look here.
The floggings will continue until morale improves...
|
|
● Re: Merge To Word Version: 97 (8.0) [Re: LenaWood]
Posted on 01/03/05 11:43 AM
Posted by LenaWood - Utter Access Ruler
Posts: 1283 - Loc: Hermiston, OR - Umatilla Chemical Agent Disposal F
Forum: Access Reports
|
• Edit
• Reply
• Quote
• Quick Reply
• Print this post
• Bookmark Post
• Notify Moderator
• Top of page
|
Thank you...I will play with this and see what I can figure out.
Lena
--------------------
Proud Grandma to Ronan Daniel 05/18/08
|
 |
| Page Jump |
|
Pages: 1
|
|
|
|
| Navigate |
|
|
|
| Page Jump |
|
Pages: 1
|
|
