By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 2,953 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Access Parameter Query to Word Merge

P: n/a
Access/Word 2000

I am trying to merge a word document with an access query that has a
parameter attached to it.

Could someone please suggest how to stop the parameter from appearing
when the query runs.

I think it might have something to do with the source document
mailmerge.doc. Could it be that I'm trying to run the query from
access using dao but the file is setup for dde??

Please, I just want the user to press a button and run the query, they
have already given me the date in a text box on the form this is
attached to.

I hope I have given you enough information, if more is needed please
let me know.

Any suggestions are gratefully welcomed.

Thanks, Lynn

Code
++++++++++++++++++++++++++++++++++++++

Private Sub CmdMailMerge_Click()
On Error GoTo Err_CmdMailMerge_Click
Dim dbs As DAO.Database
Set dbs = Application.CurrentDb
Dim qdef As DAO.QueryDef
Dim rst As DAO.Recordset, rstTable As DAO.Recordset
Dim tbf As TableDef
Dim fld As DAO.Field

DoCmd.Hourglass True

Set qdef = dbs.QueryDefs("EscalationMailMerge")
qdef.Parameters(0) = EscalationCalculationDate
Set rst = qdef.OpenRecordset()

Call ActivateWrdDoc("MailMerge.doc")

rst.Close
Set rstTable = Nothing
Set rst = Nothing

dbs.Close
Set dbs = Nothing

Exit_CmdMailMerge_Click:
DoCmd.Hourglass False
Exit Sub

Err_CmdMailMerge_Click:
MsgBox Err.Description
Resume Exit_CmdMailMerge_Click
End sub
Sub ActivateWrdDoc(somedoc As String)

AGAIN: On Error Resume Next
Set wrd = GetObject(, "Word.Application")
If Err.Number = 429 Or Err.Number = 462 Then
Err.Clear
Set wrd = CreateObject("Word.Application")
End If

' Make Word visible.
wrd.Visible = True

On Error GoTo AGAIN
wrd.Documents.Open "K:\ct\" & somedoc '<- the somedoc variable has
"somedoc.doc"
Set WordDoc = wrd.ActiveDocument
On Error GoTo ErrorHandler

' Execute the mail merge.
WordDoc.MailMerge.Execute
WordDoc.Close SaveChanges:=wdDoNotSaveChanges
Set WordDoc = Nothing
Set wrd = Nothing
On Error GoTo 0
Exit_Sub:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Err.Clear
Resume Exit_Sub
End Sub
Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
I know this doesn't directly answer your question but because of various
issues and quirks of automated mailmerge (especially the one where Word
launches a new copy of your database if it has anything displayed in the
title bar except "Microsoft Access", I tend to export the dataset to a CSV
file and then mailmerge to that instead of directly to the query.
--
Shane Suebsahakarn
----
Head of IT
PAN Telecom
Tel: +44 (0) 870 757 7001

"Lynn A." <ly***********@hotmail.com> wrote in message
news:f0**************************@posting.google.c om...
Access/Word 2000

I am trying to merge a word document with an access query that has a
parameter attached to it.

Could someone please suggest how to stop the parameter from appearing
when the query runs.

I think it might have something to do with the source document
mailmerge.doc. Could it be that I'm trying to run the query from
access using dao but the file is setup for dde??

Please, I just want the user to press a button and run the query, they
have already given me the date in a text box on the form this is
attached to.

I hope I have given you enough information, if more is needed please
let me know.

Any suggestions are gratefully welcomed.

Thanks, Lynn

Code
++++++++++++++++++++++++++++++++++++++

Private Sub CmdMailMerge_Click()
On Error GoTo Err_CmdMailMerge_Click
Dim dbs As DAO.Database
Set dbs = Application.CurrentDb
Dim qdef As DAO.QueryDef
Dim rst As DAO.Recordset, rstTable As DAO.Recordset
Dim tbf As TableDef
Dim fld As DAO.Field

DoCmd.Hourglass True

Set qdef = dbs.QueryDefs("EscalationMailMerge")
qdef.Parameters(0) = EscalationCalculationDate
Set rst = qdef.OpenRecordset()

Call ActivateWrdDoc("MailMerge.doc")

rst.Close
Set rstTable = Nothing
Set rst = Nothing

dbs.Close
Set dbs = Nothing

Exit_CmdMailMerge_Click:
DoCmd.Hourglass False
Exit Sub

Err_CmdMailMerge_Click:
MsgBox Err.Description
Resume Exit_CmdMailMerge_Click
End sub
Sub ActivateWrdDoc(somedoc As String)

AGAIN: On Error Resume Next
Set wrd = GetObject(, "Word.Application")
If Err.Number = 429 Or Err.Number = 462 Then
Err.Clear
Set wrd = CreateObject("Word.Application")
End If

' Make Word visible.
wrd.Visible = True

On Error GoTo AGAIN
wrd.Documents.Open "K:\ct\" & somedoc '<- the somedoc variable has
"somedoc.doc"
Set WordDoc = wrd.ActiveDocument
On Error GoTo ErrorHandler

' Execute the mail merge.
WordDoc.MailMerge.Execute
WordDoc.Close SaveChanges:=wdDoNotSaveChanges
Set WordDoc = Nothing
Set wrd = Nothing
On Error GoTo 0
Exit_Sub:
Exit Sub
ErrorHandler:
MsgBox Err.Description
Err.Clear
Resume Exit_Sub
End Sub

Nov 13 '05 #2

P: n/a
Yet another indirect answer... Use Albert Kallal's code that outputs
the data to a csv file and then tell Word to use that. His code does
all that pretty much for you. All you have to do is specify the query
and the template you're merging to.
Nov 13 '05 #3

P: n/a
Yet another indirect answer... Use Albert Kallal's code that outputs
the data to a csv file and then tell Word to use that. His code does
all that pretty much for you. All you have to do is specify the query
and the template you're merging to.

Uhh... yeah, the link would be nice...

http://www.attcanada.net/~kallal.msn.../msaccess.html

scroll down until you see "Super Easy Word Merge"
Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.