Mail merge from Access, and ACCDE and Word Mail merge (1 Viewer)

p_d_hodgson

New member
Local time
Today, 11:13
Joined
Jul 15, 2020
Messages
11
Hi,
Could anyone please help me on these issues? I think the answer to question 1 is initially a NO, but there may be ways around it I cannot think of.
1. I have a database I am developing for a customer, and I am releasing it as an ACCDE (executable only) front-end, with a separate back end. I want to (from Access) print labels, and believe that because the label page format may change (the customer may change their labels, and I don't want to have to keep changing reports) the easiest way to support this is through a Word merge. When I try to connect the Word document to the ACCDE, it says that it cannot because "The data source contains no visible tables"... Is there a way to overcome this?

2. Trying to create the mail merge from Access using "External Data\Word Merge" has issues... my data has to come from a query because the data is across multiple tables, and one value is generated by a global Function based on a record ID (pkey). When I create the template and try to link back to the query I am only offered tables! Does anyone know a way round this?

3. I tried creating a template and linking it to a table in a different dummy database, then use Access VBA to open the template and link it to the correct database abd query based on this code:

Set oWord = CreateObject("Word.Application")
Set oWdoc = oWord.Documents.Open(wdTemplateName)

oWord.Visible = False

With oWdoc.MailMerge
.MainDocumentType = wdMailingLabels 'wdFormLetters
.OpenDataSource _
Name:=CurrentProject.FullName, _
AddToRecentFiles:=False, _
LinkToSource:=True, _
SQLStatement:="SELECT * FROM [qryTagInfo]"
.Destination = 0 'wdSendToNewDocument
.Execute Pause:=False
End With


I get an error "ODBC Microsoft Access Driver Login Failed"/"Driver Cannot find the file '...\Sandons.mdb'", which is strange as CurrentProject.FullName is '...\Sandons v2.accdb', i.e. it seems to have lost the bit of the filename from the space onwards! Which is doubly odd as some of the directories in the path have spaces in them!!!

Not sure if this is relevant (probably not) but when I OK that, it comes up with a login prompt for ODBC but as this is obviously for a database that does not exist I press the 'Database...' button, which comes up with a list of databases in the current directory including the one that I want (Sandons v2.accdb) so I select that then OK the login, this produces a prompt for 'Select Table' with no tables listed (all my tables are linked tables to the back-end database). If I press 'Options..' on that screen I get checkboxes for Tables/Views/System Tables/Synonyms, so I tick the 'Views' and it lists the query I want to use (qryTagInfo), so I select that and OK, then the Word template appears but with no data. When I go back to Access (not closing Word) there is a message box saying "Microsoft Access"/"Word was unable to open the data source".

I also had an error at one point that seemed to indicate that Word could not open the database because it was locked (presumably by Access that I was running to automate the Word mail merge!).

Could anyone point me to a more 'correct way' of having Access use Word to generate a mail merge based on query data generated from the data in the same Access database (particularly when one item is using a global function that formats the primary key into a string e.g. XYZnnn where nnn is 100+pkey value!)? It seems obvious that I must be barking up the wrong tree!

Many thanks,
Paul
 

isladogs

MVP / VIP
Local time
Today, 11:13
Joined
Jan 14, 2017
Messages
18,186
1. In a similar situation, I create report templates for the labels based on selected label sizes e.g. Avery L7160/L7163. The user selects the correct label using a combo and the labels are then printed in Access. No need for Word at all.

2. Not sure why you are restricted to tables but perhaps you could get round that by populating a temp table with your query data.
However, I've found it easier to manage mail merge from Access using Albert Kallal's super easy mail merge utility. The code is complex but using it is 'super easy'.
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 19:13
Joined
May 7, 2009
Messages
19,169
you can use an Access Report (label).
create as many standard labels, so your user can select
which one is available to him.

if you cannot use mail-merge, use Bookmark on Word doc.
it is much easier to manipulate.
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 19, 2002
Messages
42,970
Once a label size is chosen, it probably isn't going to change unless something about the business need changes. So, I wouldn't try to anticipate this.

If you really want to do this, I would use OLE automation. I can post a sample but it is geared toward printing single documents so you would have to change the initial logic to use a query and loop through the query rather than just printing the single record. But the details of how to automate Word are there.
 

Attachments

  • SampleWordAutomation180206WithLetters.zip
    446.9 KB · Views: 398

p_d_hodgson

New member
Local time
Today, 11:13
Joined
Jul 15, 2020
Messages
11
Hi Colin and arnelgp,
I will persevere with Access reports as mail merge is not playing 'nice'! The main issue that I have is that my customer's printer is a bit unusual. It is a Zebra GX420d Direct Thermal Transfer printer that prints on 38mm by 25mm sticky labels that are on roll. The printer itself understands the page size, so that's OK, but trying to cram the information on is a problem. These are not mailing labels, but labels for items in a shop (which may be why mail merge is not working so well), but I can get a report generating the information in (what appears to be) the right format for my laser printer or PDF printer, just that when it comes to printing on the Zebra it all goes horribly wrong (2 pages ends up printed on 6 labels, 4 labels for the first page and 2 for the second!)
I tried Albert Kallal's code (thanks for pointing me there Colin) but it was so messy setting it up (because the form with the matching content was a datasheet subform, so adding the button and getting the template setup was a nightmare!)
So, I'll close this one for now.
Many thanks again guys, you're great the way you help us 'beginners' through our teething problems.
Cheers,
Paul
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:13
Joined
Sep 21, 2011
Messages
14,038
PMFJI,
Why are you not using Labels instead of Reports?, just curious.
 

p_d_hodgson

New member
Local time
Today, 11:13
Joined
Jul 15, 2020
Messages
11
Hi Gasman,

I am really just trying to use anything that works... the end target is a print of information from a query set over 2 sticky back labels that can be stuck on the good that are on the shelves. Each row in the query relates to one item that will be placed on the shelf, but the information is too much to print on one 38mm by 25mm label. The printer is a Zebra Direct Thermal Printer (which I do not have direct access to, it is in the shop 200 miles away!) and the results I get from reports tends to be things like: Just the right hand half of the labels, or, the first label's data scattered over 4 labels and the second's data scattered over two more labels.
What I am now working on is trying to export the query to a csv file (which I have succeeded in doing, I tried unsuccessfully with Export Specifications, so instead created a record set scanned the Fields to output the csv header record field names, then scanned the record outputting the field values for each record). Then the intention is to automate word to do a mail merge from a template file (using the csv data) and printing it (via use of the print dialog automated from access). I do not need to save the merge document that results so I then quit word without saving. This method will allow the simpler generation/adjustment of a template file to ensure the print goes in the correct format to the printer (which the customer does currently with item names/sizes from their stock control system).

The latest issues I have are related to opening the PrintDialog, my code does:

Set objWordDoc = GetObject(FilePath & MergeDocumentFilename, "Word.Document")

objWordDoc.Application.Visible = True

'Format:=0 '0 = wdOpenFormatAuto
objWordDoc.MailMerge.OpenDataSource _
Name:=FilePath & strMailmergeDataFilename, ConfirmConversions:=False, _
ReadOnly:=False, LinkToSource:=True, AddToRecentFiles:=False, _
PasswordDocument:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", Revert:=False, Format:=0, _
Connection:="", SQLStatement:="", SQLStatement1:=""

objWordDoc.Application.Dialogs(88).Show = True

objWordDoc.MailMerge.Destination = 1

objWordDoc.MailMerge.Execute

The '88' is the enumeration of wdDialogFilePrint, which if I use that gives me a variable not declared (I don;t know where to get the enumerated value from), but the 88 gives me a subscript out of range :-(, both options causing the merge not to run!

Any insights you might have on overcoming these problems would be gratefully received.

Thanks,
Paul
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:13
Joined
Sep 21, 2011
Messages
14,038
Firstly, I always have to check the syntax of most of the code I write, my memory is so bad, so I either Google or look through old code to see what I have written previously.
FWIW I would use the actual word adjective and not a number. Coming back 3 weeks later I would have no idea what 88 means? That is what they are there for, to be descriptive.

So firstly check out how the dialog window is used.


Now admittedly labels expect one set of data per label.? The same would apply in Word?
It is not beyond the bounds of reason to get larger labels if the data will not fit on one.? :unsure:, but that is between you and your customer.

In your predicament I would perhaps try splitting the data into two records and then use the labels.?
You would need to order it in some way, but if one query used even numbers and the other odd and it was sorted by that field, then you would have pairs of rows for the printer labels.?

Just a thought, as not something I have even had to do. The experts here will know better on how to implement that, or even a better way.

HTH
 

p_d_hodgson

New member
Local time
Today, 11:13
Joined
Jul 15, 2020
Messages
11
Hi Gasman,

Thanks, that was the page that I got the code from... the enumerated value (wdDialogFilePrint) was as I copied into the VBA code in my program. The first attempt to run this code gave a compikle fail saying that wdDialogFilePrint had not been defined prior to use (which was why I said it complained that the variable was not declared, and why I asked whether there was something - like a header file - that I should be including to define the constant/enumerated value). I found another forum on the internet that indicated someone had the same problem and replaced the Constant name with it's value (of 88), but that gave me subscript out of range. I have now given up on the whole idea of using a mail merge!

My Access report is not doing much better, but I am hoping to do a remote session to my customer's computer to set up the report on their machine to have direct access to the printer setup.

Thanks,
Paul
 

Gasman

Enthusiastic Amateur
Local time
Today, 11:13
Joined
Sep 21, 2011
Messages
14,038
If you cannot use use wdDialogFilePrint, most likely you need another reference, but your syntax is wrong anyway according to the MS docs?
The reference is likely Microsoft Word xx Object Library, but the 88 should work as well if you use the correct syntax. I only tested that line in Word itself
It should just be
Code:
Application.Dialogs(88).Show
I believe, which works in a word document, and you need to prefix it with objWordDoc

However as objWordDoc is the Word object reference for the application, then perhaps you just use
Code:
objWordDoc.Dialogs(88).Show

HTH
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 07:13
Joined
Feb 19, 2002
Messages
42,970
Try defining a custom label format that is the correct width but as long as two labels since you want your text to print across two labels. The problem will be the gap. If one label abuts the next, your text will not break as long as when the labels are transferred, they are aligned correctly. If there is any gap, you need to play around with the top margin of the label so that you can force the gap to be between lines.

In any event, this will be almost impossible unless you have a similar printer to play with. If the printer is 200 miles away, is there any way you can remote into the PC it is attached to for testing?
 

Users who are viewing this thread

Top Bottom