Reports with lots of fields not otherwise in database

[Darryl Kerkeslager]

As the subject above hopefully makes clear, I want to do several reports,
"with lots of fields not otherwise in database". These reports also have
variable-length text. I have defined the following options (all users have
Office 2000):
1. All Access solution. This appears to involve creating forms to enter
data that is just put into a report, and never saved - an exercise in wasted
development time, to my thinking. Also, the variable-length fields create
formatting issues. But theere are plusses.

2. Push into Word. This seems to involve the same amount of extra form
creation time, and even more coding, since each individual field needs to
have the data pushed into it. This is particularly time-consuming for a
printed five-page document made up of five forms, each with the top 1/3 page
the same identical data. This would handle variable text well.

3. Pull the info into Word using print merge. I haven't tried this yet. It
does seem to offer less coding, but I am concerned that there may be other
issues - like does it work well? This solution would alos handle variable
text.

4. I have seen somewhere that you could use software to convert the report
to a Word document post-data-import. This may help with the variable-length
text, and allow for the relatively faster coding of doing a report. But
will it require extra software?

Others must have handled this kind of issue. So far, having tried #1 and
#2, I'm not happy with either. Is there a better way?
Darryl Kerkeslager

[Larry Linson]

I don't recall ever having a requirement to put in Reports "lots of Fields
not otherwise in the database". I have done Reports that simply picked up a
few fields from an open Form into which the user had entered the information
into unbound Fields. But, every user's requirements are different.

Perhaps with more details on where you plan to display the typed-in
information, and, at least a rough overview of what is from the database and
what is typed in, we could be more helpful. If it is some Fields that apply
to the whole Report, to be displayed in the Report Header/Footer, that's one
thing; if it is some Fields to be entered separately for each Record Detail,
that's another.

I have, frankly, not had good results with formatting carrying through
properly into Word when I export as RTF ("Save as Microsoft Word"). You
would, perhaps, have better luck if you used COM Automation to manipulate
Word from Access -- that isn't "rocket science" but it does require learning
the Word object model.

On the other hand, maybe if the reports are _mostly_ user-entered
information, they would be best done IN Word, and queries used to retrieve
the information from the Access database?

Larry Linson
Microsoft Access MVP

"Darryl Kerkeslager" <Ke*********@comcast.net> wrote in message
news:S7********************@comcast.com...

As the subject above hopefully makes clear, I want to do several reports,
"with lots of fields not otherwise in database". These reports also have
variable-length text. I have defined the following options (all users have Office 2000):
1. All Access solution. This appears to involve creating forms to enter
data that is just put into a report, and never saved - an exercise in wasted development time, to my thinking. Also, the variable-length fields create
formatting issues. But theere are plusses.

2. Push into Word. This seems to involve the same amount of extra form
creation time, and even more coding, since each individual field needs to
have the data pushed into it. This is particularly time-consuming for a
printed five-page document made up of five forms, each with the top 1/3 page the same identical data. This would handle variable text well.

3. Pull the info into Word using print merge. I haven't tried this yet. It does seem to offer less coding, but I am concerned that there may be other
issues - like does it work well? This solution would alos handle variable
text.

4. I have seen somewhere that you could use software to convert the report to a Word document post-data-import. This may help with the variable-length text, and allow for the relatively faster coding of doing a report. But
will it require extra software?

Others must have handled this kind of issue. So far, having tried #1 and
#2, I'm not happy with either. Is there a better way?
Darryl Kerkeslager

[Darryl Kerkeslager]

"Larry Linson" <bo*****@localhost.not> wrote

I don't recall ever having a requirement to put in Reports "lots of Fields
not otherwise in the database". I have done Reports that simply picked up a few fields from an open Form into which the user had entered the information into unbound Fields. But, every user's requirements are different.

I have, frankly, not had good results with formatting carrying through
properly into Word when I export as RTF ("Save as Microsoft Word"). You
would, perhaps, have better luck if you used COM Automation to manipulate
Word from Access -- that isn't "rocket science" but it does require learning the Word object model.

On the other hand, maybe if the reports are _mostly_ user-entered
information, they would be best done IN Word, and queries used to retrieve
the information from the Access database?

The particular report that is most bothersome to me at the moment is this:

- Five printed pages
- The header of each page consists of about 15 fields identical demographic
information that is already in the database.
- The remaining 2/3 of each page varies.
- On 3 forms, the information is mostly placing two different state names in
the proper places in the text - no big deal
- The other two pages have address information, short blocks of text,
variable-length of text, check-marks, etc, etc. There is no practical
reason for storing most of this information in the database; the chance of
it ever being used again is slim, and most of the data is of no practical
ben3fit other than to fill out this one-time paper form.

I received the forms in Word, so I left them there. The Word docs already
had form-fields placed.
So I used Word automation:

Dim mWord As Object
Set mWord = CreateObject("Word.Application")
Set cnxn = CurrentProject.Connection
Set rs = New ADODB.Recordset
mWord.Documents.Add oDOCS & "TransReq.dot"
With mWord.ActiveDocument

For data already in the database:
.Bookmarks("off_ssn").Range.Fields(1).Result.Text =
Format(rs.Fields("off_ssn"), "000-00-0000")
.FormFields("sex_offender").CheckBox.Value =
rs.Fields("off_sex_offender")

Other "throw-away" data was entered in an Access form, then just placed in
Word:
.FormFields("trf_verified").CheckBox.Value = Me.chkVerified
.Bookmarks("trf_ver_date").Range.Fields(1).Result. Text =
Me.txtVerDate

I didn't want to fill in the form 90% and leave the users to hunt in fuve
Word docs for the remaining fields to fill out, knowing that this would lead
to missed fields.

My complaint is that this method seems overly cumbersome - for each field, I
need a line of code to insert the data into Word, and the total fields in
the five Word docs is about 120. Contemplating 30-40 such documents (no
more 5-pagers though) makes me ask - is there a better way?

BTW, I cannot alter the document layout or data in any way.

Darryl Kerkeslager