Mailmerge from Access with function

[Gasman]

Hi all,

In this Letter Project I am now trying a mailmerge.
I created a function to determine whether the name in the letter would be

Mr Joe Blogs
or
Mr Joe Blogs & Mrs July Blogs

Works fine in the query qryMergeFields.

When I go to open the Word document that uses Select * from qryMergFields it complains about my function Covernames? Pic attached.

I would have thought that all Word wanted was data, and not concerned of how it was created?

Is there a workaround other than IIF is possible?

My SQL is

SELECT tblLookup.LookUpValue AS Title, tblClient.ForeName, tblClient.MiddleNames, tblClient.Surname, tblClient.Address1, tblClient.Address2, tblClient.Address3, tblClient.Address4, tblClient.Address5, tblClient.PostCode, tblProvider.ProviderName, Format([SumAssured],"Currency") AS CSumAssured, tblLookup_1.LookUpValue AS CoverType, tblClientProduct.CoverTerm, Format([Premium],"Currency") AS CPremium, tblClientProduct.OldCover, tblClientProduct.OldProvider, Format([Disposable],"Currency") AS CDisposable, tblClientProduct.SurvivePeriod, tblClientProduct.Event, CoverNames([tblClient].[ClientID]) AS ClientCover
FROM tblProvider INNER JOIN (tblProduct INNER JOIN ((tblClient INNER JOIN tblLookup ON tblClient.Title = tblLookup.ID) INNER JOIN (tblClientProduct INNER JOIN tblLookup AS tblLookup_1 ON tblClientProduct.CoverType = tblLookup_1.ID) ON tblClient.ClientID = tblClientProduct.ClientID) ON tblProduct.ID = tblClientProduct.ProductID) ON tblProvider.ID = tblProduct.ProviderName;

as Titles are in a lookup table and the ID in the client table.

TIA

 

[Ranman256]

I noticed Word doesn't like home grown functions.
So Either:
Do the ' merge' in Access via report
Or
Make a report table, append the data using your function, to create the data, THEN Word merge on this table.

 

[Gasman]

I noticed Word doesn't like home grown functions.
So Either:
Do the ' merge' in Access via report
Or
Make a report table, append the data using your function, to create the data, THEN Word merge on this table.

Thank you Ranman256,

The report option is not workable, which is why I am looking at the mailmerge option now.
I'll create a table for the query results and merge from there.

 

[sneuberg]

I tried to replicate your problem and I'm running into different symptoms. In my case whenever I try to create a Word Merge document from Access and use a query with a function I defined in a module it gives me an error message titled:

ODBC Microsoft Access Driver Login Failed

The error message was:

Could not find the file 'C\Users\sneuberg\Documents\Access Projects.mdb.'

When I googled the error message title I found some suggestions about using DDE as a connection but I could get that to work.

Anyway when I use built in functions I don't encounter this problem so you could probably solve this by using IIF. What's your object to using IIF?

Sometimes if the test is for Null you can use Nz in place of IIF.

 

[Gasman]

I tried to replicate your problem and I'm running into different symptoms. In my case whenever I try to create a Word Merge document from Access and use a query with a function I defined in a module it gives me an error message titled:

ODBC Microsoft Access Driver Login Failed

The error message was:



When I googled the error message title I found some suggestions about using DDE as a connection but I could get that to work.

Anyway when I use built in functions I don't encounter this problem so you could probably solve this by using IIF. What's your object to using IIF?

Sometimes if the test is for Null you can use Nz in place of IIF.

Hi Steve,

The actual titles in the Client table are foreign keys to a lookup table?
I was not sure if I would be able to concatenate all the various lookups together, seems I can though.

SELECT tblLookup.LookUpValue AS Title, tblClient.ForeName, tblClient.MiddleNames, tblClient.Surname, tblClient.Address1, tblClient.Address2, tblClient.Address3, tblClient.Address4, tblClient.Address5, tblClient.PostCode, tblLookup_2.LookUpValue AS JointTitle, tblProvider.ProviderName, Format([SumAssured],"Currency") AS CSumAssured, tblLookup_1.LookUpValue AS CoverType, tblClientProduct.CoverTerm, Format([Premium],"Currency") AS CPremium, tblClientProduct.OldCover, tblClientProduct.OldProvider, Format([Disposable],"Currency") AS CDisposable, tblClientProduct.SurvivePeriod, tblClientProduct.Event, IIf([JointCover],Trim([Title]) & " " & Trim([KnownAs]) & " " & Trim([Surname]) & " & " & Trim([JointTitle]) & " " & Trim([JointKnownAs]) & " " & Trim([JointSurname]),Trim([KnownAS]) & " " & Trim([Surname])) AS CoverNames
FROM tblLookup AS tblLookup_2 INNER JOIN (tblProvider INNER JOIN (tblProduct INNER JOIN ((tblClient INNER JOIN tblLookup ON tblClient.Title = tblLookup.ID) INNER JOIN (tblClientProduct INNER JOIN tblLookup AS tblLookup_1 ON tblClientProduct.CoverType = tblLookup_1.ID) ON tblClient.ClientID = tblClientProduct.ClientID) ON tblProduct.ID = tblClientProduct.ProductID) ON tblProvider.ID = tblProduct.ProviderName) ON tblLookup_2.ID = tblClient.JointTitle;

Just a little unweildy?

Might need a function anyway later down the line, so handy to know.

Edit: Did you write that error message as the colon is missing after C drive letter?
At present I am testing things out manually so from Word I open the template file and select list and navigate to where my DB is stored and then select the qry from the dialogue window.

 

[sneuberg]

Edit: Did you write that error message as the colon is missing after C drive letter?

Yeah I typed it. Wouldn't it be nice if you could copy and paste from an Microsoft error message.

 

[HiTechCoach]

Hi all,

In this Letter Project I am now trying a mailmerge.
I created a function to determine whether the name in the letter would be

Mr Joe Blogs
or
Mr Joe Blogs & Mrs July Blogs

Works fine in the query qryMergeFields.

When I go to open the Word document that uses Select * from qryMergFields it complains about my function Covernames? Pic attached.

I would have thought that all Word wanted was data, and not concerned of how it was created?

Is there a workaround other than IIF is possible?

My SQL is

SELECT tblLookup.LookUpValue AS Title, tblClient.ForeName, tblClient.MiddleNames, tblClient.Surname, tblClient.Address1, tblClient.Address2, tblClient.Address3, tblClient.Address4, tblClient.Address5, tblClient.PostCode, tblProvider.ProviderName, Format([SumAssured],"Currency") AS CSumAssured, tblLookup_1.LookUpValue AS CoverType, tblClientProduct.CoverTerm, Format([Premium],"Currency") AS CPremium, tblClientProduct.OldCover, tblClientProduct.OldProvider, Format([Disposable],"Currency") AS CDisposable, tblClientProduct.SurvivePeriod, tblClientProduct.Event, CoverNames([tblClient].[ClientID]) AS ClientCover
FROM tblProvider INNER JOIN (tblProduct INNER JOIN ((tblClient INNER JOIN tblLookup ON tblClient.Title = tblLookup.ID) INNER JOIN (tblClientProduct INNER JOIN tblLookup AS tblLookup_1 ON tblClientProduct.CoverType = tblLookup_1.ID) ON tblClient.ClientID = tblClientProduct.ClientID) ON tblProduct.ID = tblClientProduct.ProductID) ON tblProvider.ID = tblProduct.ProviderName;

as Titles are in a lookup table and the ID in the client table.

TIA

First tip: NEVER NEVER NEVER let Word merge with an Access while you have the MSAccess.exe running or the database is opened by any user.


Second Tip: Only use a table as a merge source. That means you will need to running an append query to add the data from your query to a table.

Third Tip: Export the table from Tip 2 to a CSV file to be used as the merge source with word. That also satisfies Tip #1

To see it in action check out: Super Easy Word Merge


From: http://kallal.ca/wordmerge/page2.html

Don't let word attached to you running mdb file.

Again, this is just common sense. A large portion of word merge examples actually let word OPEN your mdb file. This is a formula for disaster. Allowing word to open the mdb file means that you have to deal with some known bugs (like word launching a second copy of ms-access for example). Further, if you implement security in ms-access, now word has to deal with passwords and permissions (again, a huge can of worms). Worse is if you have the runtime of ms-access installed, or multiple versions of ms-access, then word can again cause all kinds of problems as it tries to launch ms-access, and it might even launch the wrong version of ms-access. I could again rant on for pages here, but I think any developer can clearly see that if we prevent word from trying to attach to the ms-access mdb file, then we avoid a TON OF POSSIBLE problems. Again, since we CAN control this, then lets do so. As a result, my sample merge code DOES NOT let word attached to the mdb file. As a result, it is rock solid. As a result, it just works!

Of course what I do is create a temp txt file with the one record merged as a text csv file. Note that I used to use a file name of merge.txt. However, it turns out that a bug exists in the word97 merge on pc's with file extensions HIDDEN. The problem is especially noticeable on win xp machines, since hidden extensions is a common default for many users pc's. So, the solution here is to either turn on extensions, but that is hard to do. It is way too much to ask users to re-configure their pc's to show file extensions. However, by using a file name with a non registered file extension, this known word merge bug can be avoided! Thus, I now use a file name of merge.888, and this avoids this bug. If you need to use those merge files in Excel, you can either change my code, as the file name used is a constant. You can also just re-name the temp file also.

 

[Gasman]

Wow Boyd,

First tip: NEVER NEVER NEVER let Word merge with an Access while you have the MSAccess.exe running or the database is opened by any user.

I've been doing just that, albeit in test with just myself using the accdb.

Second Tip: Only use a table as a merge source. That means you will need to running an append query to add the data from your query to a table.

Currently using a SELECT query to construct and test the data.

Third Tip: Export the table from Tip 2 to a CSV file to be used as the merge source with word. That also satisfies Tip #1

I take it the csv file would need headers?, would a Excel sheet be better?

To see it in action check out: Super Easy Word Merge


From: http://kallal.ca/wordmerge/page2.html

Thanks for those.

Edit: Your download link does not work for me?

 

[sneuberg]

First tip: NEVER NEVER NEVER let Word merge with an Access while you have the MSAccess.exe running or the database is opened by any user.

I believe Word uses a ODBC connection. Are you saying NEVER NEVER NEVER connect to an Access database via ODBC while you have the MSAccess.exe running or the database is opened by any user? If the database is .ACCDB rather than .MDB would that change any of your tips?

 

[Gasman]

Thank you Boyd,

I've got that mailmerge code working with my data in test mode. It does not appear to like real templates (.dotx), as that produced an error. However saving a version as .docx works a treat.

Edit: This also works fine with queries?

 

[HiTechCoach]

I believe Word uses a ODBC connection. Are you saying NEVER NEVER NEVER connect to an Access database via ODBC while you have the MSAccess.exe running or the database is opened by any user? If the database is .ACCDB rather than .MDB would that change any of your tips?

If only it were true that Word just used ODBC.

Unfortunately that is not what Word does. When Word starts a merge and you select an Access table it actually launches another instance of the MSAccess.exe into memory and opens the database! NOT GOOD!

 

[HiTechCoach]

I got the download link fixed on my site.

Try again: Super Easy Word Merge

Thank you Boyd,

I've got that mailmerge code working with my data in test mode. It does not appear to like real templates (.dotx), as that produced an error. However saving a version as .docx works a treat.

Edit: This also works fine with queries?

My customers do 100's of merges a day with Word automated from my Access front ends.

Until I changed to only merging with a CSV files I had many issues and it was slow.

The advantage to a CSV/Text is that Word does not have to load anything else to read the file. Low system overhead! And it is very simple to code.

I also use true Word Templates (.dot/.dotx) without issue using VBA code.
Can you post the code you are using?

 

[Gasman]

My customers do 100's of merges a day with Word automated from my Access front end.

Until I change to only merging with a CSV files I had many issues and it was slow.

The advantage to a CVS/Text is that Word does not have to load anything else to read the file. Low system overhead! And it is very simple to code.

I also use true Word Templates (.dot/.dotx) without issue using VBA code.
Can you post the code you are using?

Will do tomorrow Boyd.

When I said it worked with queries, I meant Alberts code, taking a query and creating a csv file.
Alberts code does not see dotx files, so I renamed my template .dotx to .docx to be picked up in the Template form?
Should I be changing his code to pick up .dotx file instead?

I got the code from your other link. I was just informing you about yours and yes it works fine for me now. Thank you.

 

[HiTechCoach]

Yea...Albert's code was last updated for Office 2000/2002/2003.

It can be modified to work with Office 2007 and later.

FWIW: In Access 2007 and later the new RichText feature has allowed me to do a lot with Access reports that previously required Word.

 

[Gasman]

Yea...Albert's code was last updated for Office 2000/2002/2003.

It can be modified to work with Office 2007 and later.

FWIW: In Access 2007 and later the new RichText feature has allowed me to do a lot with Access reports that previously required Word.

Hi Boyd,

That was my first approach, BUT I still expected to export to Word for final amendments that would be easier to do by the user than try to cover every eventuality with code. To do that with my lack of experience would take a long long time, so i was looking for some sort of compromise.
Unfortunately the end result to Word was underwhelming to say the least, especially when I already had the text formatted correctly in rich text memo fields.

Hence the mailmerge route now.
I just spent some time opening the template and saving as .docx. Will try .dotx tomorrow.

I was surprised however that Word did not just take the data presented and not worry about how it was constructed and I might still have to take the table route later on down the line.

Eg it did not like Nz(), but was happy with Len() :banghead:

 

[Gasman]

Yea...Albert's code was last updated for Office 2000/2002/2003.

It can be modified to work with Office 2007 and later.

FWIW: In Access 2007 and later the new RichText feature has allowed me to do a lot with Access reports that previously required Word.

Boyd,

Would you be able to advise me on the code changes to allow it to work with .dotx or .dotm files please.?

I've found the code in the GUITemplate form, but trying to work out what actually needs to be changed in several places?

I'm on Office 2007 Version is 12

TIA

 

[HiTechCoach]

Word Mail Merge with an Access query = Never ending :banghead: :banghead: :banghead:

Word Mail Merge with an Access Table: - Less Bad- still will have issues

Word Mail Merge with a CSV fle: Just works. And it only take a few extra lines of code.

 

[Gasman]

Hi Boyd,

I am off on holiday now for 2 weeks, but when I get back I'd like to take into account .dotx, .docm, .dotx and .dotm files as well as what the routine already copes with.

I have worked out how to get them using dir() with *.* and test for the extensions.
The problem I have is keeping the correct extension with the filename in the listbox? as Albert uses a Value list.

Now looking to reinvent the wheel, just to using the code in the easiest and best way. and not having to rewrite Albert's code?

TIA

 

[HiTechCoach]

Hi Boyd,

I am off on holiday now for 2 weeks, but when I get back I'd like to take into account .dotx, .docm, .dotx and .dotm files as well as what the routine already copes with.

I have worked out how to get them using dir() with *.* and test for the extensions.
The problem I have is keeping the correct extension with the filename in the listbox? as Albert uses a Value list.

Now looking to reinvent the wheel, just to using the code in the easiest and best way. and not having to rewrite Albert's code?

TIA

We will be here when you get back.

I have not really used any part of the example except for the part that does the actual merge. I have built my own way to select the templates to use that works better with my application's workflow.

 

[Gasman]

Boud,

I'm thinking the easiest thing for me to do, would be to include the file extensions in the list box as well. That way I do not have the trouble of trying to keep track of them with the entry for the filename once they are in the list box?

Or would you have a better method?

Sad I know, first day of my holiday and still thinking of this, but I am in a hotel in Dallas killing time, so may as well do something useful with it.