Automated Mail Merge from Access Part 2

[jabrony76]

Hi all - I have a question already out there about this but just discovered another bug.

I can't run this Mail Merge off of a parameter query whose criteria is based on a forms values. This is because the code below is opening another instance of Access where (obviously) the form isn't open, just the switchboard.

Is there anyway I can force access to use the open active db as the data source rather than opening a new instance?
Code is below.

Thanks!
Andy



Function DenialMerge()
Dim objWord As Word.Document
Set objWord = GetObject("C:\Denial.doc", "Word.Document&quot
' Show Word.
objWord.Application.Visible = True
' Set the data source of the merge to CCDB.
objWord.MailMerge.OpenDataSource _
Name:="S:\CCDBv3.0\Front_End\CCDB_v3.01.mdb", _
LinkToSource:=True, _
Connection:="QUERY qry_Denial_Form_Test", _
SQLStatement:="SELECT * FROM [qry_Denial_Form_Test]"
' Run it!
objWord.MailMerge.Execute
' Protect Denial Worsheet!
ActiveDocument.Protect Password:="password", NoReset:=
False, Type:= _wdAllowOnlyFormFields

End Function

 

[BillPower]

Do you have MS Access on your PC. If so, a way around this is to convert the select parameter query to a make table query, e.g. get the parameter(s) from the form, make a table based on these parameters on your PC, then base your mail merge on the table or new query based on the made table. Obviously, you'll need to reflect these changes in Word. Does that make sense at all.

Function DenialMerge()
Dim objWord As Word.Document
Set objWord = GetObject("C:\Denial.doc", "Word.Document&quot
' Show Word.
objWord.Application.Visible = True
' Set the data source of the merge to CCDB.
objWord.MailMerge.OpenDataSource _
Name:="S:\CCDBv3.0\Front_End\CCDB_v3.01.mdb", _
LinkToSource:=True, _
Connection:="QUERY qry_Denial_Form_Test", _
'SQLStatement:="SELECT * FROM [qry_Denial_Form_Test]"
SQLStatement:="SELECT qry_Denial_Form_Test.AFieldName,* INTO tbl_Denial_Form_Test FROM qry_Denial_Form_Test;"
' Run it!
' tbl_Denial_Form_Test has the parameterised data ready for merging.
' where it will have made it, I don't know, you're connected to the s: drive at the moment
SQLStatement:="SELECT * FROM tbl_Denial_Form_Test;"
' Run it!
objWord.MailMerge.Execute
' Protect Denial Worsheet!
ActiveDocument.Protect Password:="password", NoReset:=
False, Type:= _wdAllowOnlyFormFields

End Function

See Thread181-418903 for an example of closing multiple instances of MS Access.

Hopefully give you some ideas.

Bill

 

[BillPower]

I've just read my posting, obviously you must have Access on your PC. I think it's time for me to go to bed.

Regards, Bill

 

[rookery]

thread705-412561