Solved

Merge Word 97 Document Into MS Access 97 Report

Posted on 2000-03-22
14
253 Views
Last Modified: 2010-08-05
I have a MS Access 97 report of client invoices.  Each different client invoices can be more than one page in length.  I need to insert a one-page Word 97 document, preferably at the beginning of each separate client, that will display the name of the individual client on to the Word 97 document.  I know it is possible because an ex-employee did it.  This report is only done once a year.
0
Comment
Question by:DrRichard
  • 8
  • 5
14 Comments
 
LVL 7

Expert Comment

by:Believer
ID: 2647516
Since nobody else has chimed in yet, I'll give it a shot...
I don't know how to merge Word and Access reports in the way you describe.  Here's how you can get the same effect, however:
Group your invoice report by client (if it's not already) and turn on the group header.  Type the contents of the "Word" document in the header, then put a page break control after the "Word" document but still in the group header.
If you've already got the group header, insert the "Word" document *above* the existing header information, followed by the page break, then the existing group header content.
0
 
LVL 2

Expert Comment

by:repstein
ID: 2647803
I'm not clear exactly what you are trying to do.

1. Do you want to take a static word document, insert the clients name into a field on the document, then, print the modified word document just before your Access report is printed?

2. Or do you want to do something else?


If you want to do 1, the basic strategy I suggest is:

1. Save your word document as a templated
2. Open a new word document based on the template from access
3. Add the employee name to the word document
4. Print the word document
5. Print the access report

Richard
0
 
LVL 2

Expert Comment

by:repstein
ID: 2647805
Or, you could contact your former employee and ask (pay) him to tell you how he dit it :->

Richard
0
 
LVL 7

Expert Comment

by:Believer
ID: 2649506
repstien: I have a feeling that you solution won't work too well when printing a batch of invoices.  All the Word docs would get printed followed by all the Access reports.  Then they'd have to manually collate everything.  If I'm right (which is no guarantee! lol), DrRichard would rather have everything come out a bit more organized...
IMHO anyway!
0
 
LVL 2

Expert Comment

by:repstein
ID: 2651021
Believer: It is possible for the printed output to be collated properly. Just depends on how you setup the report routine.

But since I'm still not exactly sure what DrR wants to do, perhaps we should just wait...

0
 

Author Comment

by:DrRichard
ID: 2651243
Thanks for your inputs.  What I am looking to do is to print a Word document with the client's name merged in, then print the Access report for this client, print the Word document with the next client's name merged into it, print this client's Access report, etc.....  Remember, the client's Access report can be more than one page long.  I should have one stack of papers to pick up from the printer.  I know it can be done.  I have until end of April to get this out to our clients.  If not then I believe the best thing to do is to print each one out separately.  A list of the client's name can be extracted from the Access database and mail merged into the Word document.  Then it can be collated by hand as long as the ordering by the client's name is maintained.
0
 
LVL 7

Expert Comment

by:Believer
ID: 2651324
Why does the client cover letter have to be in Word?
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 2

Expert Comment

by:repstein
ID: 2652001
I am going to assume that you know how to print a report for a selected client from a recordset. The details in the code below are sketchy.

Lets say you have the following:
Table: ClientNames with client name in the Field called  Name
Report: InvoiceReport
Query: QueryClientNames that picks the customers whose invoices you want to print


1. Open the Word Document you want to use
2. Go to the part in the document where you want to insert the Client's name and create a bookmark named "ClientName"
3. Save the document as a template and call it InvoiceHeader.dot
Save it in the default location


In your access database, you are going to establish a recordset from the ClientNames table, lets call it rstClientNames



Create the following function, which will be used to print the Word pages and the Access Report


Private Sub PrintInvoices()
Dim objWord as Object
Dim dbs as Database
Dim rstClientNames as Recordset
Dim sInvoiceNumber as string

set dbs = CurrentDb

' you may want to base the rstClientNames recordset on a query, just insert the query name in place of "Client Names"
set rstClientNames =dbs.OpenRecordset("QueryClientNames",dbOpenSnapshot)



On Error Resume Next
' Activate Word, if its not running, then an error will be generated and trapped, and word will load
' if you have installed Word in a different location, you need to change the shell line accordingly

' Cycle through this code for all the customers selected from your invoice database
rstClientNames.MoveFirst

While not rstClientNames.eof

AppActivate "Microsoft Word"
If Err then
' if you have installed Word in a different location, you need to change the shell line accordingly
  Shell "c:\msoffice\windowrd\word"
endif
'cancel error trapping
On Error GoTo 0


set objWord = CreateObject("Word.Basic")
with objWord
' change the FileNew line to reflect the location of your template files if is different from what I have written
' This line opens a document based on the template
  .FileNew "C:\Program Files\Microsoft Office\Templates\InvoiceHeader"

'Navigate to the BookMark called ClientName in the document where you want insert the name
 .EditGoTo "ClientName"

' Insert the name from the RecordSet
 .Insert rstClientNames!Name
' Print the file to your default printer without prompts
 .objWord.FilePrint
' close the file without prompts to save
 .objWord.FileClose 2


' Here you'll need a method of just selecting the Invoice report you want. For example, if sInvoiceNumber is the string value that is the correct invoice to print, you'll need to assign it accordingly

DoCmd.OpenReport "InvoiceReport",acViewNormal,,"[InvoiceNumber] = '" & sInvoiceNumber & "'"
DoCmd.Close
 
Wend
End Sub


If you need to do more from Access with Word than I've described here, I highly recommend looking as the MS Word Developer's Kit. It has the syntax for all the commands that you can use from Access (or any other Ofice application) If you want to insert address information or other simple stuff, you can just define several other bookmarks in your template and repeat the .EditGoTo "Bookmark Name" followed by .Insert


Hope this helps.


Richard

PS To Believer:
My guess is that the cover letter doesn't *HAVE* to be in Word, but this is what DrRichard wants to do. I assume that either there are some special formatting features that are on the client cover letter, or he just doesn't want to be bothered converting it to an Access Report.
0
 
LVL 2

Expert Comment

by:repstein
ID: 2652005
There should be a .MoveNext just before Wend.

Hope this didn't waste too much of your paper :->

Richard
0
 
LVL 7

Expert Comment

by:Believer
ID: 2701431
Doc: Was looking thru old open questions and wondered wassup with this one...
0
 
LVL 2

Expert Comment

by:repstein
ID: 2733629
DrRichard:

Did you ever get this problem resolved?

Richard
0
 
LVL 2

Expert Comment

by:repstein
ID: 2739844
To Believer:

   What's protocol here when someone gets a usable solution and goes mute? Should I repost my comment as an answer or what?

   BTW, good luck on your new endeavor!

Richard
0
 
LVL 7

Expert Comment

by:Believer
ID: 2740178
repstein: Thanks.
Don't know about "protocol."  No matter what we do here, the e-mail notifications still get sent to the questioner.  It's entirely up to them to check e-mail and respond accordingly.  Could try asking EE Help, I guess.
0
 
LVL 2

Accepted Solution

by:
repstein earned 65 total points
ID: 2740196
To DrRichard:

I'm reposting my previous comment as an answer. You should either accept it or reject it so that this question can either be cleared out, or additional comments received. If you want to withdraw the question (not really recommended), you also have this option.

Thanks

Richard


I am going to assume that you know how to print a report for a selected client from a recordset. The details in the code below are sketchy.

Lets say you have the following:
Table: ClientNames with client name in the Field called  Name
Report: InvoiceReport
Query: QueryClientNames that picks the customers whose invoices you want to print


1. Open the Word Document you want to use
2. Go to the part in the document where you want to insert the Client's name and create a bookmark named "ClientName"
3. Save the document as a template and call it InvoiceHeader.dot
Save it in the default location


In your access database, you are going to establish a recordset from the ClientNames table, lets call it rstClientNames



Create the following function, which will be used to print the Word pages and the Access Report


Private Sub PrintInvoices()
Dim objWord as Object
Dim dbs as Database
Dim rstClientNames as Recordset
Dim sInvoiceNumber as string

set dbs = CurrentDb

' you may want to base the rstClientNames recordset on a query, just insert the query name in place of "Client Names"
set rstClientNames =dbs.OpenRecordset("QueryClientNames",dbOpenSnapshot)



On Error Resume Next
' Activate Word, if its not running, then an error will be generated and trapped, and word will load
' if you have installed Word in a different location, you need to change the shell line accordingly

' Cycle through this code for all the customers selected from your invoice database
rstClientNames.MoveFirst

While not rstClientNames.eof

AppActivate "Microsoft Word"
If Err then
' if you have installed Word in a different location, you need to change the shell line accordingly
  Shell "c:\msoffice\windowrd\word"
endif
'cancel error trapping
On Error GoTo 0


set objWord = CreateObject("Word.Basic")
with objWord
' change the FileNew line to reflect the location of your template files if is different from what I have written
' This line opens a document based on the template
  .FileNew "C:\Program Files\Microsoft Office\Templates\InvoiceHeader"

'Navigate to the BookMark called ClientName in the document where you want insert the name
 .EditGoTo "ClientName"

' Insert the name from the RecordSet
 .Insert rstClientNames!Name
' Print the file to your default printer without prompts
 .objWord.FilePrint
' close the file without prompts to save
 .objWord.FileClose 2


' Here you'll need a method of just selecting the Invoice report you want. For example, if sInvoiceNumber is the string value that is the correct invoice to print, you'll need to assign it accordingly

DoCmd.OpenReport "InvoiceReport",acViewNormal,,"[InvoiceNumber] = '" & sInvoiceNumber & "'"
DoCmd.Close
   
Wend
End Sub


If you need to do more from Access with Word than I've described here, I highly recommend looking as the MS Word Developer's Kit. It has the syntax for all the commands that you can use from Access (or any other Ofice application) If you want to insert address information or other simple stuff, you can just define several other bookmarks in your template and repeat the .EditGoTo "Bookmark Name" followed by .Insert


Hope this helps.


Richard

0

Featured Post

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
QuickBooks® has a great invoice interface that we were happy with for a while but that changed in 2001 through no fault of Intuit®. Our industry's unit names are dictated by RUS: the Rural Utilities Services division of USDA. Contracts contain un…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now