Don't tell your left hand what your right hand is doing

This old advice is given in the bible, by a man called Jesus. You can look it up in a book called Matthew, chapter 5. The moral was: if you do something good, don't let everybody know it. If you tell everybody how good you are, God wont reward you, as you will already have gotton your reward from the people around you.

Sometimes I see the same effct in organizations, but not on purpose, but by accident. Some call it lack of communication, some call it lack of coordination. The bigger the organization, the easier it is to forget to communicate something essential. One department invents something cool, but it remains a secret, known only within the department.

One nice example of lack of communication I discovered today in livemail, see this pic. For those who dont know dutch, the message in the pink background says: This message has been blocked for your safety.

MS Access and ms word

Consistent use of features and interface has never been a strong point of microsoft products. Some people accuse microsoft of cross selling to create and maintain monopolies, and I understand these accusations.

But I vividly remember that in an old version of ms office, the default directory whem I saved a new word document was the same as with the previous document, while ms excel alsways started in "my documents". As I never use the "my documents" directory for serious documents, but always tend to save important docs in "real" directories, I regretted the excel default. Unfortunately, since then microsoft has tended to standardize on "my documents" even more.

In one of my latest projects, the customer wanted to export reports to ms word. A fellow worker told me this would be no problem. Of course I should have been more careful. I objected and said that I wanted to see a working demo first.

He delivered one in record time.
It turned out he used the code:

DoCmd.OutputTo acOutputReport, "ReportName", acFormatRTF, "report.doc"

It worked beautifully, even with a subreport, and I accepted.

I should have read more closely. The "acFormatRTF" should have warned me. It generates an rtf file, which is saved as a word document. In a way MS Office are wonderful: even when the file extension tells you otherwise, ms word automatically recognize rtf and html.

One problem that arose was the use of page footers. We wanted to have a reportname and pagenumber at the bottom of each page. We added them to the pagefooter of the ms access report. When we exported the report to rtf, it generated plain text on each page. And the page lengths didnt match, so the pagenumber appeared a little bit higher on each page.

We could solve that one by making the pagefooter elements in the report invisible before exporting them. Then we wrote an ms word macro which added page footers.

In ms access:
Reports!Reportname.MyPageFooter.visible = false
DoCmd.OutputTo acOutputReport, "ReportName", acFormatRTF, "report.doc"

A second problem was that we had a lot of memo fields and subreports. Both tend to have variable lengths and a nice border around them. When exporting the reports to rtf, ms access forgot the borders.
But as our customer needed the borders to make the document pretty enough to comply with the standards demanded by their customers, we simply had to add these borders. You can sometimes persuade a customer to be satisfied with something less than he hoped for, but a good customer won't accept no for those products he sends directly to his own customer.

This solution was even more complex than the previous one. On the main report, we added 2 tages for each memofield and subreport: a "startsub" and "endsub", placed before and after each memofield and subreport. These were made invisible before showing in ms access, and made visible before exporting to rtf.
Again an ms word macro had to be written. This one selected all text between and including the two tags, and moved it into a newly inserted 1 cell table. Then the two tages were removed.

This worked fine in ms word 2002 and ms word 2003. Of course there was a minor hickup, as you can't expect ms word macro's to be backward compatible to ms word 2000 when it comes to basic operations like find and cut.

The final problem arose from the variable length memo fields and sub reports. We had carefully put the autochange size (or whatever the correct name in english may be, we used the dutch version) property at Yes/True. That worked very well while viewing the reports in ms access. It created chaos when exporting the reports to ms access. Some of them expanded, some of them didnt. After a few days we arrived at a solution: all the memo fields went into subreports, even when there was no reason for them, and nothing was left in the details section of the main report. This finally solved the problem, though a little teaser remained: the tags introduced in the previous paragraph.

It turned out that the tags, when on the main form, did move down as required with the subreprots in some versions of ms office, but not in others. I couldnt reference the tags from vb-script when they were in the subreport. I still havent solved this problem, though my fellow worker probably did this in the past weeks.