Access Mail Merge to Word VBA Code Assistance

[kayberrie]

I want to write a VBA mail merge code. I want to link the code/macro/dohicky to a nifty little button so it makes life easy. I think I can handle the button part, the code part - not so much. I know very little (actually probably nothing) about VBA and I'm currently learning my way around Access.

Basically, I want to click a button in the form that merges the letter for me, only for the one record I'm currently viewing..not the whole database.

Here is what I have so far (shamelessly modified from the MS help site)

Public Function Merge3rdOffense()


Set obj = Word = GetObject("\\Ovpd2k2\Admin\Cassidy\Alarms\3rd Letter\0 NEW MAIL MERGE TEMPLATE.doc", "Word.Document")
'Make Word visible
obj.Word.Application.Visible = True
'Set the mail merge data source as the Alarms 2007 database
objWord.MailMerge.OpenDataSource _
Name:="C:\Progream Files\Microsoft " & _
"\\Ovpd2k2\Volunteer\alarms\Alarms 2007.mdb"
LinkToSource: =True, _
Connections:="QUERY 3rd Offense", _
SQLStatement:="SELECT * FROM (3rd Offense)"

'Execute the mail merge.
objWord.MailMerge.Execute

End Function

This part:
LinkToSource: =True, _
Connections:="QUERY 3rd Offense", _
SQLStatement:="SELECT * FROM (3rd Offense)"
seems to be my error (or at least I assume it is since it is in red).

I'ver been told we don't have a SQL server so that might be my issue, but again, I don't know enough about it to know how to modify this to work.

I'm using Access 2002 (ick I know) and Windows XP.

Alarms 2007 is my database

0 NEW MAIL MERGE TEMPLATE.doc is my merge doc (maybe the 0 is bad?)

There is a query called 3rd offense that I was hoping might make life easier (based on the MS example). It pulls only records with the Date Letter Sent field filled in with today's date. If I don't need the query, I'm OK with that.

Thanks for your help! Hopefully, I have enough info in there=/

[nico5038]

Try changing:

SQLStatement:="SELECT * FROM (3rd Offense)"

into

SQLStatement:="SELECT * FROM [3rd Offense]"

Nic;o)