Creating dynamic letters from templates in access

[halabis]

I have been tasked with creating an access database that will store a large number of email templates, that can then be used to quickly create responses to customers. The templates all have varying numbers of fields that need to have custom data entered into Example:

"Thank you for contacting our customer care center. Your account balance is <Customer Specific Data> and your due date is <Customer specific data>"

The customer data is NOT stored in the database and is to be entered manually for each letter.

Has anyone had to do this before? If so do you have any tips and or advice? Should I use a form or a report for the output letters? How should I format the form for generating the letters? So far I have a couple of drop downs in a form to select the correct template. I suspect I am going to need to add fields where the user can type in the customer specific data, but I don't know how I can adjust how the fields display to incorporate the customer data mid text.



Like I said, any advice would be appreciated.

(And Personally I think this is better handled in excel, and in fact have already made an excel sheet that does all of this, but It has been mandated from above that we do it in access.)

Thanks!

[ranman256]

The user has a combo box to pick the customer
then all needed data can be copied to the boxes needed.

[CJ_London]

in principle, have a table with all your phrases/paragraphs in but with specific names for each type of data. e.g.

"Thank you for contacting our customer care center. Your account balance is <Customer Balance> and your due date is <Customer DueDate>"

These could be selected from your dropdown

I suspect I am going to need to add fields where the user can type in the customer specific data

don't see why you need fields - you may need controls on a form. All depends on how you want it to work

don't know how I can adjust how the fields display to incorporate the customer data mid text.

you can then use the replace function to replace <Customer Balance> and <Customer DueDate> with the entries made in unbound controls on a form.

Should I use a form or a report for the output letters? How should I format the form for generating the letters?

Output as a report which you can use to format as required.

[halabis]

in principle, have a table with all your phrases/paragraphs in but with specific names for each type of data. e.g.

you can then use the replace function to replace <Customer Balance> and <Customer DueDate> with the entries made in unbound controls on a form.

This is exactly the direction i needed to be pointed in. Thanks!

[CJ_London]

if you wanted to you could use the split function to find out what values need to be completed

e.g. if your field is called phrase then some vba code like this could be used to populate labels called lbl1, lbl2 etc.

dim capture() as string
dim I as integer
dim j as integer
dim p as integer

capture=split(phrase,"<")
j=1
for I=0 to ubound(capture)-1
    p=instr(capture(I),">")
    if p<>0 then 
        me("lbl" & j).caption=left(capture(I),p-1)
        j=j+1
    end if
next I

you would then hide all unrequired labels and associated unbound controls