Report using Word template

[AlanMiller]

I'm pretty new to Access 2003, particularly generating reports. I have developed successful techniques to "scoop up" cell values from XL workbooks and transfer them to "automated" Word templates as document variables. This enables me to produce "automatic" reports with all the formatting, bells & whistles of a standard Word document. From the examples I have seen, the reports possible in Access are comparatively more limited.

I'm wondering if, to repeat this technique with an Access db, I would be best to follow the same programmatic methods I've used in transferring XL data to a Word doc template, or if my impression of Access reports incorrect, and I should go for that alternative. The types of documents I need to produce contain formatting (color included), table layouts, headers, footers, footnotes and sometimes graphics. I know it's a pretty general question, but any opinions from those with experience in Access reports would be appreciated.

Alan

[Carol W.]

Alan,

I, personally, find Access reports to be very flexible, especially in the areas of sorting and grouping. Of course, I've been doing it for a few years so it's pretty easy for me to do reports.

Your requirements:

Report headers / footers - no problem
Formatting (color, font, size etc.) - no problem
Page headers / footers - no problem
Inserting images - no problem

Not sure what kind of table layouts you mean but you can lay out your row data in the report detail section and have it repeated for as many data records as are contained in your report record source. You can also design multi column reports. I've done this for labels, where there are three columns (for example) across the page.

Does this help? Maybe other posters will come along and add their input.

[AlanMiller]

Thanks Carol. Yes, this does answer my question as to whether I can make an Access report as "fancy" as what I'm used to in a Word document. It looks like I underestimated the rich content capability, having only seen some examples of pretty plain outputs. The only other (client) requirement I may have missed is the use of, I guess, the equivalent of frames in Word, which appear in some of their paper documents (see attached .PNG). I did manage to drag out an old NorthWind database (wonder if they're still going ) and found some fancier report samples there, which also are encouraging. I think most, if not all of their needs could be met from within Access, and this would be a tidy solution for their business model, I believe.

cheers
Alan

[Carol W.]

Alan,

I neglected to mention subreports in my earlier post. Subreports can be inserted in main reports and don't need to look anything like the main report. In fact, in one application I developed, I have one report that contains 11 subreports, each displaying different data in different formats. The important requirement for my application was that all 11 "reports" needed to be on one page. I accomplished this using subreports.

Just another flexible feature of Access.

[AlanMiller]

One more general query. I'd forgotten about Crystal Reports, which the client is familiar with and would purchase if needed. Is there anything this could offer that the internal Access reports could not?

Alan

[AlanMiller]

Alan,
I neglected to mention subreports in my earlier post.

Thanks Carol. I doubt that the client would need to go this deep, but it's another one for me to pursue on my training wheels . Good to know about the "independent" formatting flexibility in this context too.

cheers
Alan

[Wendell]

I'm late to the party, but let me add my 2 cents. Most of the things we do are done in reports, and I agree with Carol that Access reports are pretty flexible and can meet most of your needs. However there are some things where Word documents work better than Access reports - for example if you want to do such things as floating graphics behind or in front of text. Another issue is if your client wants justified text, Access text that is formatted that way doesn't look as nice as Word paragraphs that are justified. You also don't have options such as sub and super text. The bottom line is if your documents need to look like word processing documents, then you may be better off using Automation to create Word documents. We routinely do that where a document needs to be created as a file, although the ability to export to PDF in 2007 and 2010 has made that less necessary. We also Automate Excel and Outlook from Access, and it seems to work very well. I think you will find it works much like Automation from Excel.

[AlanMiller]

Thank you Wendell. All useful information. I seem to recall some sub/superscripts in the client's documents. I'll have to check that one out.

cheers
Alan

[AlanMiller]

Wendell. I did some searching and turned up RichText RTF2 Active control. Any comments on this? Also, found a trick of using the numeric sub/superscripts in the Lucinda Sans Unicode font. No alpha sub/superscripts though.

Alan

[Wendell]

Those are certainly things you can use - though I've not used that specific one. If you were using Access 2007, there is a rich text control in it, and you can do some fairly nice things with it. Another set of ActiveX controls that I am familiar with are from http://www.dbi-tech.com and are very polished - see their ctText control for example - we use them with our AccessUI product. The challenge with using ActiveX controls is that you have to deploy them to each workstation, and each workstation that is replaced or upgraded, and that can get to be a pain in the long run.

[AlanMiller]

The challenge with using ActiveX controls is that you have to deploy them to each workstation, and each workstation that is replaced or upgraded, and that can get to be a pain in the long run.

Don't I know it! I've had the same issues with my own single custom DLL in automated solutions I've done for similar business systems. Fortunately, this one is for a single computer (so far, that is). Thanks for all that info. I'll check it all out. BTW, the client has Office 2010. Any issues if I develop in 2003? I might migrate myself anyway.

Alan

[AlanMiller]

I unearthed an Older NON ActiveX RichText solution but I'm yet to see how it handles sub/superscript. It's been a looooooong time since I've written in RTF - those days of self-flagellation are way back in the distant past.

Alan

[Wendell]

Which is why you may want to consider using Automation with Word - we have a simple tutorial on our web site - Automation 101 but if you've been doing it from Excel, you will have a handle on how to manipulate the Word object model pretty well. Much of the time it is a crap-shoot as to which approach is less effort.

[AlanMiller]

Thanks again Wendell. My approach has been to use Word templates for the basis of automated documents. When I create a document from XL, I scoop up all of the relevant data from the workbook and dump it into a list of document variables in the template. The templates have menus for the user to insert any of the values as fields wherever they want (like mail merge fields) but it does some automated filling into the pre-existing template fields as well, of course. I shipped a single DLL to make it all happen, but even this approach could cause hiccups on some systems. I think I'll give the "all in one" Access approach a shot first - seems simpler and neater - and see how it all goes. My teething was done on Oracle, so Access already seems a bit foreign... but like changing programming languages, it just takes some time and practice.

Alan