UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Formatting Data For A Word Mail Merge, Access 2016    
 
   
dflak
post Jan 1 2018, 07:32 PM
Post#1


Utter Access VIP
Posts: 6,056
Joined: 22-June 04
From: North Carolina


I posted this in the MS-Word forum of the site. It turns out that it’s not a Word issue, but an MS-Access issue. So don't respond there. Respond here.

I am attempting to use the attached database with MS-Word MailMerge.

Let me explain what I am trying to do. I have another database. It is a simple "flat" database that basically has Name, Phone1, Phone2 and Phone3 as its fields - so the database assumes that people will have at *most* three telephone numbers associated with them. I use these 4 fields in a mailmerge and it comes out nicely since if there is no data for phone2 or phone3, the mailmerge skips them.

My new database has the phones in a separate table linked to the main table by a foreign key. I have a query that uses a UDF to get all the phones associated with the main key and concatenates them with a line feed between them. This way a person can have as many phones as needed but they all show up as single entry in a single column for the purposes of the mailmerge. That is the essence of it. I have other UDFs in the same query that collect family members, dates (like birthdays and anniversaries) and general comments.

The problem is that I cannot use this query as the data source of the mailmerge because of the UDFs. UDFs inside Access are unknown to applications outside Access. The mailmerge function doesn’t even show them and other applications (such as MS-Query) can “see” them but cannot access them.

My work-around for the moment is to run the query in MS-Access and export the results to an Excel workbook and use the workbook as the data source for the mailmerge. The results are exactly what I want. But I would like to accomplish them in fewer steps and without opening Access at all if possible.

So, I can think of two things to do:
1. Figure out a way to do this without UDFs (I haven't got a clue how to do this)
2. Find a way to automate my workaround preferably saving the query results as a temporary file in the Access database and use the temporary file as the mailmerge data source.

I am fairly certain I can write code to produce the temp file. However, I don’t know of a way to trigger it automatically. Is there such a thing as a “Before Database Close” or “On Database Save” event? I would rather not have to remember to do this



Attached File(s)
Attached File  Contacts_Project.zip ( 104.92K )Number of downloads: 5
 

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
MadPiet
post Jan 2 2018, 12:07 AM
Post#2



Posts: 2,314
Joined: 27-February 09



One way of doing this is to have a table that you populate with one or more append queries and then use it as the source of your mail merge. Then the whole UDF thing becomes a moot point. If you do the standard SetWarnings thing, you could shut off warnings for the append queries. Then just run all that. Then when your data is all in the table, run the merge. Finally, when the merge is over, truncate the table. Of course, you'd have to use Compact On Close with your database because it would bloat...

Oh wait a minute... Find Albert Kallal's "Super Easy Word Merge" stuff. See if that will do what you want. In a word, he runs a query in Access and exports that to CSV (I think), and then he runs the merge from Word (using Automation), and then just uses KILL to delete the exported CSV once the merge has completed.

http://www.kallal.ca/msaccess/msaccess.html
This post has been edited by MadPiet: Jan 2 2018, 12:13 AM
Go to the top of the page
 
MadPiet
post Jan 2 2018, 06:51 PM
Post#3



Posts: 2,314
Joined: 27-February 09



One way of triggering the file creation/export is to add some code to the Close event of a hidden form (I guess you could open the form hidden when the table populating takes place.) Then if you dump the data to a delimited text file, you can just use Albert Kallal's Super Easy Word Merge code on his website, and you should be sorted.
Go to the top of the page
 
dflak
post Jan 3 2018, 08:35 AM
Post#4


Utter Access VIP
Posts: 6,056
Joined: 22-June 04
From: North Carolina


Thanks for the tip. I will probably not get back to this project until the weekend. I'll keep you posted.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
dflak
post Jan 5 2018, 12:38 PM
Post#5


Utter Access VIP
Posts: 6,056
Joined: 22-June 04
From: North Carolina


I finally got the couple of minutes I needed to test out a couple of things. When I bring the database up, it automatically launches the Address Index "Menu" I added code to the Form_Unload event. This triggers when the user closes the form or when the database is closed. The version the user will get won't allow access to the forms tables, etc. So if the form is closed, the user will have to close the database and launch it again. Either way, the code gets executed before the database gets closed.

With as few records as I plan to have, it should only take seconds to make the table I need. The only thing that won't work is if the user updates the database, leaves it open and expects the mailmerge to work. I'll just tell them not to do that smile.gif.

Maybe as a future project, I'll work on the word side. I'll have the program triggered by an OnOpen event in Word, have it find the database (assumed to be in the same directory), open the database, run the macro and close the database. Then the user can play with the template and move the fields around or use them in any way. One Office Application at a time: I already have Excel under control and I am working on Access.

The bottom line is that the Form_Unload event on a form that is launched automatically on database start does what I want.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
JonSmith
post Jan 5 2018, 02:25 PM
Post#6



Posts: 3,227
Joined: 19-October 10



Dan, I already built something very similar to what you suggest using bound data fields which were introduced in 2007. In 2008 they got even better being able to handle repeating data aswell as rich text.

Making the xml the data is bound to was quite a pain in 2007/2010 as it could only be done in VBA. 2013 is better since there is more GUI functionality.

I created a Word addin to further enchance this allowing you to edit the xml and get or set values on the fly. It can also be used to create the bound data controls. Its all very simple to use and non-programmers have used it where I work to edit templates.

To get the data its really easy, I made a class for it in VBA. You can get or set individual fields or you can generate the xml you want and push that to the doc.

I did post it to the code archives but the mods didnt seem interested and afaik no-one reviewed it.


I personally despise mail merge. I think its outdated and clunky now, if you are interested in seeing my new method let me know and I'll upload an example.
Go to the top of the page
 
dflak
post Jan 5 2018, 02:45 PM
Post#7


Utter Access VIP
Posts: 6,056
Joined: 22-June 04
From: North Carolina


I am always interested in options. I have to admit, that your explanation sounded mostly Greek to me. However, if I see something concrete, I'm pretty good at disassembling it to see what makes it tick smile.gif.

The main issue is that at the moment, my time is limited and this is a low-priority, I'm-in-this-for-the-learning project, so please understand if I don't get back to you for a while.

--------------------
Dan

One spreadsheet to rule them all. One spreadsheet to find them. One spreadsheet to bring them all and at corporate, bind them.
Please zip and attach samples. It makes life easier for those who have to figure out what you are trying to do. Thanks
Go to the top of the page
 
JonSmith
post Jan 8 2018, 08:45 AM
Post#8



Posts: 3,227
Joined: 19-October 10



Oke, here it is.

The screenshot shows how I manage the fields in Word. I can add any field I want to on the file and create repeating groups (so in your example 'Address' is a repeating group and 'AddressLine1' is a data field in that repeating group).

I can use the UI I created to then add bound controls to the document. If the same field is required in multiple places they all stay in sync with each other and changing one changes them all instantly.

What is also really nice is that the values in this data can easily be read back in VBA aswell as written to (So far I am unaware of any other structured way to read data out of Word).

If you look at the folder 'Example Usage' you will see a simple Word template file with some bound XML fields aswell as a document created with that template containing some data.
In the same folder is an Excel file which demonstrates Pushing data to the Word Template and saving the copy aswell as pulling data out of an existing document.
I build a class to manage it so it can easily be dropped into any file. My class in my Word addin to manage the XML is bigger and more advanced as it has to do more complicated work. You can find the whole thing in the 'Word Addin' folder.
Attached File(s)
Attached File  Screenshot.png ( 49.73K )Number of downloads: 3
Attached File  Custom_Word_XML_fields.zip ( 163.91K )Number of downloads: 2
 
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    21st January 2018 - 03:38 PM