Sunday, February 24, 2008

Filling in form letters from Access

I ran across a few Access apps earlier last year where the customer had some very simple requests - to update the header, addresses, logos, etc. on the form letters their apps produced. Upon investigating the application, discovered that the original developer had essentially designed Access reports that simply mimic-ed the template the customer used. Textboxes, labels, images, etc. A seamingly reasonable approach, but in this case, with the end users have no Access experience, the text was essentially 'locked' from their point of view... and even if they had, whenever the template changed, the Access developer would have to reproduce their changes. A colleague, not a programmer, asked a simple question and made a simple suggestion - why couldn't they just use their existing Word template, the app could fill it in from the current record.

In digging, found this article,, to accomplish just that. Modified it slightly to sit in an code module and take an RecordID to allow the request record to be used, and toggle the template used based on other fields in the record (in this case, the templates could be assumed to contained the same form properties, only the boiler-plate text differed) We shipped the Document Templates in a folder called 'Templates' sitting next to the application.

  • The customer can update their own templates using Microsoft Word
  • Further ability to lock down modify access to the application, without risking cutting off manage-ability in the future (i.e. even if all existing staff leave or forget about the app, the template is sitting out for anyone with appropriate permissions to edit)
  • Having the seperate 'Templates' folder is an extra dependency that could be deleted, forgotten to be moved, etc.
  • A uncareful Word editor could accidentally delete or modify form properties instead of just the boilerplate text
  • Dependency on Word on the end-users side
Alternatives I have used in the past for similar scenarios:
-Scanning in the form and using as a background (more typical with 3rd party forms than the form letters I was dealing with here, can get out of hand in file size)
-The whole embedding the form letter as a Access report
-In the software BriefCase, used a model of exporting data to a text file, then kicking off Word documents with the Mail Merge feature bound to the text file. The user could modify their own templates, or even create new ones using a master list of merge fields (this is probably the richest model I've used yet, just certainly less quick-and-dirtythan the others, a little bit beyond basic Word training needed)
-In SQL Server Reporting Services, laying out a report to emulate a form letter (while having the fewest dependencies, assuming an SSRS environment exists, very awkward to design, as the tool is much more geared to tabular or matrix based reports, and flow layout vs. a fixed paginated layout needed) FWIW, I haven't really ran across a web-based solution that produces good fixed-layout, paginated reports, in any app I've built...

I've used the Word Form.Properties fill method in 3-4 Access applications, where I've needed to tack in a quick form letter reporting solution, was able to ship and control a whole 'folder', vs. having to ship a single file and/or had to prepare a more full-fledged installer package. I would recommend it in closed environments where you can assume that Word is standard (which is probably a good bet if Microsoft Access available)

Future steps, are to tie the model into .Net apps, which is hopefully where some of the Access apps will be going for other reasons. In Access, I could see pursuing some paths such as storing the templates in the db as BLOBS, or building something that would automatically download the templates if missing or updated... Not sure what bang-for-buck there is there. In a .Net app in the same environment, ClickOnce would manage the templates as dependencies.

Tuesday, February 19, 2008

Remote Desktop Gadget 2.1

Remote Desktop Gadget 2.1

Grabbed the updated version of the Remote Desktop Gadget for the Vista Sidebar. The 1.x version was really the only alternative I found that worked for me. Other tools out there that have features for looking up an OU, etc.: great concept, but they always seem to lockup. This tool seems very simple and clean. The 2.x version added 'History', in the form of a drop-down box when you expand the gadget. Still very lightweight, with this new feature.

Friday, February 8, 2008

Installing the AD Admin pack in Vista
This link shows how to install the Active Directory (AD) Admin pack in Windows Vista

Snippet for launching an Click-Once application

While a less common deployment scenario, perhaps. This code will launch an application installed through Click-Once. (Notably, assuming it is already installed)

Dim runningProc As System.Diagnostics.Process
runningProc = New

'Do not receive an event when the process
runningProc.EnableRaisingEvents = False

runningProc.Start("c:\Documents and Settings\" & UserName &
"\Start Menu\AppName.appref-ms")

What is an "Application Reference"?