Formatting within Access report text controls - OR - sorting and grouping in Word

[Mike MacSween]

tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.

At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls.
e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word?
I've tried all sorts of Crosstab cleverness but it seems silly not to use
the report writer, it being so good an' all.

Cheers, Mike

 

[Howard Kaikow]

Whether you create the reports in Access or in Word, you still need to pull
the data from somewhere and stick it in the desired place in your customized
report.

You can automate Word from Access, or vice versa, so you can use features in
both apps.
If you are concerned with formatting, then you could generate the report
using Word and keep the data in an Access data base.

 

[Mike MacSween]

Thanks, I'm aware of those things.

Let's see if you have any idea how to do this then.

The data structure is one to many. I assume you know what that means.

So a student will be on a course, which will have 0 to many events. The
letters I want will be something like this:

**************

Dear Student Name

Thanks for enrolling on Access For Dummies. The events for this course are:

20/4/4 - Introduction
21/4/4 - Conclusion

Many thanks

The administrator

***************

So the stuff in the middle of that is variable from course to course, yes?
Now, in a Access report that's easy to do using the sorting and grouping
functions, as you know. What I can't find a way to do it is in Word, if
there is an unknown (at design time, that is) number of events. But you seem
to be an expert on Word, no doubt you'll know.

Many thanks

Mike MacSween

 

[Chuck Grimsby]

Don't link to a table, Mike. Link to a query that does the sorting
and grouping. Note that you may have to use a few "levels" of queries
to duplicate the sorting and grouping you need, but that's going to
depend upon exactly what you are doing and your data structure.
Remember that you can link, and re-link the queries and tables
together, even to the same table over and over as many times as you
need.

Of late, I've come to hate Word reports, although they do have their
uses. A lot of what can be done in word as to formatting can be done
with a lot less effort in HTML (although that may be just a subjective
thing). Word can read HTML files easily enough, but so does the
browser on everyone's computer these days. There are a couple of
tricks you can use to ensure that the file opens up in word, if that's
what you have to have.

 

[Ruth]

Chuck,

I'm curious!!!

<<Remember that you can link, and re-link the queries and tables together,
even to the same table over and over as many times as you need.>>>

What can you achieve by doing this?

Thanks!

Ruth

 

[Mike MacSween]

Thanks Chuck

Yes, it is a query that's driving this, with a few other queries in that
too.

The basic problem is that the many side of the one to many has an unknown
number of events.

So the very easy thing that we can do with access reports using sorting and
grouping -

group on student ID

in detail show each event they're on.

can't be done, at least not in a similar way, in Word. Well, I guess I could
set up a Word template with the 'maximum number of event field I think I
might need at this point in time' fields. But it's cludgy.

Cindy Meister has some good links to this. But a google in the Word groups
where many people have posed similar questions gives the basic answer, even
from Word MVPs, that Access is better than Word for this.

Well, it would be, wouldn't it? It's a database program!!

The formatting the clients need isn't that fancy, and I'm sure I can
persuade them away from the precise formatting they would like, at least
when I tell them it's going to cost a lot more. More of a problem is that
they often like to edit the output. Though that's a requirements problem I
think.

I'm sure there are ways round it, concatenating every value from the many
side into one huge text field and using that, for instance, then outputting
the results to Word.

I'll do a demo with an Access report I think, then remind them they can
always RTF to Word if they really want to edit it.

Cheers, Mike

 

[Chuck Grimsby]

Actually, quite a bit!

It's a bit daunting to describe what can be done given such an open
ended question, but for one example, you can have a totals query of
some kind which is linked back to a select query to give you over all
totals for something without doing a grouping on a field that you
don't want grouped. An example of that might be a report which lists
all the bolts in a inventory table, and you need to show all the
different types, but you only need to know the over all count, not the
count for each separate type.

Does that make any sense?

 

[Chuck Grimsby]

Ah! I think I'm getting it now! Sorry for the confusion.

You're correct in thinking that you're not going to be able to use the
Merge function in Access to get that information to Word in a
different format, but you can do it if you use Office Automation (VBA)
to do the task.

Personally, I'm far more familiar with automating Word from Access
rather then the other way around (pushing to Word, rather then pulling
from Word), so I won't be much help there.

Using VBA from Access, it's just a matter of using a variable to
"monitor" a trigger field (in your case, the "student ID" field) to
figure out when to start a new form as your code loops through the
records pushing the data out to word.

 

[Steve]

tblCourses one to many to tblEvents.

A course may have an intro workshop (a type of event), a mid course
workshop, a final exam. Or any combination. Or something different in the
future.

At the moment the printed output is usually going to Word. It's turning into
an unholy mess, because I'm having to prepare umpteen different Word
templates, and the queries that drive them, depending on what events a
course has.

So I'd rather use Access reports. Group on CourseID, detail is events
attached to that Course, Bob's your uncle.

The clients at the moment have a small amount of formatting within
paragraphs. I can't find a way of doing that in Access Report text controls.
e.g. 'Your student number is <bold>123/456</bold>, make sure you don't
forget it'. Anybody know how to do that in an Access report?

Or alternatively, how to duplicate Access's sorting and grouping in Word?
I've tried all sorts of Crosstab cleverness but it seems silly not to use
the report writer, it being so good an' all.

Cheers, Mike

Mike, I think I've done what your talking about. I did this by coding
an On Format event procedure in the Detail section of the report. The
report I produce lists a series of accounts and related info. If the
account end date has passed, I highlight the field in red. Here's the
code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If ACCTEnd < Date Then
ACTPer.BackStyle = 1
ACTPer.BackColor = 11974910 'Sets background color to red if ending
date is less than
'current date
Else
ACTPer.BackStyle = 0
End If

End Sub

I realize this is a list, and it may be more complex in a "paragraph"
type report.

Good luck.

-Steve-

 

[Ruth]

I'll have to experiment a little!

How about multiple instances of a form - what can be achieved?

Thanks.

 

[Mike MacSween]

Thanks Steve

Yes, you're right. Separate controls are easy to format, but within a
control it seems to be impossible.

Mike

 

[Mike MacSween]

Thanks Chuck

If they insist on this then probably some sort of office automation is
probably the only way. Constructing the Word document piece by piece at
runtime.

At the moment I run an Access query, which actually sends the data to an rtf
and that is used as the source for a Word document based on one of about 50
Word templates.

It sounds more complicated that it is. I actually got it from The Access
Cookbook. It's nice because whenever I call it I can just specify from
Access - use qryX and templateY.dot and it runs. And because at run time the
data are actually held outside of Access it doesn't slow Access atall.

It's just going to be a PITA not to be able to use the
built-in-and-perfect-for-the-job tool that Access provides.

Cheers, Mike

 

[Chuck Grimsby]

Well, there are some caching problems you'll need to overcome if you
go down that route.... Multiple forms can also be quite confusing to
users, especially if they're all displaying data from the same source!
It's not something I'd advocate.