[weisinator]

Automated mail merge, I thought this would be easy, lol.

MS Access 2000, Word 2002. Executing from Word VBA custom form.

I currently populate my custom form's group selection listbox with the
recordset. I know I have a connection to the database, just not with
the mail merge.

The group list selected value is used in the SQL statement I would
like to use for pulling mail merge records.

The SQL statement is valid (though ugly) and pulls data from the
tables as intended when I use it within Access.

However, if I exclude the "SQLStatement" parameter from the
OpenDataSource, it pops up with a table. I want to avoid that, so I
threw some generic code in there.

My problem comes with the document.mailmerge.datasource.querystring
property. I get "command failed" when I try to set it equal to strQry.
Doesn't matter if SQLStatement is populated or not.

Code:
-----------------------------------------------------------------------------------
Private Sub btnBeginMerge_Click()
Set docMerge = ActiveDocument

Dim strQry As String

strQry = "SELECT g.Grp, g.Ppl, " _
& "p.Ttl, p.Frstnm, p.Mddlnm, p.Lstnm, " _
& "a.Cmpny, a.Strt1, a.Strt2, a.Cty, a.Stt, a.Zp " _
& "FROM Addrss AS a, Ppl AS p, GrpMmbr AS g " _
& "WHERE g.Grp = " & drpGrpLst.Value & " " _
& "AND g.Ppl = p.Id " _
& "AND p.Id = a.Ppl;"

Debug.Print strQry

With docMerge.MailMerge
.MainDocumentType = wdFormLetters

.OpenDataSource Name:= _
dbPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Data Source=" & dbPath & ";Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet", _
SQLStatement:="SELECT * FROM `MailMergeList`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

'============
' error here!
'============
.DataSource.QueryString = strQry

With .Fields
.Add _
Range:=Selection.Range, _
Name:="FrstNm"
.Add _
Range:=Selection.Range, _
Name:="Grp"
End With

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute pause:=False

End With

End Sub
-----------------------------------------------------

Since I know I can use ADO, I'm sure I could make/drop temp tables for
merging purposes, but that is a scenario I would love to avoid if at
all possible.

Any suggestions?

[Peter Jamieson]

Four possibilities, none of them entirely convincing:
a. for some reason you may not be able to assign anything to Querystring.
However, I have just checked here and it seems OK. Easily tested with e.g.
"SELECT * FROM [Addrss]"
b. your query is longer than around 255 chars. But it doesn't seem to be
unless drpGrpLst.Value was a string around 50 characters long
c. g.Grp is supposed to be a string value, in which case you need to put
single quotes around drpGrpLst.Value , e.g. (and then you obviously need to
be careful about quotes in the string etc.)

& "WHERE g.Grp = '" & drpGrpLst.Value & "' " _

But I think you would have got that right.
d. the syntax works in Access but not via OLE DB. For example

SELECT * FROM Addrss does not work from Word but AFAIK it is OK in Access;
SELECT * FROM [Addrss] works in both. However, as far as I know if you alias
your table names as you have then the syntax works OK in Word. Might be
worth just checking that.

Peter Jamieson

"weisinator" wrote in message
ups.com...
Automated mail merge, I thought this would be easy, lol.

MS Access 2000, Word 2002. Executing from Word VBA custom form.

I currently populate my custom form's group selection listbox with the
recordset. I know I have a connection to the database, just not with
the mail merge.

The group list selected value is used in the SQL statement I would
like to use for pulling mail merge records.

The SQL statement is valid (though ugly) and pulls data from the
tables as intended when I use it within Access.

However, if I exclude the "SQLStatement" parameter from the
OpenDataSource, it pops up with a table. I want to avoid that, so I
threw some generic code in there.

My problem comes with the document.mailmerge.datasource.querystring
property. I get "command failed" when I try to set it equal to strQry.
Doesn't matter if SQLStatement is populated or not.

Code:
-----------------------------------------------------------------------------------
Private Sub btnBeginMerge_Click()
Set docMerge = ActiveDocument

Dim strQry As String

strQry = "SELECT g.Grp, g.Ppl, " _
& "p.Ttl, p.Frstnm, p.Mddlnm, p.Lstnm, " _
& "a.Cmpny, a.Strt1, a.Strt2, a.Cty, a.Stt, a.Zp " _
& "FROM Addrss AS a, Ppl AS p, GrpMmbr AS g " _
& "WHERE g.Grp = " & drpGrpLst.Value & " " _
& "AND g.Ppl = p.Id " _
& "AND p.Id = a.Ppl;"

Debug.Print strQry

With docMerge.MailMerge
.MainDocumentType = wdFormLetters

.OpenDataSource Name:= _
dbPath, _
ConfirmConversions:=False, _
ReadOnly:=False, _
LinkToSource:=True, _
AddToRecentFiles:=False, _
PasswordDocument:="", _
PasswordTemplate:="", _
WritePasswordDocument:="", _
WritePasswordTemplate:="", _
Revert:=False, _
Format:=wdOpenFormatAuto, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";Us er
ID=Admin;Data Source=" & dbPath & ";Mode=Read;Extended
Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry
Path="""";Jet OLEDBatabase Password="""";Jet", _
SQLStatement:="SELECT * FROM `MailMergeList`", _
SQLStatement1:="", _
SubType:=wdMergeSubTypeAccess

'============
' error here!
'============
.DataSource.QueryString = strQry

With .Fields
.Add _
Range:=Selection.Range, _
Name:="FrstNm"
.Add _
Range:=Selection.Range, _
Name:="Grp"
End With

With .DataSource
.FirstRecord = wdDefaultFirstRecord
.LastRecord = wdDefaultLastRecord

End With
.Destination = wdSendToNewDocument
.SuppressBlankLines = True
.Execute pause:=False

End With

End Sub
-----------------------------------------------------

Since I know I can use ADO, I'm sure I could make/drop temp tables for
merging purposes, but that is a scenario I would love to avoid if at
all possible.

Any suggestions?

[weisinator]

On Feb 13, 12:46 pm, "Peter Jamieson"
wrote:
- snip -
SELECT * FROM Addrss does not work from Word but AFAIK it is OK in Access;
SELECT * FROM [Addrss] works in both. However, as far as I know if you alias
your table names as you have then the syntax works OK in Word. Might be
worth just checking that.

Peter Jamieson

I changed one line in the query:

& "FROM Addrss AS a, Ppl AS p, GrpMmbr AS g " _

to:

& "FROM [Addrss] AS a, [Ppl] AS p, [GrpMmbr] AS g " _

It works now!

Thank you Peter!