Report creation opinion

[timmg]

I have an application were users evaluate records and based on the
amount of money involved answer between 17 and a 120 questions on one
of three response forms. I have a master list of question in one
table and another that links the subset of questions to the particular
form. When the user gets ready to answer the questions the app
appends the questions to the answer table - all of which makes it easy
to answer the questions within a subform for the record they're
reviewing.

So far, I think, so good.

Now the output can go either to an Excel spreadsheet or Word doc - one
record per file, which then gets printed and mailed to the next step
in the business process.

Here's where I'd love your opinions: One option is I can concatenate
the responses into a merge file (likely CSV, but could be anything,
and execute the merge from the Word doc. I can shell out to Word or
just execute from within Access. I'm not thrilled with the Excel
idea, but some users have made SSs that look like the Word docs - I
only mention it in case someone says "well, it'd be really easy if you
were moving the answers to Excel."

Another is opening a Doc template from within Access and inserting the
data via bookmarks.

In either case the resulting document needs to be saved with the
record number as the file name.

Which would you say is the better path? Is there an easier way? The
Access report to RTF option probably isn't robust enough given the
formatting required, but I might be able to make it work if the
consensus is that that's the way to go.

Thanks for your time

Tim Mills-Groninger

[Allen Browne]

What's the problem with exporting to RTF? I would have thought that was more
robust than trying to manipulate Word or Excel from Access.

You can certainly use TransferText to create a CSV if that helps.

For exporting to Word, Albert Kallal's 'super easy word merge' might help:
http://www.members.shaw.ca/AlbertKal.../msaccess.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"timmg" <tm************ *@gmail.comwrot e in
messagenews:f6* *************** *************** ***@w7g2000hsa. googlegroups.co m...

>I have an application were users evaluate records and based on the
amount of money involved answer between 17 and a 120 questions on one
of three response forms. I have a master list of question in one
table and another that links the subset of questions to the particular
form. When the user gets ready to answer the questions the app
appends the questions to the answer table - all of which makes it easy
to answer the questions within a subform for the record they're
reviewing.

So far, I think, so good.

Now the output can go either to an Excel spreadsheet or Word doc - one
record per file, which then gets printed and mailed to the next step
in the business process.

Here's where I'd love your opinions: One option is I can concatenate
the responses into a merge file (likely CSV, but could be anything,
and execute the merge from the Word doc. I can shell out to Word or
just execute from within Access. I'm not thrilled with the Excel
idea, but some users have made SSs that look like the Word docs - I
only mention it in case someone says "well, it'd be really easy if you
were moving the answers to Excel."

Another is opening a Doc template from within Access and inserting the
data via bookmarks.

In either case the resulting document needs to be saved with the
record number as the file name.

Which would you say is the better path? Is there an easier way? The
Access report to RTF option probably isn't robust enough given the
formatting required, but I might be able to make it work if the
consensus is that that's the way to go.

Thanks for your time

Tim Mills-Groninger

[Mike P]

On 10 Jul, 19:36, timmg <tmillsgronin.. [e-mail] e:

I have an application were users evaluate records and based on the
amount of money involved answer between 17 and a 120 questions on one
of three response forms. I have a master list of question in one
table and another that links the subset of questions to the particular
form. When the user gets ready to answer the questions the app
appends the questions to the answer table - all of which makes it easy
to answer the questions within a subform for the record they're
reviewing.

So far, I think, so good.

Now the output can go either to an Excel spreadsheet or Word doc - one
record per file, which then gets printed and mailed to the next step
in the business process.

Here's where I'd love your opinions: One option is I can concatenate
the responses into a merge file (likely CSV, but could be anything,
and execute the merge from the Word doc. I can shell out to Word or
just execute from within Access. I'm not thrilled with the Excel
idea, but some users have made SSs that look like the Word docs - I
only mention it in case someone says "well, it'd be really easy if you
were moving the answers to Excel."

Another is opening a Doc template from within Access and inserting the
data via bookmarks.

In either case the resulting document needs to be saved with the
record number as the file name.

Which would you say is the better path? Is there an easier way? The
Access report to RTF option probably isn't robust enough given the
formatting required, but I might be able to make it work if the
consensus is that that's the way to go.

Thanks for your time

Tim Mills-Groninger

Have you tried the 'Access report to snapshot' option? The snapshot
output format retains all of the formatting of the originating report.
The snapshot viewer is installed with Access 2000 when the snapshot
file is created. It is available as a free of charge download from the
MS website for those who do not have Access.

If that is not to your liking, initiating a mail merge from within
Access is feasible if all the merged data are plain text. Remember
that this inserts a section break after each merged record; you
probably will need continuous sections rather than new page sections.

If you wish to insert a Word table, for example, then try the
'Insertdatabase ' method of the Word object to insert an Access table
directly at the appropriate bookmark in the Word document. The
Insertdatabase method can be problematic if the datasource is in a
secured database. As an alternative, try the 'Insertfile' method using
an exported RTF file as the data source.

You can use a combination of insert methods and mail merge but I don't
think that bookmarks are retained in the ouput Word document so make
inserts at bookmarked locations before running a mail merge.
Mike P

[timmg]

On Jul 10, 10:04*pm, "Allen Browne" <AllenBro...@Se eSig.Invalid>
wrote:

What's the problem with exporting to RTF? I would have thought that was more
robust than trying to manipulate Word or Excel from Access.

The forms have a bunch of line segments and a few other minor
formatting concerns. However, I will give it another look - although
building all of the text required into the report will be a pain.

But then, no pain, no gain - or that concept too "Twen Cen/ Jane
Fonda?" <grin>

>
You can certainly use TransferText to create a CSV if that helps.

Good point.

>
For exporting to Word, Albert Kallal's 'super easy word merge' might help:
* *http://www.members.shaw.ca/AlbertKal.../msaccess.html

yes, I've used these tools with other project. Whey Kewl.

Thanks,

Tim

--
Allen Browne - Microsoft MVP. *Perth, Western Australia
Tips for Access users -http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"timmg" <tmillsgronin.. [e-mail] e in
messagenews:f6* *************** *************** ***@w7g2000hsa. googlegroups.co m...

I have an application were users evaluate records and based on the
amount of money involved answer between 17 and a 120 questions on one
of three response forms. *I have a master list of question in one
table and another that links the subset of questions to the particular
form. *When the user gets ready to answer the questions the app
appends the questions to the answer table - all of which makes it easy
to answer the questions within a subform for the record they're
reviewing.

So far, I think, so good.

Now the output can go either to an Excel spreadsheet or Word doc - one
record per file, which then gets printed and mailed to the next step
in the business process.

Here's where I'd love your opinions: *One option is *I can concatenate
the responses into a merge file (likely CSV, but could be anything,
and execute the merge from the Word doc. *I can shell out to Word or
just execute from within Access. *I'm not thrilled with the Excel
idea, but some users have made SSs that look like the Word docs - I
only mention it in case someone says "well, it'd be really easy if you
were moving the answers to Excel."

Another is opening a Doc template from within Access and inserting the
data via bookmarks.

In either case the resulting document needs to be saved with the
record number as the file name.

Which would you say is the better path? *Is there an easier way? *The
Access report to RTF option probably isn't robust enough given the
formatting required, but I might be able to make it work if the
consensus is that that's the way to go.

Thanks for your time

Tim Mills-Groninger- Hide quoted text -

- Show quoted text -

[timmg]

On Jul 11, 8:35*am, Mike P <mikewpa...@tis cali.co.ukwrote :

On 10 Jul, 19:36, timmg <tmillsgronin.. [e-mail] e:

I have an application were users evaluate records and based on the
amount of money involved answer between 17 and a 120 questions on one
of three response forms. *I have a master list of question in one
table and another that links the subset of questions to the particular
form. *When the user gets ready to answer the questions the app
appends the questions to the answer table - all of which makes it easy
to answer the questions within a subform for the record they're
reviewing.

So far, I think, so good.

Now the output can go either to an Excel spreadsheet or Word doc - one
record per file, which then gets printed and mailed to the next step
in the business process.

Here's where I'd love your opinions: *One option is *I can concatenate
the responses into a merge file (likely CSV, but could be anything,
and execute the merge from the Word doc. *I can shell out to Word or
just execute from within Access. *I'm not thrilled with the Excel
idea, but some users have made SSs that look like the Word docs - I
only mention it in case someone says "well, it'd be really easy if you
were moving the answers to Excel."

Another is opening a Doc template from within Access and inserting the
data via bookmarks.

In either case the resulting document needs to be saved with the
record number as the file name.

Which would you say is the better path? *Is there an easier way? *The
Access report to RTF option probably isn't robust enough given the
formatting required, but I might be able to make it work if the
consensus is that that's the way to go.

Thanks for your time

Tim Mills-Groninger

Have you tried the 'Access report to snapshot' option? The snapshot
output format retains all of the formatting of the originating report.
The snapshot viewer is installed with Access 2000 when the snapshot
file is created. It is available as a free of charge download from the
MS website for those who do not have Access.

If that is not to your liking, initiating a mail merge from within
Access is feasible if all the merged data are plain text. Remember
that this inserts a section break after each merged record; you
probably will need continuous sections rather than new page sections.

If you wish to insert a Word table, for example, then try the
'Insertdatabase ' method of the Word object to insert an Access table
directly at the appropriate bookmark in the Word document. The
Insertdatabase method can be problematic if the datasource is in a
secured database. As an alternative, try the 'Insertfile' method using
an exported RTF file as the data source.

You can use a combination of insert methods and mail merge but I don't
think that bookmarks are retained in the ouput Word document so make
inserts at bookmarked locations before running a mail merge.

Good points, thank you. I'd likely do one or the other, not a
combination. I'm going to cogitate on the problem for a while long
while I build some other features.

tim

>
Mike P- Hide quoted text -

- Show quoted text -