Posted by Erland Sommarskog on 07/23/05 01:05

David (david.roebuck@btinternet.com) writes:
> I want to loop through a table (tblrelmanager) and merge the results
> with a word doc. The trouble is I am not used to SQL coding and I cant
> get my WHERE statement to work.
>
> Can anyone tell me where I am going wrong
>
> Set rst = db.OpenRecordset("tblrelmanager")
> Do Until rst.EOF
> Set objWord = GetObject("N:\Customer Management
> Centre\Warrington\Reach
> Team\Database\fire risk assessment.doc",
> "Word.Document")
> objWord.Application.Visible = True
> objWord.MailMerge.OpenDataSource _
> Name:="D:\Documents and Settings\All
> Users\Documents\WSS\WSS.mdb", _
> LinkToSource:=True, _
> Connection:="table tblAccenture", _
> SQLStatement:="SELECT * FROM [tblAccenture] " & _
> WHERE [Relationship Manager] = rst.[Relationship Manager]"
> objWord.MailMerge.Execute
> rst.MoveNext
> Loop

This a newsgroup for MS SQL Server, and I don't know anything about
mail merge. I didn't even know that you used SQL for it.

But I would expect this to be better:

SQLStatement:="SELECT * FROM [tblAccenture] " & _
WHERE [Relationship Manager] = '" & _
rst("Relationship Manager") & "'"

That is, you need to expand the field value before you pass it to
mail merge. And rst is just another VB(A) obejct, and you refer to its
elements and indexes as any other VB(A) object.

--
Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp

[Back to original message]