Automate a very complex task? 2

[thirty4d]

I know this is a long shot, but what have I got to lose but ask! I'm desperate

Pretend for demo purpose, I have 5 Word documents that consist of merged fields via automation where the fields and data came from a make-table query called ToolAssemblyID. (It's a lie, in reality we have hundreds!).

These documents originally were created by the function MergeIt() from the master document called 5640 and Saved As under a new filename.

Where Filename derived from: Partnumber and Operation Sequence, which are fields in the database. (Example: 1710-10 020).

Of these 5 Word documents, 4 documents have a merged field called txtInsertDesc and have been modified in Access database.

Therefore, existing document(s) no longer have the accurate data and need to be updated.

Is there such code that would possibly open each document and perform a mass update without rerunning the qryLayoutProfile?

qryLayoutProfile is a parameter query that prompts the user for unique Partnumber and Operation Sequence to compose an Assembly.

Any leads will be appreciated

 

[MichaelRed]

I don't think so, however you might -if you know the document named vs parameter lists parameters (and it appear that you do) jus t re-run the entire sequence. Just set up a table with the parameters listed for each document and re-run the process, re-creating the Documents.

As an alternative (to avoid the maintenance issue), generate the "documents" as Ms. Access reports. Let the report be the document. Any changes to the documents would be implemented in the reporting scheme, and each time a "document" was pulled up, it wopuld automatically be updated.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[tangram]

I Have to say that using Word like this sounds a bit crazy thirty4d. Using a word processor as a database reporting tool? If the documents have to be reported on using a Word document format, have you thought of embedding an Excel table within the document. If the Excel table has taken the data from the database then every time you open the document, the spreadsheet will do all the updating for you.

I have to agree with MichaelRed; a completely different approach to the reporting system would be better I'm sure.

 

[thirty4d]

To MichaelRed and tangram,

Thank you both for your feedback. Unfortunately, that's the system that my company already have in place (that is using Word as their documentation but manual creation).

To MichaelRed: Yes, that is what I've got set-up right now is to re-run the query and fire the code that executes the entire process. I thought it was poor programming on my part but glad to hear that suggestion from an expert!

I have honestly, considered using Access report as one of my option but reluctant as it might bloat the database. After all I still have to save this report somewhere else without losing the fancy formatting.



Thanks Again very much!

 

[tangram]

Legacy systems... don't ya love 'em. NOT!

Do you do VB thirty4d? If fancy formatting is the reasoning behind the use of Word then can I suggest a VB app, on a scheduler, running Crystal Reports exporting (as Ver7 and presumably later versions do) to a Word document?

I’ve not done this before as I always export reports in HTML format for our Intranet, but I’ve just tried it with an export of one of my sales figures reports into Word format and it works very well. Once written the whole process could be automated and run as often as you like without having to lift a finger.

Doing the reporting in Crystal wouldn’t affect the size of the Access database itself at all either.

 

[thirty4d]

To tangram,

I too have never used Crystal report as we don't have one at work, but if that would do the trick I could request one. (hmmmm........guess have to do more reading on cyrstal report).


Really appreciate the feedback!

 

[MichaelRed]

having the report(s) in Ms. A. shouldn't bloat the db. A report APPEARS to be sufficient to accomodate what you'e doing - the format is the same, and the query is the same. Just the parameters (for the query) need to vary. inputting these should not be an issue, or even setting up the table to run them in batches. If you MUST have them in some "permanant" form, ask Ms. A to export them in RTF format. It is geneally acceptable for the more or less plian reporting formats, which -since this appeary to be truly ancient- I would asssume is acceptable.

MichaelRed
m.red@att.net

There is never time to do it right but there is always time to do it over

 

[thirty4d]

To MichaelRed,

Thanks for another suggestion. I have actually tried the RTF as one of the option but if you see the complexity of my database, I think that you would understand why RTF just won't suffice.

Do appreciate the info about using Access report.