Have hit a brick wall

Gasman

Gasman

Enthusiastic Amateur
Local time
Today, 16:36
Joined
Sep 21, 2011
Messages
17,469
Hi everyone,

I *thought* I was making reasonable progress on this letter creation project. However I have hit a brick wall now.:banghead:

I have a fairly decent skeleton report layout at the moment.
The detail line just consists of
Textbox for header for the RTF textbox below
RTF textbox for Memo field

As the memo field is RTF and capable of various colours, formats the output whilst needing tidying up is almost what I was looking for.

However when I export to Word for final tidy up, bullet points are gone, colour is gone (I could perhaps live with that), but the format looks very little like the format in the report.
I had always intended to make the final copy in Word, as I knew I could not achieve everything in Access due to my limitations and experience. However I expected to get the bulk of the letter created with a few clicks.

So!, where do I go from here.?:mad:

I'm open to suggestions please.

Steve (Sneuberg) had mentioned doing it in Word if he was doing it, and I did not dismiss that idea out of hand, just that at present I have multiple records as paragraphs, and I know I need to allow for more records/paragraphs in the future.

Is it possible to program a Word document to accept multiple records in a mailmerge.

The requirement will always be for one letter only I believe, to avoid potential mistakes.


So layout would be
Client Name and Address
Date
Dear Mr Client Name,

Record1
Record2
Record 3......

All has not been wasted, I still need the data, and the functionality of selecting paragraphs/assembling the letter, it is just the end result into a Word Document that has got me stumped.

As always I'm grateful for advice/suggestions.

TIA
 
I haven't playing with mail merge much but I believe it's geared to one Access record per document. If you want to try it I suggest first seeing if you can merge a RTF field into the document without losing the formatting.

If you decide to go the full Word Automation route you have pretty much control of everything so multiply records within a document is not a problem. There is a problem concerning inserting RTF fields into a Word document. A RTF field is pretty much in HTML format and if you insert an RTF field directly into a Word Document you will get HTML in the document. We found we had to write the RTF field out to a file prefixed with <HTML> and then suffixed with </HTML> and then import the file back in. The actual code was in part

Code: 
Dim sPath As String
sPath = CurrentProject.Path & "\Temp.html"
Open sPath For Output As 1
Print #1, "<HTML>" & pprs!What & "</HTML>"
Close #1
What.TextFrame.TextRange.InsertFile (sPath)

That got us the formatting, e.g., bullets, colors, but also some extra lines that weren't in the report version.

An expensive solution to your problem would be to get Acrobat Pro which can convert a PDF to a Word document. I think you will find that you don't lose much if anything in the PDF output of a report and I've heard that Acrobat does a pretty good job of converting PDF to word.
 
Hi Steve,

Thanks for the reply.

I only used the RTF Memo field as I thought that would save me having to format in the detail line with the extra fields I have in the Paragraph table, which is what I *thought* would be a way of doing it at the start of this project. Then I found I could paste the paragraph from Word as is into the memo field.

I could put the paragraphs back to plain text if that made life easier and then format in the Word document.?

I must admit my main stumbling block was how to have multiple records in one document, as the mailmerge, as you state tends to think of one record one letter.

I have found this since my post, but it is based on Excel.

My head is spinning from all of this today, so I am going to stop now and go home.

No doubt I'll be thinking about this all night now and will wake up early tomorrow because of it. :)
 
Gasman,

I haven't done anything with Word MailMerge and Access for many years.

Do you have a set of requirements and test data/database you can share?
Have you looked at any free videos on Youtube re Access data and Word Mail Merge?

Also I found this by Susan Harkins that may be helpful. It uses a Word form, so may not be appropriate.

Good luck
 
Last edited:
Thank you jdraw,

I do not believe a form would be suitable.
Essentially I am trying to have a Word document that would emulate an Access report.
We already have templates, but the management want it this way, unless it is impossible for me to do.

In the bank where I created a system with help from you folks here, even we used templates and deleted/amended paragraphs.

I only have test data in the db at present so I see no reason not to upload.

I'll do that tomorrow.

Thanks for the reply. I saw the number of views and no replies and was thinking I was pretty much on my own with this one.
Sometimes it helps if I can use people for a sounding board and get their views on the problem, providing of course I can describe it sufficiently.
 
Have you considered using bookmarks in your word document? I used it before - got it from a YouTube video. Used late binding and legacy formatting, but it works great. If you are interested, I can dig up the link and post it.
 
Hi Nauticalgent,

I have seen code about bookmarks on here in the past and I might well have to go that route if no better method is worked out.
However I think that that limits the flexibility of the letter.
My reasoning for this is I could put in 20 bookmarks, one for every paragraph I have at present.
What happens if I have to split/add a paragraph to make 21.?
I believe I would then need to have to re edit the template and add another bookmark?

I do appreciate I am asking a lot, but am still looking to make the system as flexible as possible. That was my reasoning behind the report method, more paragraphs, more records, but just sort them in the correct order and that should be it.

I was just caught out that the word version looks nothing as good as the report, even in it's rough layout. That might still be the way to go, but give the user the ability to edit the final set of paragraphs. Perhaps concatenate all the memo fields into one memo field for the report. I can just see that might be awkward to edit?

Right now I am open to all ideas :D
 
I don't know if it would work for you but can you create a table in word and populate each element of the table? Hide table borders etc
 
jdraw said:
Gasman,

I haven't done anything with Word MailMerge and Access for many years.

Do you have a set of requirements and test data/database you can share?
Have you looked at any free videos on Youtube re Access data and Word Mail Merge?

Also I found this by Susan Harkins that may be helpful. It uses a Word form, so may not be appropriate.

Good luck
Click to expand...

Jdraw,

I've attached the DB.
The report is rptletter
I've attached the first letter I am trying to create. Others are very similar.

Many thanks
 

Attachments

Last edited:
CJ_London said:
I don't know if it would work for you but can you create a table in word and populate each element of the table? Hide table borders etc
Click to expand...

I'm not sure CJ_London,

What I am hoping for is someone who has done something like this before, or knows it can be done in a certain way.

That way I can dedicate my time to a definite target.
 
Gasman,

More info/details please.
The rptLetter and the doc are separate entities, correct? I ask because there seems to be a difference in wording on first glance, but I have not gone through the text in detail.

Do you have a sample letter with the data from Access positioned and valued as required?

It appears to me that some of the text in the letter body is meant as a checklist/review point when you are creating a letter, for example:
Insert details of the plan e.g. sum assured/term/premium/options/life assured/type of cover
Click to expand...

– (delete this section if not appropriate)
Click to expand...
amend/delete as appropriate
Click to expand...

Years ago (pre Access '70's and '80's) we used to produce personalized letters based on inputs from "potential vacation travelers". They filled in forms/post cards with preferences, cities, time of year etc. These were processed to create a transaction files with Name/Address, and an indicator/number of the interest (skiing/hiking/swimming/touring...) , cities, season etc.
We had a letters and paragraphs file (templates) that dealt with interests, cities, seasons etc.
There was "boiler plate" text ---greetings, friendly words re visiting our country/region(s), some related (marketing and provider info etc) and then selectable paragraphs based on the numbers on the transactions derived from client mail-in cards. So only the relevant paragraphs, cities etc would be in the "personalized" response. At that time the files were "merged" into personalized and "mechanically signed" digital letters. These were subsequently printed with some "high speed" printers; then the letter and see through envelopes (showing the client address) were physically mailed.
Times have certainly changed, but the underlying logic ( the what is being done) is similar.
 
Last edited:
NauticalGent,

I'd be interested in the link/example re Word bookmarks, late binding , merge Access data etc.
I have also found youtube videos on many subjects to be helpful.

TIA
 
Hi jdraw,

The text in tblParagraphs was copied from paragraphs in the letter I attached. Yes, that is a 'basic' template.
I do not have a letter from Access, but it would have been the report output of rptLetter.

Essentially my directors wanted the user to select paragraphs required. They saw this method on two systems they use already, BUT they are not sufficient for our needs on this project.

My intent was to
Allow the user to select the paragraphs required.
Replace markers in the text with [] with the value of those fields
Output to Word for final adjustment.
I dare say it could all be done within Access, but would take me a hell of a long time. This was a compromise between functionality and time to develop.

However having got as far as I have, I have mentioned to my directors that with the problem with Word, the user would now need to manually adjust text in each paragraph prior to generating the letter in Access.

If that is the case it would be just as easy, if not easier to do this in a proper Word Template dotx file.

They are going to think about this. Their concern was in the past the 'template' was inadvertently amended thereby never giving a correct document to start with, but their templates were only a normal word docx file.

I can amend the templates for word merge fields and generate a query from the data I will have in the DB to supply as much of the variable data as I can and leave the user to select/amend the correct paragraphs.

Consequently unless you view this as a concept to develop, I would ask you hold off for now from spending any more time on this as my directors are not unreasonable and may well take my suggestion.

All my hard work is not in vain as the structure can still be used, but accessed from within Word and not within Access.

jdraw said:
Gasman,

More info/details please.
The rptLetter and the doc are separate entities, correct? I ask because there seems to be a difference in wording on first glance, but I have not gone through the text in detail.

Do you have a sample letter with the data from Access positioned and valued as required?

It appears to me that some of the text in the letter body is meant as a checklist/review point when you are creating a letter, for example:



Years ago (pre Access '70's and '80's) we used to produce personalized letters based on inputs from "potential vacation travelers". They filled in forms/post cards with preferences, cities, time of year etc. These were processed to create a transaction files with Name/Address, and an indicator/number of the interest (skiing/hiking/swimming/touring...) , cities, season etc.
We had a letters and paragraphs file (templates) that dealt with interests, cities, seasons etc.
There was "boiler plate" text ---greetings, friendly words re visiting our country/region(s), some related (marketing and provider info etc) and then selectable paragraphs based on the numbers on the transactions derived from client mail-in cards. So only the relevant paragraphs, cities etc would be in the "personalized" response. At that time the files were "merged" into personalized and "mechanically signed" digital letters. These were subsequently printed with some "high speed" printers; then the letter and see through envelopes (showing the client address) were physically mailed.
Times have certainly changed, but the underlying logic ( the what is being done) is similar.
Click to expand...
 
Gasman,

I'm just tinkering --not really too deeply involved.
Just some food for thought. And, I am not proficient with Word so my terminology and ideas are admittedly suspect from the start.

Where you have things like
delete this section if not appropriate
Click to expand...
, suppose you had a series of word documents (properly named) that dealt with all generic thoughts/concepts. That is, paragraphs that could be included in the letter if required, or excluded if it didn't apply. I think this is along the lines of what your management is saying.

Let me give an analogy:

You have a warehouse that stores individual parts. These individual parts are used in many of the "final/custom" products your company assembles and sells. A Client orders Product XYZ-economy. Product XYZ which is composed of parts A,B,P and T and has options for Trim. Fancy trim is part p1000; medium trim is p200 and economy trim is part p123.

So the Client ordered Product XYZ, the parts collector, in preparation of assembly, goes to the warehouse with a "picklist" to get parts --A,B,P,T and p123.

Another client orders Product XYZ- fancy and for this the picklist is A,B,P,T and p1000.

All of this just to say that having standard parts (paragraphs), and a list of Products (Letters) with their component parts identified can be assembled to meet client needs.

Now I don't know how easy/difficult it is to assemble a final word document from a picklist of word documents(standard parts), but I think that sort of matches your management's approach.

I'm not saying this is how to proceed. I am just looking for options to get to your end requirements.

Good luck.
 
Jdraw,

That is pretty much how I was approaching it in Access, though the permutations are quite large.
That was why I was trying to do the 'easy' decisions and then let the user do the more complicated reasoning.

This morning I had thought of naming each paragraph P1, P2, P3 etc and then using the IsUsed flag create the query with client data and product data.

I would then have to create a crosstab query to get
P1 P2 P3 P4 ... etc

Then in the word document have
{IF P1=True,"This a a paragraph",""}

Whilst I do not envisage the number of paragraphs changing dramatically, any increase would mean revisiting the document and adding more code.

I will await my director's decision and that will dictate how to proceed.
 
My view was some form for user who was creating/assembling specific LetterType(s)
Person could work with a Form and select LetterType, then subform with Paragraph numbers and text. Implication is someone(s) have pre-created/edited/spell-checked the paragraphs.

They could check a text box or select multiple entries from a listbox and identify which Paragraphs should be included in a LetterType. They should also be able to identify the sequence number for that paragraph in that LetterType.

If there was additional customization required, they could copy closest existing LetterType, give it a new number and name, then adjust which paragraphs should be included/excluded and the respective sequence in the letter.

While I have shown Paragraphs in Access, they could be links/folders to word documents/sub documents.

Just some additional thoughts for consideration for concept/approach options. I don't mean to cause any additional work--just looking at the issue from 30000ft. I like to get a big picture before jumping into Access/database physical.

Experience suggests - if volumes warrant - give user a select type option (select an entry, check a box..). Don't let users enter free form text necessarily.

Additional info: I found this link to a merge by Albert Kallal. I have seen it mentioned over the years. I do not have any experience with it.
 
Last edited:
Thank you jdraw,

That was pretty much my approach. You had in fact helped me with the two listboxes in frm UserParagraph to select required paragraphs. I had not got around to reordering as the order was pretty stable, but was thinking of it in stage two. Stage one was get it working albeit roughly.
The export to word not working pretty much took the wind out of my sails. :D

Now looking at it from a mailmerge prospective and a query to support that.
Also not heard back from the directors yet either.
 
Last edited:
You could mock up the basic logic and test before getting too involved in details/volumes/format.
Getting the logic/processing steps identified and working with a few test strings may be a valuable use of time.

Again, not trying to create meaningless extra work.
 
jdraw,

I'm off on holiday next week, so this will be looked at on my return.
We do appear to be thinking along the same lines though, or so I believe, as I have got that far in a skeleton format already
 
Good stuff. Enjoy your holiday.
 
You must log in or register to reply here.

Similar threads

Dick7Access
Font Size
Replies
5
Views
791
Dick7Access
Dick7Access
G
Greetings - Have you ever had an idea that you wish you didnt?
Replies
4
Views
1,030
The_Doc_Man
The_Doc_Man
jwcolby54
Using ChatGPT as a personal editor
Replies
0
Views
692
jwcolby54
jwcolby54
Juice
Printing html file to a specific printer
Replies
4
Views
757
arnelgp
arnelgp
S
Is there a way to have print/save options for filtered form without utilizing right click?
Replies
10
Views
1,080
Gasman
Gasman

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom