Form Letter Ideas

[gschimek]

I want to pick everyone's brain on this one, because I'm not sure what I want to do can be done very easily.

I have a database that tracks client information for insurance policies. I'm going to be giving it to other agents, many who are very computer illiterate. One of the requested functions is for them to be able to create form letters from the database.

So, I'd like some ideas on how to do that. Ideally, I'd like for there to be a form that someone could load some letter templates, but also tweak them or even create new ones that can be saved. It would also need to have "merge fields" much like you would in Microsoft Word. Basically since every agent will want to have their own letters, it needs to be easily editable.

Is there an easy way for a computer illiterate person to just select "interest letter," change some wording, and have it merge into a report? Or, is there an easy way for someone to get access to the merge fields if they were to do a Word mail merge? I don't really want people trying to find the right tables/queries amidst all the different tables in the database.

Or, throw out some other ideas. I'm open for most anything. Thanks in advance.

 

[Moniker]

Make a table for your templates. Call it something obvious like t_Letter_Templates. In that table, put these fields:

ID - AutoNumber (PK)
Template_Name - Text
Template_Owner - Text
Template_Text - Memo

Make one template that is the standard generic template. This will need to not be altered -- you're going to make copies of it to produce all the other templates that users may want.

What you're going to want here is fairly simple, but it'll take some programming skill. Everywhere that you want standard information (Contact Name, Date, etc.), you are going to put your own keyword(s). Your template might look something like this:

Dear {CONTACT NAME HERE},

On {DATE HERE}, we received your email concerning your interest in obtaining a {INSURANCE TYPE HERE} policy. Our rates are very competitive, such as {RATE HERE} per month.

And so forth. Use the brackets for each keyword you have, because the next step is to make a form that is going to fill in this template. On the form, get the user's input on all the keywords you place in the template. Get as much or as little information as you need -- we're just going to replace it in the template. You may want to make a form that asks simple questions like, "Enter the name of the contact:" and "Enter the date to be used:" and so on. Just note that for each piece you put in brackets, you need to get user input on what's supposed to go there. Additionally, be sure that each keyword is unique. You can be as descriptive as you want here.

From here, you do a little programming to get the standard template text, change the bracketed information, and then allow the user to edit it. It will look like this:

Dim strLetter As String

strLetter = DLookUp("Template_Text","t_Letter_Templates","Template_Name='Default' AND Template_Owner='Admin'")

Then, to replace each bracketed string, just use Replace statements.

strLetter = Replace(strLetter,"{CONTACT NAME HERE}",Your_Forms_Field_Name_For_Contact)
strLetter = Replace(strLetter,"{DATE HERE}",Your_Forms_Field_Name_For_Date)
strLetter = Replace(strLetter,"{INSURANCE TYPE HERE}",Your_Forms_Field_Name_For_Insurance_Type)
strLetter = Replace(strLetter,"{RATE HERE}",Your_Forms_Field_Name_For_Rate)

Do this for all your keywords. You now have a string consisting of your template, edited by the user simply by answering questions on a form. To allow the users to edit this, place a memo field on your form that is editable and assign your recently created string to that memo. They can do whatever they want here, and this is also the body of the report.

After they're done editing (if they edit at all), you need to append their customized letter to the table t_Letter_Templates. Make them enter a Template Name for that field, use Environ("username") to get their computer login ID and store that as the Template_Owner, and Template_Text is the string they created and edited. You can just use a simple append query here.

And there you have it. You now have a basic template that can be altered and edited on the fly, and each individual template is stored for each user. From here, you can easily make a front end that shows all of the reports made by particular people (filter on their login ID), you can add or remove keyword fields as necessary, and so on.

It's not too difficult, really, but you need to give it a shot and try everything you can. I'm just providing the direction I would take, which may or may not be what you were looking for. I know it would work as this is the same method I use to do dynamic SQL. However, it also does expect that you've got a pretty good understanding of VBA and how forms, tables, and reports interact with each other.

Good luck and HTH.

 

[GaryPanic]

there is a letter writer in the samples - which is kinda ok

what I have done is
on your base table have memo field for the bodily of the letter txt and txt field for the headings

lets take 5 memo field and 5 parag heading
have par1 and memo1 added pra as txt field memo1 as memo

now I have also set up a a table of letters 5 and 5 again
these have names
on my main form combo box tied to name after select letter and this populates my 10 fields 5 and 5 - these can then be edited to fit the quote/cover then record these to a letter history table (tie to the quote/policy)- this may not be what you are after and you can do a bit of clever formatting - I have faxes in mine wher if you pick fax from the combo drop down the report will be fax , memo , letter, broker letter,client letter -

I would whizz you over what I have done , but its messy and I have not had the chance to tidy it up

this might give you an idea on stuff

 

[gschimek]

Thanks! I'll give those ideas a try.

 

[gschimek]

Is there some code that I could attach to a button that would insert certain text wherever the cursor might be? So I could have a button that says "First Name" that when clicked, would enter {First_Name} wherever the cursor is at.