Make mailmerge complete with zero records?? 1

[ordendelfai]

This is my last question, and I am squared away with this project (yay!).

Using the below code, the last problem I have run into is when there are no matching records a really nasty error message pops up from word and from access (asking to debug). I need to find a way to either make the merge still happen and just not merge any data, or a quick and easy line of code that stops the merge from occuring with a custom message box if no records are returned in the query the merge is looking at for data.

Thanks!! =D
Joel


---------------------------code-----------------------------

Public Function MergeItBlankFaxPrint()
Dim objWord As Word.Document
Set objWord = GetObject("C:\MyDB\Templates\ExternalCorrespondence\BlankFax.doc", "Word.Document&quot
' Make Word visible.
objWord.Application.DisplayAlerts = False
objWord.Application.Visible = True
' Set the mail merge data source as the Access database.
objWord.MailMerge.OpenDataSource _
Name:="C:\MyDB\MyDB.mdb", _
LinkToSource:=False, _
Connection:="QUERY qryMailMergeBlankFax"
' Execute the mail merge.
objWord.MailMerge.Destination = wdSendToNewDocument
objWord.MailMerge.Execute
'The following line must follow the Execute statement because the
'PrintBackground property is available only when a document window is
'active. Without this line of code, the function will end before Word
'can print the merged document.
objWord.Application.Options.PrintBackground = False
objWord.Application.ActiveDocument.PrintOut
ActiveDocument.Close savechanges:=wdDoNotSaveChanges
End Function

 

[RobCPA]

This will not allow the mail merge to occur if there are no records. Add this to your code:

Dim db as database
Dim rst as recordset

Set db = currentdb
set rst = db.openrecordset("qryMailMergeBlankFax&quot

if rst.recordcount < 1 then
msgbox &quot;No records!&quot;
exit function
end if

Include the rest of your code starting with:
Set objWord...

 

[ordendelfai]

Rob, your the man! Thank you for always helping me

I'll let you know how this goes.

Joel

 

[ordendelfai]

I get an error:

&quot;Run-Time error '2062'

Too few parameters. Expected 20&quot;

Any ideas??

 

[RobCPA]

I'm guessing that you're getting the error at:

set rst = db.openrecordset(&quot;qryMailMergeBlankFax&quot

Usually when the error &quot;Too few parameters&quot; appears, the query is looking for user input because the name of the field appears in the query but not in the table. Can you try running the query &quot;qryMailMergeBlankFax&quot; by itself and see if there are any error messages? If there are, you can then resolve it there.

Let me know what you find out.

 

[ordendelfai]

If I do not use that new code, the query runs ok, and the doc merges. I am using Office 97, not sure if that matters?

I know when I started to type &quot;Set rst = db.openrecordset(&quot;
The auto helper thing pops up and the format looks like:

db.openrecordsset(Name As String,[Type],[Options],[LockEdit]) As Recordset

Not sure if that helps any?

 

[RobCPA]

If the query runs ok manually, I'm not sure how else I can troubleshoot. I'm using Access 97 as well. When you type the &quot;Set rst...&quot; the auto helper will pop up, but you just need to make sure the statement ends up looking like:
set rst = db.openrecordset(&quot;qryMailMergeBlankFax&quot

I've looked for info on the specific error that you received, but haven't found anything on it.

 

[JoyInOK]

Here's a couple of things to try:
Are you setting the query in code prior to calling the MergeItBlankFax routine? If so, Use debug.print to print the SQL you're setting it to. Copy that and replace the query's SQL in design view to see if you can find the field name error.

Set the Word document's record source to your query manually through Word. In code in Access, after opening the Word Doc, add this line
Debug.Print objWord.Application.ActiveDocument.MailMerge.
DataSource.QueryString

It will return something like this in the immediate window
SELECT * FROM `qryLabelQuery`

Note the funky type of single quotes. Don't change them to regular quotes. Copy the line as it appears in the immediate window.
Use this to add
SQLStatement:=&quot;SELECT * FROM `qryMailMergeBlankFax`&quot; after the &quot;Connection:=&quot; string