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
> Code To Close Database Aafter Mail Merge, Access 2016    
 
   
dflak
post Jan 6 2018, 01:23 PM
Post#1


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


I haven't quite scrambled to the the shoulders of the giant yet. With the help of my friends on this board, I have been able to develop a Contacts List in MS-Access. I want to use this contact list as a data source for a mail merge.

My ultimate goal is to hand a user a zip archive that contains 3 files: A word or PDF document containing instructions, the word mail merge template and the Access Database. The user should be able to run the access database using a free, run-time only license. The instructions include that the mail merge template and the database be in the same folder.

What I want to do is have the user open the mail merge template, have it connect to the data source and perform the mail merge, all automatically. I have accomplished that.

What I need is cleanup. If you download both of the files in the zip archive and open the word document.

First, it brings up the dialog box warning you that it wants to connect to a database. I have application.displayalerts = FALSE as the first thing in the on open document event but that does not seem to do any good. I would not like this alert to show.

Next, produces the merged document - this is the desired result.

However the database is now open. I'd like to close it.

Also the mail merge template is also open. I'd like to close it too. I notice that the database closes when the template is closed, so maybe these are the same fix.

Also, the application ends with a dialog box inviting the user to save the merged file. I would like to suppress that as well. I would not mind saving the document to the current folder with a date stamp in the name, but that is optional. I want the mail merged document open and on top and the template and the database closed.

Surprisingly, there isn't a lot of code to this.


Attached File(s)
Attached File  Contacts_List.zip ( 165.46K )Number of downloads: 4
 

--------------------
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 6 2018, 02:40 PM
Post#2



Posts: 2,316
Joined: 27-February 09



I'm going to test a slightly different alternative... I want to see what happens if I use Albert Kallal's Super Easy Word Merge code to do it. (He outputs the recordset to a CSV, then uses that as the source for the merge and then KILLs the file he created.)
Go to the top of the page
 
moke123
post Jan 6 2018, 04:12 PM
Post#3



Posts: 1,203
Joined: 26-December 12
From: Western Ma.,L.I.,N.Y.,Jupiter,Fl.



Albert's Word merge code has endured for 20 years with apparently very little modifications and has worked flawlessly in every project I've included it in.
Given that, I'd take every word he says on the matter as gospel.

scroll down to "Don't let word attached to your running mdb file. " in this link...wordmerge
Go to the top of the page
 
dflak
post Jan 6 2018, 04:35 PM
Post#4


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


Just to let you know what the very final product will look like.

Right now when the user closes the main menu either by closing the form or by closing the database, the code makes the temp file that is used for the mail merge. If this turns out to be a CSV file, that's OK too.

What could happen and I would like to prevent, is that the user updates the database, does not close the form or the database, runs the mail merge and expects the new data to be there.

So, my plans are to transplant the code that writes the temp file to the Word document. So regardless of the database status, when the person opens the Word Document, it opens the database and makes the temp table before doing the mail merge. I already have some sample code that claims to do something similar. I think I'm learning enough to be able to untangle it.

Heretofore, I have not played a lot with MS-Access. I'm learning to swim by jumping into the deep end of the pool and am depending on the lifeguards at UA to help me out. I am happy at how well I am able to tread water at the moment.

I'll make it a point to get off my hamster wheel for a while and catch up on reading the references.

Thanks for all the help, and when I do get this project done, I will make sure to give credit to UA.

The real ultimate end is to use this product as a "loss leader" - when I retire in a couple of years, I intend to hire myself out as an Excel / Access / Technical Writing mercenary - I'll use this as evidence for two of the three. I'll "attach" it to my resume.

--------------------
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 7 2018, 10:03 PM
Post#5



Posts: 2,316
Joined: 27-February 09



Sorry, but I don't understand this:
"What could happen and I would like to prevent, is that the user updates the database, does not close the form or the database, runs the mail merge and expects the new data to be there."

You could force a form to close by using the form's OnTimer event - once enough time passes, the form is closed (use DoCmd.Close...). You could also call the mail merge. You could store the fact that the merge ran in a boolean variable, and set it to True when the code runs. If it has not run by the time the form is closed, it could be run in the form's Close event.

As for setting focus to the Word application/document at the end... I suppose you could do something like

appWord.Activate
appWord.Documents("MergedDocument").SetFocus

There's code on AccessWeb, I'm almost sure. I remember reading it over the past 20 years or whatever.

Being that I'm super lazy, I'd absolutely use Albert's code. Just leave the copyright in there. =)
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd January 2018 - 09:12 AM