From Access Reports to MS Word

[Guest]

Hello,

I only know slightly more than the basics in Access, and I'm sure there is a
better way to do what I want to do. I am also familiar with bits of SQL and
VB, but only bits... now, onto the question.

I'm doing Masters research and have created an Access database to track my
notes. It's pretty simple and I'm sure poorly designed, but I have three
tables at the moment:

1) Sources (SourceID, Title, Author, Date, Publisher, City)
2) Categories (CategoryID, Category, Sub-Category) --> for organizing and
grouping notes by topic rather than by source.
3) Notes (NotesID,Source from #2, Category from #2, Notes)

I have also created a series of reports. I know that if I want to export a
report into rich text format, I can right click it and click export, but that
doesn't quite do what I need. I would like to export to rtf, but also
control line breaks, etc., rather than have the exported rtf match exactly my
report.

For example, I created a report to generate a quick bibliographic listing.
The report only has one field (=[Author] & ". " & [Title] & ". " &
[Publisher] & ": " & [City] & ", " & [Date] & "."). This successfully lists
and sorts the sources, but when I export the report to rtf and open it in
Word, it tries too hard to keep the formatting, and even adds line breaks if
the field wraps.

If I'm intending on most of this information making its way to Word, is
there a simpler way to handle exporting data in a controlled format?

Hopefully that made some sense. If you wish to take a look at the database
(and you trust that I haven't encoded any viruses into it), you can download
it here:
http://daapspace.daap.uc.edu/~wilschs/thesis_notes.mdb

Feel free to comment on anything else that strikes you, too; I make no
claims that it is a well designed database, but I don't have much experience
at these.

Thanks in advance,
Seth

 

[Alex Ivanov]

If you need it in Word, then the best way is to create your report in Word.
This way you will have greater control of document layout.
Create a Word template with desired layout (Report Header, one Section
header, one subheader, one subsubheader, ..., one detail)
Insert bookmarks in places where you want to enter the data. Highlight one
section at a time and save it as an autotext entry within the template
(Insert/Autotext/Autotext) make sure your template is selected in the Look
in box. Give a distinct name to the autotext entry (MainHeader,Subheader,
Detail, etc.) then click Add. Delete the selection, you don't need it
anymore.

Repeat these steps for all sections of your report, except perhaps the main
report header, you can leave it in the body of the template.
The create an Access module that opens the source recordset(s), creates a
new Word document based on that template and populates it.
The sample below does not use headers, but it will give you an idea... use
nested loops for handling headers.
http://www.aleksoft.net/samples/Access_Word.zip

Instead of one source recordset it may be easier to open one recordset for
outhermost header and while processing this recodset in the loop open
another recordset based on current record id and process it in an inner loop
etc...

HTH
--
Please reply to NG only. This email is not monitored.
Alex.

Hello,

I only know slightly more than the basics in Access, and I'm sure there is
a
better way to do what I want to do. I am also familiar with bits of SQL
and
VB, but only bits... now, onto the question.

I'm doing Masters research and have created an Access database to track my
notes. It's pretty simple and I'm sure poorly designed, but I have three
tables at the moment:

1) Sources (SourceID, Title, Author, Date, Publisher, City)
2) Categories (CategoryID, Category, Sub-Category) --> for organizing and
grouping notes by topic rather than by source.
3) Notes (NotesID,Source from #2, Category from #2, Notes)

I have also created a series of reports. I know that if I want to export
a
report into rich text format, I can right click it and click export, but
that
doesn't quite do what I need. I would like to export to rtf, but also
control line breaks, etc., rather than have the exported rtf match exactly
my
report.

For example, I created a report to generate a quick bibliographic listing.
The report only has one field (=[Author] & ". " & [Title] & ". " &
[Publisher] & ": " & [City] & ", " & [Date] & "."). This successfully
lists
and sorts the sources, but when I export the report to rtf and open it in
Word, it tries too hard to keep the formatting, and even adds line breaks
if
the field wraps.

If I'm intending on most of this information making its way to Word, is
there a simpler way to handle exporting data in a controlled format?

Hopefully that made some sense. If you wish to take a look at the
database
(and you trust that I haven't encoded any viruses into it), you can
download
it here:
http://daapspace.daap.uc.edu/~wilschs/thesis_notes.mdb

Feel free to comment on anything else that strikes you, too; I make no
claims that it is a well designed database, but I don't have much
experience
at these.

Thanks in advance,
Seth

 

[Guest]

Alex:

Thanks much for your assistance. Quick question for you or anyone else that
may know the answer.

The sample you sent (http://www.aleksoft.net/samples/Access_Word.zip) works
perfectly. It was created in an earlier version of MS Access; and Access
asks each time I open it if I want to convert the DB to a newer version (2003
in this case). If I choose "no," the module works perfectly but I cannot
make any changes to the DB. If I choose "yes," or I copy the code to a new
2003 Access DB, then I get an error when it executes:

Run-timer error 5151, Word was unable to read this document. It may be
corrupt...

Any ideas on where the version is messing something up???

Thanks much; it's much appreciated,
Seth

If you need it in Word, then the best way is to create your report in Word.
This way you will have greater control of document layout.
Create a Word template with desired layout (Report Header, one Section
header, one subheader, one subsubheader, ..., one detail)
Insert bookmarks in places where you want to enter the data. Highlight one
section at a time and save it as an autotext entry within the template
(Insert/Autotext/Autotext) make sure your template is selected in the Look
in box. Give a distinct name to the autotext entry (MainHeader,Subheader,
Detail, etc.) then click Add. Delete the selection, you don't need it
anymore.

Repeat these steps for all sections of your report, except perhaps the main
report header, you can leave it in the body of the template.
The create an Access module that opens the source recordset(s), creates a
new Word document based on that template and populates it.
The sample below does not use headers, but it will give you an idea... use
nested loops for handling headers.
http://www.aleksoft.net/samples/Access_Word.zip

Instead of one source recordset it may be easier to open one recordset for
outhermost header and while processing this recodset in the loop open
another recordset based on current record id and process it in an inner loop
etc...

HTH
--
Please reply to NG only. This email is not monitored.
Alex.

Hello,

I only know slightly more than the basics in Access, and I'm sure there is
a
better way to do what I want to do. I am also familiar with bits of SQL
and
VB, but only bits... now, onto the question.

I'm doing Masters research and have created an Access database to track my
notes. It's pretty simple and I'm sure poorly designed, but I have three
tables at the moment:

1) Sources (SourceID, Title, Author, Date, Publisher, City)
2) Categories (CategoryID, Category, Sub-Category) --> for organizing and
grouping notes by topic rather than by source.
3) Notes (NotesID,Source from #2, Category from #2, Notes)

I have also created a series of reports. I know that if I want to export
a
report into rich text format, I can right click it and click export, but
that
doesn't quite do what I need. I would like to export to rtf, but also
control line breaks, etc., rather than have the exported rtf match exactly
my
report.

For example, I created a report to generate a quick bibliographic listing.
The report only has one field (=[Author] & ". " & [Title] & ". " &
[Publisher] & ": " & [City] & ", " & [Date] & "."). This successfully
lists
and sorts the sources, but when I export the report to rtf and open it in
Word, it tries too hard to keep the formatting, and even adds line breaks
if
the field wraps.

If I'm intending on most of this information making its way to Word, is
there a simpler way to handle exporting data in a controlled format?

Hopefully that made some sense. If you wish to take a look at the
database
(and you trust that I haven't encoded any viruses into it), you can
download
it here:
http://daapspace.daap.uc.edu/~wilschs/thesis_notes.mdb

Feel free to comment on anything else that strikes you, too; I make no
claims that it is a well designed database, but I don't have much
experience
at these.

Thanks in advance,
Seth

 

[Alex Ivanov]

I think this might be the offending line of code
Set doc = .Documents.Add(Left(CurrentDb.Name, Len(CurrentDb.Name) - 8) &
"report.dot")
If the converted mdb file's lenght differ from 8 characters, it will blow
up.
Also set references to your Word Type Library (If you converted the database
to a newer version of Access, the Conversion Wizard should have it done for
you) as the sample uses early binding to Word.

If none of the above suggestions work, try to hard code the full path to the
report.dot.

HTH

--
Please reply to NG only. This email is not monitored.
Alex.

Alex:

Thanks much for your assistance. Quick question for you or anyone else
that
may know the answer.

The sample you sent (http://www.aleksoft.net/samples/Access_Word.zip)
works
perfectly. It was created in an earlier version of MS Access; and Access
asks each time I open it if I want to convert the DB to a newer version
(2003
in this case). If I choose "no," the module works perfectly but I cannot
make any changes to the DB. If I choose "yes," or I copy the code to a
new
2003 Access DB, then I get an error when it executes:

Run-timer error 5151, Word was unable to read this document. It may be
corrupt...

Any ideas on where the version is messing something up???

Thanks much; it's much appreciated,
Seth

If you need it in Word, then the best way is to create your report in
Word.
This way you will have greater control of document layout.
Create a Word template with desired layout (Report Header, one Section
header, one subheader, one subsubheader, ..., one detail)
Insert bookmarks in places where you want to enter the data. Highlight
one
section at a time and save it as an autotext entry within the template
(Insert/Autotext/Autotext) make sure your template is selected in the
Look
in box. Give a distinct name to the autotext entry (MainHeader,Subheader,
Detail, etc.) then click Add. Delete the selection, you don't need it
anymore.

Repeat these steps for all sections of your report, except perhaps the
main
report header, you can leave it in the body of the template.
The create an Access module that opens the source recordset(s), creates a
new Word document based on that template and populates it.
The sample below does not use headers, but it will give you an idea...
use
nested loops for handling headers.
http://www.aleksoft.net/samples/Access_Word.zip

Instead of one source recordset it may be easier to open one recordset
for
outhermost header and while processing this recodset in the loop open
another recordset based on current record id and process it in an inner
loop
etc...

HTH
--
Please reply to NG only. This email is not monitored.
Alex.

Hello,

I only know slightly more than the basics in Access, and I'm sure there
is
a
better way to do what I want to do. I am also familiar with bits of
SQL
and
VB, but only bits... now, onto the question.

I'm doing Masters research and have created an Access database to track
my
notes. It's pretty simple and I'm sure poorly designed, but I have
three
tables at the moment:

1) Sources (SourceID, Title, Author, Date, Publisher, City)
2) Categories (CategoryID, Category, Sub-Category) --> for organizing
and
grouping notes by topic rather than by source.
3) Notes (NotesID,Source from #2, Category from #2, Notes)

I have also created a series of reports. I know that if I want to
export
a
report into rich text format, I can right click it and click export,
but
that
doesn't quite do what I need. I would like to export to rtf, but also
control line breaks, etc., rather than have the exported rtf match
exactly
my
report.

For example, I created a report to generate a quick bibliographic
listing.
The report only has one field (=[Author] & ". " & [Title] & ". " &
[Publisher] & ": " & [City] & ", " & [Date] & "."). This successfully
lists
and sorts the sources, but when I export the report to rtf and open it
in
Word, it tries too hard to keep the formatting, and even adds line
breaks
if
the field wraps.

If I'm intending on most of this information making its way to Word, is
there a simpler way to handle exporting data in a controlled format?

Hopefully that made some sense. If you wish to take a look at the
database
(and you trust that I haven't encoded any viruses into it), you can
download
it here:
http://daapspace.daap.uc.edu/~wilschs/thesis_notes.mdb

Feel free to comment on anything else that strikes you, too; I make no
claims that it is a well designed database, but I don't have much
experience
at these.

Thanks in advance,
Seth

 

[Alex Ivanov]

In Access 2003 you can
Set doc = .Documents.Add(Left(CurrentDb.Name, InStrRev(CurrentDb.Name,
"\")) & "report.dot")
which does not depend on filename length. This is not an option in Acc97
unless you write your own implementation of InStrRev, which is not hard to
do, but I'm lazy...

--
Please reply to NG only. This email is not monitored.
Alex.

I think this might be the offending line of code
Set doc = .Documents.Add(Left(CurrentDb.Name, Len(CurrentDb.Name) - 8)
& "report.dot")
If the converted mdb file's lenght differ from 8 characters, it will blow
up.
Also set references to your Word Type Library (If you converted the
database to a newer version of Access, the Conversion Wizard should have
it done for you) as the sample uses early binding to Word.

If none of the above suggestions work, try to hard code the full path to
the report.dot.

HTH

--
Please reply to NG only. This email is not monitored.
Alex.

Alex:

Thanks much for your assistance. Quick question for you or anyone else
that
may know the answer.

The sample you sent (http://www.aleksoft.net/samples/Access_Word.zip)
works
perfectly. It was created in an earlier version of MS Access; and Access
asks each time I open it if I want to convert the DB to a newer version
(2003
in this case). If I choose "no," the module works perfectly but I cannot
make any changes to the DB. If I choose "yes," or I copy the code to a
new
2003 Access DB, then I get an error when it executes:

Run-timer error 5151, Word was unable to read this document. It may be
corrupt...

Any ideas on where the version is messing something up???

Thanks much; it's much appreciated,
Seth

If you need it in Word, then the best way is to create your report in
Word.
This way you will have greater control of document layout.
Create a Word template with desired layout (Report Header, one Section
header, one subheader, one subsubheader, ..., one detail)
Insert bookmarks in places where you want to enter the data. Highlight
one
section at a time and save it as an autotext entry within the template
(Insert/Autotext/Autotext) make sure your template is selected in the
Look
in box. Give a distinct name to the autotext entry
(MainHeader,Subheader,
Detail, etc.) then click Add. Delete the selection, you don't need it
anymore.

Repeat these steps for all sections of your report, except perhaps the
main
report header, you can leave it in the body of the template.
The create an Access module that opens the source recordset(s), creates
a
new Word document based on that template and populates it.
The sample below does not use headers, but it will give you an idea...
use
nested loops for handling headers.
http://www.aleksoft.net/samples/Access_Word.zip

Instead of one source recordset it may be easier to open one recordset
for
outhermost header and while processing this recodset in the loop open
another recordset based on current record id and process it in an inner
loop
etc...

HTH
--
Please reply to NG only. This email is not monitored.
Alex.


Hello,

I only know slightly more than the basics in Access, and I'm sure
there is
a
better way to do what I want to do. I am also familiar with bits of
SQL
and
VB, but only bits... now, onto the question.

I'm doing Masters research and have created an Access database to
track my
notes. It's pretty simple and I'm sure poorly designed, but I have
three
tables at the moment:

1) Sources (SourceID, Title, Author, Date, Publisher, City)
2) Categories (CategoryID, Category, Sub-Category) --> for organizing
and
grouping notes by topic rather than by source.
3) Notes (NotesID,Source from #2, Category from #2, Notes)

I have also created a series of reports. I know that if I want to
export
a
report into rich text format, I can right click it and click export,
but
that
doesn't quite do what I need. I would like to export to rtf, but also
control line breaks, etc., rather than have the exported rtf match
exactly
my
report.

For example, I created a report to generate a quick bibliographic
listing.
The report only has one field (=[Author] & ". " & [Title] & ". " &
[Publisher] & ": " & [City] & ", " & [Date] & "."). This successfully
lists
and sorts the sources, but when I export the report to rtf and open it
in
Word, it tries too hard to keep the formatting, and even adds line
breaks
if
the field wraps.

If I'm intending on most of this information making its way to Word,
is
there a simpler way to handle exporting data in a controlled format?

Hopefully that made some sense. If you wish to take a look at the
database
(and you trust that I haven't encoded any viruses into it), you can
download
it here:
http://daapspace.daap.uc.edu/~wilschs/thesis_notes.mdb

Feel free to comment on anything else that strikes you, too; I make no
claims that it is a well designed database, but I don't have much
experience
at these.

Thanks in advance,
Seth