Letter generation via a report?

[Gasman]

Hi all,

I have been tasked with creating a rudimentary letter generation system in Access 2007.

Initial thoughts are to create a set of ready made paragraphs. One to each record's Memo field. The record will also hold attributes for the text, bold underline etc.

User will then select a check box to include record in the report that creates the letter. Option to reorder the sequence.

Report will then produce all the paragraphs in requested sequence.

I did not think Word automation would work, due to the variety of paragraphs being selected changing depending on various circumstances.

Has anyone any tips/caveats to watch out for please.

TIA

 

[CJ_London]

Something I've done in the past is to have some standard 'fields' (such as name, postcode, county etc) which can be used within the memo field.

So the paragraph includes a field by identifying it with square brackets (or some other combination) e.g.

We are contacting everyone in the [county] area to advise them.....

then part of the word creation routine would be to replace '[county]' with the county of the person being communicated with

 

[Ranman256]

you'd need fields (integer) to determine the order of the memo box.
if query results:
FirstN,LastN, memo1, memo2, ShowBox1, ShowBox2
bob, smith, text.., text..., 2, 1

youd need 2 memo fields on top of one another, both invisible,
then in the ON PRINT event, show the textbox that is in ShowBox1

set txtBox = controls("textBox" & me.showBox1)
txtBox.visible = true

and so on with memo 2, and on down the sheet.

 

[Gasman]

Hi CJ_London,

At present the letter has been created in Word with sentences in Red where they need deleting/amending etc.

I can see the need to insert some data in with the text and was wondering on how to go about that. I was also thinking of using something like '[sex]' as a marker and then work out some way of substituting that for the actual value.?

Something I've done in the past is to have some standard 'fields' (such as name, postcode, county etc) which can be used within the memo field.

So the paragraph includes a field by identifying it with square brackets (or some other combination) e.g.

We are contacting everyone in the [county] area to advise them.....

then part of the word creation routine would be to replace '[county]' with the county of the person being communicated with

 

[CJ_London]

'[sex]' as a marker and then work out some way of substituting that for the actual value.?

perhaps gender rather than sex?

in principle if you have the data it can be done e.g. in a query

updatedmemo:replace([Memo],"[Sex]",iif([title]="Mr","Male","Female"))

but you would need more if title was 'Dr.', perhaps based on first names? But then you are probably talking about using a udf as it becomes more complicated.

 

[Gasman]

Sorry Ranman256,

You've got me there.:banghead:

I was thinking that each paragraph was in a detail record. So if you selected 6 paragraphs, there would be six records (somewhere) The data for the report would be sorted on the order field in the record. The paragraph would also have a heading field for it. There might not be data in some heading fields, so paragraphs will just appear to be for the same heading?

So the report would look something like

[Heading Field]
[ParagraphText]

[Heading Field]
[ParagraphText]

[Heading Field]
[ParagraphText]

and my data like

FirstN,LastN,Order,Heading,Paragraph,Attributes....
Bob, Smith,1,text,text,Bold
Bob, Smith,2,text,text,Normal
Bob, Smith,3,text,text,Underline

So I am unsure as to why two memo fields are required.?
Could you explain a little more please?

I have also discovered that the memo fields can hold Rich Text, which *might* make life a little easier?

That was the reason for the font attributes for each paragraph, so I could amend the data in the Detail On print event?

I have attached a picture of what I have at present.

I also need to allow the user to amend the paragraphs, perhaps add an extra sentence or two. The set paragraphs are just for quick creation of the basic letter. It will need to be tailored every time.

The reason I need to do something is that the existing system only took the name and address plus date as variables, and it is also outside our control.

Bit of a task ahead I know.

you'd need fields (integer) to determine the order of the memo box.
if query results:
FirstN,LastN, memo1, memo2, ShowBox1, ShowBox2
bob, smith, text.., text..., 2, 1

youd need 2 memo fields on top of one another, both invisible,
then in the ON PRINT event, show the textbox that is in ShowBox1

set txtBox = controls("textBox" & me.showBox1)
txtBox.visible = true

and so on with memo 2, and on down the sheet.

 

[Gasman]

perhaps gender rather than sex?

in principle if you have the data it can be done e.g. in a query

updatedmemo:replace([Memo],"[Sex]",iif([title]="Mr","Male","Female"))

but you would need more if title was 'Dr.', perhaps based on first names? But then you are probably talking about using a udf as it becomes more complicated.

I am thinking that the name and address fields will go in the group header. At the moment I am only thinking of 1 letter per person. That is pretty much how the user will work, as each letter still need manual tailoring.

Somewhere I will walk through the records and replace [gender] with 'Male', [years] with whatever the value is in years.
I have amounts to process also.
I have some ideas, but not much idea on how to implement them.
Still working on the structure as well.