Norbert,
Here's the basic structure that I'm using.
1. All MS Word templates are on a shared drive to enable all users to access them.
2. The full, dynamic, path of the location of the templates is in a single record table all by itself. This allows for the template location to be moved at any time and only one record needs be updated. Whenever a template is needed or its document is saved, the dynamic path is concatenated to the front of the file name to get or save the file.
3. The names of the MS Word templates live in another table and are selected from a drop down list by the user when needed. The template table has a 1 to many relationship with a child table that lists the template fields to be replaced and the table and field within the table used to replace the template field. Taking this approach allowed me to cut code that always knows how to personalize every letter and to do so in a manner such that once built, the user has total control over what should be personalized in each letter and what data to use to do so.
4. Whenever a letter is sent, there needs to be an audit trail so the physical file name of the letter that's been created is stored in an audit trail table that has a many relationship to each letter's recipient. This allows for multiple letters to be sent to the same individual.
5. This letter's file name is displayed in a text box with the ability for the user to double click on it when desired to open it via a shell function. Just in case, this works with any type of file as long as it's file extension has an association with an application on the PC that will open it. In my case, I can store Word documents as well as *.pdf, *.jpg, and *.msg files if the users wish.
6. With my database structure where I have a primary table with 5 related tables with a many relationship to the primary I had to allow for letters to be sent to groups of people from groups of people to to individuals assocyaed with either group. The subject of the template controlled this so the template table has a few extra fields that I use to determine if, when building a letter, it goes to everyone at once or a separate letter goes to each person individually.
I mentioned some "gotcha's" so here they are.
1. Every field in a MS Word template that I want to replace with data from the database is bookended with percent signs. Therefore the actual name of a person to whom thne letter is being sent might be "Dear %recipient%," (no quotes) in the Word document. You'll see how the code does the replace a bit further down.
2. At first I left the Word documents open so the user could make manual changes. I discovered that if I did that, and even though I could make the changes and successfully save the document, I couldn't open it later. MS Word said that it couldn't open the file. Therefore the Access code opens the Word template, does the personalization, saves the document, quits Word, sets the object to nothing, and then creates the audit record with the save file
Here's the basic structure that I'm using.
1. All MS Word templates are on a shared drive to enable all users to access them.
2. The full, dynamic, path of the location of the templates is in a single record table all by itself. This allows for the template location to be moved at any time and only one record needs be updated. Whenever a template is needed or its document is saved, the dynamic path is concatenated to the front of the file name to get or save the file.
3. The names of the MS Word templates live in another table and are selected from a drop down list by the user when needed. The template table has a 1 to many relationship with a child table that lists the template fields to be replaced and the table and field within the table used to replace the template field. Taking this approach allowed me to cut code that always knows how to personalize every letter and to do so in a manner such that once built, the user has total control over what should be personalized in each letter and what data to use to do so.
4. Whenever a letter is sent, there needs to be an audit trail so the physical file name of the letter that's been created is stored in an audit trail table that has a many relationship to each letter's recipient. This allows for multiple letters to be sent to the same individual.
5. This letter's file name is displayed in a text box with the ability for the user to double click on it when desired to open it via a shell function. Just in case, this works with any type of file as long as it's file extension has an association with an application on the PC that will open it. In my case, I can store Word documents as well as *.pdf, *.jpg, and *.msg files if the users wish.
6. With my database structure where I have a primary table with 5 related tables with a many relationship to the primary I had to allow for letters to be sent to groups of people from groups of people to to individuals assocyaed with either group. The subject of the template controlled this so the template table has a few extra fields that I use to determine if, when building a letter, it goes to everyone at once or a separate letter goes to each person individually.
I mentioned some "gotcha's" so here they are.
1. Every field in a MS Word template that I want to replace with data from the database is bookended with percent signs. Therefore the actual name of a person to whom thne letter is being sent might be "Dear %recipient%," (no quotes) in the Word document. You'll see how the code does the replace a bit further down.
2. At first I left the Word documents open so the user could make manual changes. I discovered that if I did that, and even though I could make the changes and successfully save the document, I couldn't open it later. MS Word said that it couldn't open the file. Therefore the Access code opens the Word template, does the personalization, saves the document, quits Word, sets the object to nothing, and then creates the audit record with the save file
From: "drnorbert@msn.com [MS_Access_Professionals]" <MS_Access_Professionals@yahoogroups.com>
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 20, 2014 8:23:14 AM
Subject: Re: [MS_AccessPros] Convert reports (letters with if conditions) from Access 2013 to Word 2013
--
To: "MS Access Professionals" <MS_Access_Professionals@yahoogroups.com>
Sent: Saturday, December 20, 2014 8:23:14 AM
Subject: Re: [MS_AccessPros] Convert reports (letters with if conditions) from Access 2013 to Word 2013
Hi Jeffrey,
Thanks for your explanation.
Yes is exactly what I need.
Can I get in touch with you?
Much regards
Norbert
--
---
Jeffrey Park Jones
Excel, Access, Word, Office Expert
919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net
Jeffrey Park Jones
Excel, Access, Word, Office Expert
919-671-9870
5109 Deer Lake Trail
Wake Forest, NC 27587
jpjones23@centurylink.net
__._,_.___
Posted by: jpjones23@centurylink.net
| Reply via web post | • | Reply to sender | • | Reply to group | • | Start a New Topic | • | Messages in this topic (7) |
.
__,_._,___
Tidak ada komentar:
Posting Komentar