Mail Merge a MS Access 97 Query with a Word 97 Document 2

[thirty4d]

This code was the example code in Q159328 of Microsoft.com and applied it to my form.

It worked fine except it's supposed to pull the data from a 3 parameter query.

When I changed the line of code to this:

Connection:="QUERY qryToolAssy", _
SQLStatement:="Select * from [Layout]", it prompted me twice of the 3 parameters?


Function MergeIt()
Dim objWord As Word.Document
Set objWord = GetObject("J:\Shared\WorkingFolder\LayoutMaster.doc", "Word.Document&quot
' Make Word visible.
objWord.Application.Visible = True
' Set the mail merge data source as the MACH_4 database.
objWord.MailMerge.OpenDataSource _
Name:="J:\Shared\WorkingFolder\MACH_4.mdb", _
LinkToSource:=True, _
Connection:="TABLE Layout", _
SQLStatement:="Select * from [Layout]"
'Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute

'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word
'can print the merged document.

objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
'MEGA IMPORTANT - ALWAYS RELEASE THE OBJECTS - SAVE MEMORY - INCREASE PERFORMANCE.
objWord.Close
Set objWord = Nothing
End Function

Thanks!

 

[930driver]

The Connection tells it where to look, the SQLStatement tells it what records to return. You told it to look at a query, it prompts for parameters. Then you tell it to Select * from the query so it runs and prompts again for the parameters.

It's been a while since I've used Word automation but I think you can get away without the SQLStatement, especially since you want * and are not limiting the recordset.

You need both Connection and SQLStatement when you want some subset of the Connection. If you must base the Connection on a query, for instance you are joining multiple tables, use a different query without parameters.

 

[thirty4d]

930driver or anybody,

I know there's a conflict of interest in my code that's why I want someone to help me correct it!

YES! I am limiting my selection to a subset of records by these 3 fields: Partnumber, OperationSequence and Partname to make it unique.

I have to base it on a parameter query because I have to automate this task!

How can I change the SQLStatement to limit my selection based on these 3 fields?

I also know that this is the line that causing my problem.

SQLStatement:="Select * from [Layout]"

 

[Databaseguy]

If your parameter query is already referenced, you shouldn't need the SQL statement.

According to the On Line help, it's an optional argument.

P.S., releasing the objects not only saves memory, but when your Automating it'll stop those annoying multiple instances of word, as well as errors 462 and 429. Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[thirty4d]

Databaseguy,

When I remove the SQLStatement and point the connection to the Query instead of Table I still get Prompted twice.

Again, still without the SQLStatement, if I changed the connection to point to the Table I get no prompt at all.

Currently, all I have in the table is one subset of records. Because I still in test mode. But that's beside the point. I expect the code to ask me for input. Otherwise how is Access going to know what subset of records I'm looking for.

The values on the parameter query changes based on the 3 fields depending on what subset of records users are going to create meaning the kit that the user is going to prepare?

 

[Databaseguy]

Can you post the SQL for your query ? Tyrone Lumley
augerinn@gte.net

http://server47.hypermart.net/databaseguy

 

[thirty4d]

Hello Databaseguy,


SELECT Layout.ID, Layout.txtMachine, Layout.txtPartnumber, Layout.txtOperationSequence, Layout.txtPartname, Layout.Function, Layout.txtStation, Layout.txtCuttingTools, Layout.txtInsertDesc, Layout.MachineHolder
FROM Layout
WHERE (((Layout.txtMachine)=[Forms]![Layout]![txtMachine]) AND ((Layout.txtPartnumber)=[Forms]![Layout]![txtPartnumber]) AND ((Layout.txtOperationSequence)=[Forms]![Layout]![txtOperationSequence]));

 

[930driver]

Point the connection to the table and paste your sql string into the SQLStatement, something like the following

SQLStatement:= "SELECT Layout.ID, Layout.txtMachine, Layout.txtPartnumber," & _
"Layout.txtOperationSequence, Layout.txtPartname, Layout.Function," & _
"Layout.txtStation, Layout.txtCuttingTools, Layout.txtInsertDesc," & _
"Layout.MachineHolder " & _
"FROM Layout " & _
"WHERE (((Layout.txtMachine)=" & [Forms]![Layout]![txtMachine] & &quot AND " & _
"((Layout.txtPartnumber)=" & [Forms]![Layout]![txtPartnumber] & &quot AND " & _ "((Layout.txtOperationSequence)=" & [Forms]![Layout]![txtOperationSequence] & &quot);"

Watch out, there might be some wierd line wraps when you paste this in

 

[thirty4d]

930driver or Anybody!

I did what you asked me to do still got an error this time:

Run-time error '9105': String is longer than 255 characters.

I changed my code similar to Help in Access: Build SQL Statements That Include Variables and Controls and this is what they have for example:

Dim dbs As Database, qdf As QueryDef, strSQL As String
Set dbs = CurrentDb
strSQL = "SELECT * FROM Orders WHERE OrderDate" _
& "> #" & Forms!Orders!OrderDate & "#;"
Set qdf = dbs.CreateQueryDef("SecondQuarter", strSQL)


This is what I have:

objWord.MailMerge.OpenDataSource _
Name:="J:\Shared\WorkingFolder\MACH_4.mdb", _
LinkToSource:=True, _
Connection:="TABLE Layout", _
SQLSTATEMENT:="SELECT * FROM Layout " & _
"WHERE (((LAYOUT.TXTMACHINE)=" & [Forms]![Layout]! [txtMachine] & &quot AND " & _
"((LAYOUT.TXTPARTNUMBER)=" & [Forms]![Layout]![txtPartnumber] & &quot AND " & _
"((LAYOUT.TXTOPERATIONSEQUENCE)=" & [Forms]![Layout]![txtOperationSequence] & &quot);"

After running the code this is the error that I got:

Run-time error '2450':

Microsoft Access can't find the form 'Layout' referred to in a macro expression or Visual Basic code.

* The form you referenced may be closed or may not exist in this database.
* Microsoft Access may have encountered a compile error in a Visual Basic module for the form.

VERY VERY FRUSTRATED!

 

[damber]

Hi,

Just a quick tip.. maybe what you need, maybe not.

Your SQL statement is importing variables to a form.. Is that form open ?
Are you running the code from that form..?
Is the form named Layout?

I know these may be silly questions but that is what Access is telling you.

Try referencing the form using "me" rather than the full path>> me.txtLayout.value for example

Also, make sure all the fields are referenced correctly.

I also noticed that you do not enter ' ' marks around the text string input when building the SQL statement - this is surely to error as SQL requires it.. for example here is a quick SQL statement:

strSQL = "SELECT * FROM tblData " & _
         "WHERE (((tblData.txtRef1='" & me.txtRef1 & "') AND " & _
         "      ((tblData.intNum1=" &  me.intNum1 & ") AND " & _
         "      ((tblData.dteDate1=#" & me.dteDate1 & "#));"

Also - may be worth checking if the query runs on its own.. as it will show you the input boxes when it cannot find the object / control you referenced in the query.. which is probably why it doesn't work when you copy and paste into the code..

Hope it works - I know how painful these darn things can be !

Damian
damber@damber.net

A smile is worth a thousand kind words. So smile, it's easy!

 

[thirty4d]

damber,

Thank you sooooo much for your suggestion and also for symphathizing with me.

Trust me! I will try your suggestion as I am very very desperate.

Keep your fingers crossed for me.




Also many thanks to everyone who tried to help me. Didn't want to take that for granted.