Use Value in Word calculated with VBA in Access

I've to use a Microsoft Access Database to create different bulk letters in Microsoft Office Word. This works just fine in most cases but it is somehow not possible to use a View (which is defined in Access) in Word as long as the source for data contains a column that is calculated by VBA code in the Access Database. Ah, and I need exactly this calculated value to put it into a Microsoft Office Word Field.

There is unfortunately no way to do this calculation in SQL so I need a solution how I can use those Views as the source in MS Word.

I found just one way yet: Export the View from Access into a Excel Worksheet and use this as the source in Word. As you can think this is very unusable :-(.

(We use Microsoft Office 2003)

Cheers, Gregor

The solution is to use some access word merge code that outputs the query as a text file and then launches the word template and points the template to that intermediate text file.

There are many advantages to the above approach. For one, you don't let word attached to ms-access, so the whole approach is more stable (one application if it freezes up will not affect the other as easy). If you using sql server, or even a workgroup secured access database, it don't matter because the access code is producing that intermediate file. So, even for SQL server, Oracle, MySql etc the word merge will continue to work since we producing a intermediate text file. So, the same merge system works for JET, MySql, Oracle, SQL server, and things will work REGARDLESS of the security settngs on the database.

Also using a intermediate file also means you don't have to resort to some bookmark example which usually means you have to write new code for every merge (that makes no sense!). And, bookmarks are hard to see and insert into the word document.

Another bonus here is word users can continue to use their training courses and teaching and books and on how to setup a word merge document. Another advantage to using merge fields is they allow live preview of the data during editing and composing of the word template document. And, the final resulting merge document does not have any special codes or fields in it.

I have a working sample here that allows you to word enable any form with ONE line of code. This Super easy word merge system then takes over.

http://www.members.shaw.ca/AlbertKallal/msaccess/msaccess.html

Just scroll down in above until you reach the Super Easy Word merge.

The above will allow your VBA expressions in your query also to be used in the word merge.

I have to agree that life gets so much easier with a text file.

Remou 2010-09-11 18:03:05

I really like this solution but I've used for now apenwarr's solution as it is easier done and I do not need a bullet proofed implementation. Thank you. *up vote*

Gregor 2010-09-11 18:53:46

I've used Albert's solution, but only in a heavily altered form. I don't like the dependence on data loaded in a form in Access, to be honest, and when I used it, I was forced to create a form just for that purpose. I don't recall why I couldn't use the existing form (it was a subform, but I don't think Albert's code couldn't use a subform), nor do I remember why my usual temp table approach didn't work. But I felt constrained by Albert's tool and had to make considerable alterations, as it assumed more than was useful in my case.

David-W-Fenton 2010-09-11 19:34:40

There is a useful post here: http://www.tek-tips.com/faqs.cfm?fid=5088 I have used a stripped down version more than once.

Remou 2010-09-11 19:48:58

There is only dependency on the current form by default to save coding (the one command takes all un-bound + bound columns text boxes on the current form and creates that intermediate file (so, you can have un-bound comments that get sent to the word doc). However, if you use MergeAllWord "some sql here", then there is no dependencies on the current form. Options for the merge including setting table (sql), setting and saving output document name, printing it. Options can be found here:http://www.members.shaw.ca/AlbertKallal/wordmerge/page2.html

Albert D. Kallal 2010-09-12 16:48:40

Have you updated that, Albert? I grabbed it years and years ago, so maybe you didn't have the SQL option then. Or maybe I just didn't read the code carefully enough.

David-W-Fenton 2010-09-12 22:33:42

The option to use any sql been there quite early on – about since 2003? Options to specify and save the template without printing, or to save + print without viewing the template was added in later years. The allowing of unbound text boxes to appear as merge columns in the word template was added in 2005 (surprsing, but this was a common request that un-bound text boxes such as some comments box on the form appear in the template merge).

Albert D. Kallal 2010-09-13 10:19:03

Remou 2010-09-11 18:32:13

I think it rather depends on where the mail merge is being triggered from. If it's from Access, I'd do what @apenwarr suggests, and prepopulate a table for the mail merge to use as its data source. I wouldn't use a MakeTable, but use a persistent table that I empty and repopulate as needed. Indeed, nearly all of my apps have exactly this structure already. If you're initiating the merge from Word, though, it's not so simple. Some logic can be replicated in Word fields with If/Then/Else logic, but that's really inconvenient (because of how hard it is to edit them).

David-W-Fenton 2010-09-11 19:27:08

ansaurus

tags:

views:

answers:

Use Value in Word calculated with VBA in Access

related questions