Template lookup to SQL Server DB value

[Kelli E.]

I want to build a module that does the following for multiple Word templates:
1. Request the user to input a policy#
2. Look up the policy info in a table in SQL Server Database with the policy #
3. Populate fields in the template (name, address, etc)
What is the best way to accomplish this? The data I need is in one table in
the database, dbo.STAT1. I want to be able to save this module to a bunch of
templates to automatically run when the template is opened. Any tips would
be greatly appreciated. Thanks!!

 

[Peter Jamieson]

I have some starting points for this, but nothing more than that.

If you want to pursue it, despam my email and get in touch:

pjj at pjjnet dot demon dot co dot uk

(NB a basic workable approach is
a. use an "auto macro" that will display a userform when a new
document is created from the template
b. the userform allows the user to enter an ID and verify that it is
the correct one.
c. data from the selected record is used to set the values of Word
Variables
d. DOCVARIABLE fields are used to insert those values in the document

If you search this group, e.g. on Google groups, you will find some
examples of this kind of approach, e.g. by Doug Robbins - I think most
if not all deal with Access and use DAO to get data. With SQL Server
you'd need to use ADO instead (not that big a change); unless you can
always guarantee that the user can type in a valid policy ID, you may
also need a mechanism to enable the user to find a policy; I assume when
you say "table" you mean "table" but in many environments such a table
would actually be accessed via a Transact-SQL procedure
)

Peter Jamieson

http://tips.pjmsn.me.uk