JimDantin
09-30-2011, 05:51 AM
I've been lurking here for a while and have searched for previous posts that might help me, to no avail. So, I'm seriously asking for help now. I'm in the middle of a complex project and need some code. I'm even willing to pay if it comes to that!
The project is an automated report writer. Data is stored for customer information and for the specifics of the incident that we are reporting. The incident is tracked and data entered through normal Access forms and tables.
A final report is needed that explains what has happened, actions taken, and recommendations made. This is NOT a normal rows and columns type of factual report that Access can generate -- it is a NARRATIVE written for legal processes and MUST be in the form of a Word document. The end result is dozens of pages long and contains a compilation of standard text paragraphs, custom edited paragraphs, and many appendices.
The logic works like this:
Query the incident data - incident number, type of incident, client data, analyst's data ...
Decide on the type of case this is, select the proper type of report to generate, based on the case data.
Present the analyst with recommended paragraphs of text from a table of pre-written paragraphs. These paragraphs apply to certain incident types and conditions and the system selects the appropriate segments from a dozen or so variables. The text segments have been previously vetted and approved for legal and grammatical accuracy - that's why we're using them.
The selected text segments are stored in a temporary table, one record per paragraph. Each paragraph has related data that identifies the report section, the paragraph Style, and the order that the text is to be written in the section.
The user can add custom text paragraphs to provide details about the incident.
Note: everything up to here is working. Now we get into what I need help with. . .
:banghead:
We need to embed placeholders (variables, symbols, bookmarks . . . whatever term makes sense) into the stored text segments. These placeholders will be replaced with actual incident data when the report is generated. Example: "Dear <customername>, we have analyzed the event that occurred on <eventdate> ..." This is similar to Word's Mailmerge, but driven by Access. We don't want to actually use Mailmerge for a variety of reasons.
Once we have selected the appropriate text segments, we need to create a Word 2010 document (from Access 2010), using the appropriate Word template (we'll have a cover letter template, various report type templates, etc.) and using Word's paragraph Styles to ensure consistent formatting.
I need Access routines (macros, functions, subroutines, etc) that will do the following:
1. Create a new Word document using the template specified (I will then generate whatever header, footers, and report introduction sections are needed) Pretty basic so far . . .
2. Loop through the table of text . . .
3. At the start of each section, write out a paragraph with the section heading name as the text, using a specified style for the heading paragraphs (style names are stored in the template - we can standardize on a heading style name)
4. Process the text paragraph, replacing the placeholder with the actual incident data (<customername> becomes "Joe Smith") and write the paragraph to the Word document with the appropriate Style.
5. Continue through each paragraph in the Section, and repeat for the remaining sections
6. Close the document and save to a file name and folder that is stored in the data record.
It would be nice to somehow handle small tables for sections of the report. Sometimes data can't be effectively presented in a narrative, and we'd like to do some formatting. This may be impossible.
I do NOT want to resort to hard-coded VBA that cycles through a list of Case statements for each placeholder name. The placeholders can be designed to represent field names -- <customer name> = [customername], or we could have a lookup table with the placeholders and filed names.
The code must handle the addition of new placeholders to the system without editing the code -- the logic must work from the naming structure or be driven by the placeholder lookup table. There will be many more placeholder names than are actually used in any given text segment, or report. Some segments will have no placeholders, others may have multiple placeholders or the placeholder may appear multiple times in the same placeholder.
The queries that feeds the routine will, of course, need to be modified for different report types or changes in placeholder logic. Assume that the query feeding the routines is one record long. Missing data should be handled gracefully -- we could use a standard {MISSING DATA} type of message in the output document wherever needed.
Hopefully you can see the power and flexibility of a reporting system like this. I would like to collaborate with someone(s) on this part of the project and am willing to share concepts and techniques.
Thanks
Jim
The project is an automated report writer. Data is stored for customer information and for the specifics of the incident that we are reporting. The incident is tracked and data entered through normal Access forms and tables.
A final report is needed that explains what has happened, actions taken, and recommendations made. This is NOT a normal rows and columns type of factual report that Access can generate -- it is a NARRATIVE written for legal processes and MUST be in the form of a Word document. The end result is dozens of pages long and contains a compilation of standard text paragraphs, custom edited paragraphs, and many appendices.
The logic works like this:
Query the incident data - incident number, type of incident, client data, analyst's data ...
Decide on the type of case this is, select the proper type of report to generate, based on the case data.
Present the analyst with recommended paragraphs of text from a table of pre-written paragraphs. These paragraphs apply to certain incident types and conditions and the system selects the appropriate segments from a dozen or so variables. The text segments have been previously vetted and approved for legal and grammatical accuracy - that's why we're using them.
The selected text segments are stored in a temporary table, one record per paragraph. Each paragraph has related data that identifies the report section, the paragraph Style, and the order that the text is to be written in the section.
The user can add custom text paragraphs to provide details about the incident.
Note: everything up to here is working. Now we get into what I need help with. . .
:banghead:
We need to embed placeholders (variables, symbols, bookmarks . . . whatever term makes sense) into the stored text segments. These placeholders will be replaced with actual incident data when the report is generated. Example: "Dear <customername>, we have analyzed the event that occurred on <eventdate> ..." This is similar to Word's Mailmerge, but driven by Access. We don't want to actually use Mailmerge for a variety of reasons.
Once we have selected the appropriate text segments, we need to create a Word 2010 document (from Access 2010), using the appropriate Word template (we'll have a cover letter template, various report type templates, etc.) and using Word's paragraph Styles to ensure consistent formatting.
I need Access routines (macros, functions, subroutines, etc) that will do the following:
1. Create a new Word document using the template specified (I will then generate whatever header, footers, and report introduction sections are needed) Pretty basic so far . . .
2. Loop through the table of text . . .
3. At the start of each section, write out a paragraph with the section heading name as the text, using a specified style for the heading paragraphs (style names are stored in the template - we can standardize on a heading style name)
4. Process the text paragraph, replacing the placeholder with the actual incident data (<customername> becomes "Joe Smith") and write the paragraph to the Word document with the appropriate Style.
5. Continue through each paragraph in the Section, and repeat for the remaining sections
6. Close the document and save to a file name and folder that is stored in the data record.
It would be nice to somehow handle small tables for sections of the report. Sometimes data can't be effectively presented in a narrative, and we'd like to do some formatting. This may be impossible.
I do NOT want to resort to hard-coded VBA that cycles through a list of Case statements for each placeholder name. The placeholders can be designed to represent field names -- <customer name> = [customername], or we could have a lookup table with the placeholders and filed names.
The code must handle the addition of new placeholders to the system without editing the code -- the logic must work from the naming structure or be driven by the placeholder lookup table. There will be many more placeholder names than are actually used in any given text segment, or report. Some segments will have no placeholders, others may have multiple placeholders or the placeholder may appear multiple times in the same placeholder.
The queries that feeds the routine will, of course, need to be modified for different report types or changes in placeholder logic. Assume that the query feeding the routines is one record long. Missing data should be handled gracefully -- we could use a standard {MISSING DATA} type of message in the output document wherever needed.
Hopefully you can see the power and flexibility of a reporting system like this. I would like to collaborate with someone(s) on this part of the project and am willing to share concepts and techniques.
Thanks
Jim