Sending Reports Automatically to MS Word

  • Thread starter Thread starter Anthony
  • Start date Start date
A

Anthony

Hi All

I know that it is possible to send reports to Word automatically using the
OutputTo command within a macro.

But...does anyone know if I can give the Word doc an unique name using this
method, preferably by incorporating one or more fields from a table into the
doc name? Is there another way to achieve this objective?

Thanks in advance for any suggestions.

Anthony
 
Anthony,

You can use an expression in the Output File argument of the OutputTo
macro action. For example...
="C:\MyReports\Stats" & [Forms]![MyForm]![MyField] & ".rtf"
 
Hi Steve

Thanks for you response. I created a macro, which included the line of code
you suggested. This macro runs when the Report opens (ie OnOpen). However, I
kept getting an error message (ie "MS Access can't find the form "My Form"
referred to in a macro expression of VB code"). I tried fixing this by
adding a line of code elsewhere so that "My Form" remains open while the
macro runs. This time I received an error message telling me that, "This
action can't be carried out while processing a form or report event".

Any further suggestions???

Steve Schapel said:
Anthony,

You can use an expression in the Output File argument of the OutputTo
macro action. For example...
="C:\MyReports\Stats" & [Forms]![MyForm]![MyField] & ".rtf"

--
Steve Schapel, Microsoft Access MVP

Hi All

I know that it is possible to send reports to Word automatically using the
OutputTo command within a macro.

But...does anyone know if I can give the Word doc an unique name using this
method, preferably by incorporating one or more fields from a table into the
doc name? Is there another way to achieve this objective?

Thanks in advance for any suggestions.

Anthony
Click to expand...
Click to expand...
 
Anthony,

What is your purpose in opening the report? I had assumed you were
triggering the OutputTo from an event on a form. As you discovered, my
suggestion relies, also, on an assumption that the data you want
included in the exported document name is represented in a control or
controls on a form which is open at the time.

If you still can't get it working, maybe it would help if you post back
with some more details, perhaps with examples, of what you want.
 
Hi Steve

My database is set up so that the user enters data in one form (patient
details), then presses a command button, which closes that form and opens
another (ie operation details) into which more data are entered. A command
button on the Operation Details form then closes this form and opens a
report displaying data for a patient's operation. At the moment, I must
press the MS Word icon on the toolbar to export the report to word. I want
this process to occur automatically, so that the report is saved as a word
doc with an unique name.

Can your original suggestion be applied to this case, or is there another
approach that will produce the desired results?

Thanks again
Anthony
 
Anthony,

Yes, assign your macro on the button on the form, not the Open event of
the report.
 
Anthony,

By the way, the information that Albert referred to in his reply is cool!
 
Hi Albert

Your word merge example is very nifty. I tried incorporating it (and the
code) into my database but encountered the following problems:

1. I get an error (ie "Compile Error: Sub or function not defined") when I
click on the
MergeSingleWord command button.

2. I get the same error message when I try to open the GuiWordTemplate
directly.

On your web site, you mention that the Microsoft DAO 3.6 Object Library in
the references window must be set. Could this be the cause of these problems
and, if so, how do I set this object library.

Thanks in advance
Anthony
 
Hi again

I managed to figure out how to set the Microsoft DAO 3.6 Object Library, but
this did not help solve the problems with undefined subs/functions. Any
suggestions?

Anthony
 
Yeay!!!!

Albert, I figured out how to get your code/forms working (almost
perfectly!). After a couple of hours trying to figure out what the problem
was, I finally realised that I had forgotten to include the modules.

The only problem I have now is that the merge produces a document containing
letters for all records. Any clues as to how I can fix this?

When moving between forms, I have been using the following line
stLinkCriteria in the "Open Form" command to get to the record for a
particular patient:

stLinkCriteria = "[PatientID]=" & Me![PatientID]

I tried incorporating this into your module that opens the "GuiWordTemplate"
form, but it seemed to choke on "Me". Any suggestions?

Many thanks
Anthony
 
Great stuff here!
When moving between forms, I have been using the following line
stLinkCriteria in the "Open Form" command to get to the record for a
particular patient:

stLinkCriteria = "[PatientID]=" & Me![PatientID]

I tried incorporating this into your module that opens the "GuiWordTemplate"
form, but it seemed to choke on "Me". Any suggestions?
Click to expand...

Hum, should be able to use MergeSingeWord when talking about a regular bound
form. My code is supposed to only merge ONE record, the one you are looking
at.

That single command should just give you ONE merge record.

MergeSingleWord

If the above command is merging all records, it is possible the data source
for the template got mixed up. Try hitting the modify template button, and
then save the template. Now try the merge. I should give just the one
record. If you copy templates into the diredclity, remmber to hit the
"modify" buttion, as this will fix the data souce for the reocrds.
 
Hi Albert


My database is set up so that the user enters data into one form (i.e.,
Patient Details), then clicks a command button to enter data into another
form (i.e., Operation Details). Your command button is placed on the second
form, "Operation Details".

I have noticed that when I click the modify button, the data source for the
template reverts back to the underlying table of the active form (i.e., the
underlying table for Operation Details form). However, the data source
(i.e., the query on which my report is based) remains stable if I don't
modify the table.

Could the set up of my database cause the problem with merging, sending all
records to Word instead of just one???

Anthony


Albert D. Kallal said:
Great stuff here!
When moving between forms, I have been using the following line
stLinkCriteria in the "Open Form" command to get to the record for a
particular patient:

stLinkCriteria = "[PatientID]=" & Me![PatientID]

I tried incorporating this into your module that opens the "GuiWordTemplate"
form, but it seemed to choke on "Me". Any suggestions?
Click to expand...

Hum, should be able to use MergeSingeWord when talking about a regular bound
form. My code is supposed to only merge ONE record, the one you are looking
at.

That single command should just give you ONE merge record.

MergeSingleWord

If the above command is merging all records, it is possible the data source
for the template got mixed up. Try hitting the modify template button, and
then save the template. Now try the merge. I should give just the one
record. If you copy templates into the diredclity, remmber to hit the
"modify" buttion, as this will fix the data souce for the reocrds.


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

I have added some code to the MergeSingleWord command button code so that a
form is opened (at the appropriate record, of course!) which has my query
(i.e., the query upon which my report is based) as its base. This was
inserted before the "MergeSingleWord" command so that it becomes the active
form when the MergeSingleWord module is run. This solved the problem with
the resetting data source in my Word template.

I still haven't solved the problem with the multiple records being merged to
word when I only want to send one record, so any suggestions would be most
welcome.

Anthony


Anthony said:
Hi Albert


My database is set up so that the user enters data into one form (i.e.,
Patient Details), then clicks a command button to enter data into another
form (i.e., Operation Details). Your command button is placed on the second
form, "Operation Details".

I have noticed that when I click the modify button, the data source for the
template reverts back to the underlying table of the active form (i.e., the
underlying table for Operation Details form). However, the data source
(i.e., the query on which my report is based) remains stable if I don't
modify the table.

Could the set up of my database cause the problem with merging, sending all
records to Word instead of just one???

Anthony


Albert D. Kallal said:
Great stuff here!
When moving between forms, I have been using the following line
stLinkCriteria in the "Open Form" command to get to the record for a
particular patient:

stLinkCriteria = "[PatientID]=" & Me![PatientID]

I tried incorporating this into your module that opens the "GuiWordTemplate"
form, but it seemed to choke on "Me". Any suggestions?
Click to expand...

Hum, should be able to use MergeSingeWord when talking about a regular bound
form. My code is supposed to only merge ONE record, the one you are looking
at.

That single command should just give you ONE merge record.

MergeSingleWord

If the above command is merging all records, it is possible the data source
for the template got mixed up. Try hitting the modify template button, and
then save the template. Now try the merge. I should give just the one
record. If you copy templates into the diredclity, remmber to hit the
"modify" buttion, as this will fix the data souce for the reocrds.


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


My database is set up so that the user enters data into one form (i.e.,
Patient Details), then clicks a command button to enter data into another
form (i.e., Operation Details). Your command button is placed on the second
form, "Operation Details".
Click to expand...

Ah, yes. Which ever form you place the button on, it will use that data
source. If you need more control, or don't want everything to be auto
selected, then do NOT use MergeSingleWord.

MergeSingleWord is great for "most" forms, and works well. However, when the
requirements start become more "custom", then the easy go luckily one line
of code button is NOT the best bet. Note also that MergeSingleWord takes the
active screen form, and thus can't be used for sub-forms either.

The solution when YOU NEED control over the records and the data source for
the merge is use MergeAllWord.

So, in your case, just set whatever sql you want to use.

dim strSql as string

strSql = "select * from tblCustomer where City = 'Edmonton'"

MergeAllWord (strSql)

In your case, if you want the one record, then use:

strSql = "select * from tblCustomer where id = " & me!CustId
MergeAllWord(strSql)

Remember, if you do use the above, make sure the FIRST TIME, you edit the
template, as the data source will need to be re-set to whatever sql you
used.
 
Anthony said:
Update!

I have added some code to the MergeSingleWord command button code so that a
form is opened (at the appropriate record, of course!) which has my query
(i.e., the query upon which my report is based) as its base. This was
inserted before the "MergeSingleWord" command so that it becomes the active
form when the MergeSingleWord module is run. This solved the problem with
the resetting data source in my Word template.
Click to expand...

See my other post. If you need to contorl the data souce, then don't use
MergeSingleWord, but use MergeAllWord.
 
Back
Top