Merge: Adding error suppression?

[horndog]

I created a VB front end for mail merges. The mail merge issue I a
having problems with is that I have two combo boxes, one pulls th
month (long format, i.e January) from the database and the Year i
4-digit format.

If a user selects a month and year that doesn't not exist, then i
states the recordset is empty, but run-time error (in run-time). Whe
the application is compiled, then it says the string is longer than 25
characters.

I have tried to suppress the error, but I think since VB just fires of
the data and opens up word, word takes over and controls the error, i
this correct?

Here is the code


PHP code
-------------------


Private Sub cmdReCallDate_Click()

Dim oApp As Word.Application
Dim oDoc As Word.Document

'Start a new document in Word
Set oApp = CreateObject("Word.Application")
Set oDoc = oApp.Documents.Add

With oDoc.MailMerge

'Insert the mail merge fields temporarily so that
'you can use the range containing the merge fields as a layout
'for your labels -- to use this as a layout, you can add it
'as an AutoText entry.
With .Fields
.Add oApp.Selection.Range, "PatientFirstName"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "PatientLastName"
oApp.Selection.TypeParagraph
.Add oApp.Selection.Range, "PatientStreet"
oApp.Selection.TypeParagraph
' oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "PatientCity"
oApp.Selection.TypeText ", "
.Add oApp.Selection.Range, "PatientState"
oApp.Selection.TypeText " "
.Add oApp.Selection.Range, "PatientPostalCode"
' oApp.Selection.TypeText " -- "
' .Add oApp.Selection.Range, "Country"
End With
Dim oAutoText As Word.AutoTextEntry
Set oAutoText = oApp.NormalTemplate.AutoTextEntries.Add("MyLabelLayout", oDoc.Content)
oDoc.Content.Delete 'Merge fields in document no longer needed now
'that the AutoText entry for the label layout
'has been added so delete it.

'Set up the mail merge type as mailing labels and use

.MainDocumentType = wdMailingLabels
.OpenDataSource Name:="", Connection:="DSN=Brent;DATABASE=NAME; uid=; pwd=;", SQLStatement:="SELECT PatientFirstName, PatientLastName, PatientStreet, PatientCity, PatientState, PatientPostalCode FROM Patients, exams WHERE exams.PatientID = Patients.PatientID AND exams.ReturnMonth = '" & cmbMonth.Text & "' And exams.ReturnYear = " & cmbYear.Text, SubType:=wdMergeSubTypeWord2000






'Specify your data source here

'Create the new document for the labels using the AutoText entry
'you added -- 5160 is the label number to use for this sample.
'You can specify the label number you want to use for the output
'in the Name argument.
oApp.MailingLabel.CreateNewDocument Name:="5160", Address:="", _
AutoText:="MyLabelLayout", LaserTray:=wdPrinterManualFeed

'Execute the mail merge to generate the labels.
.Destination = wdSendToNewDocument
.Execute

'Delete the AutoText entry you added
oAutoText.Delete

End With


'Close the original document and make Word visible so that

'the mail merge results are displayed
oDoc.Close False
oApp.Visible = True

'Prevent save to Normal template when user exits Word
oApp.NormalTemplate.Saved = True

End Sub

 

[horndog]

anyone know how I would add an error message stating the datasourc
records are zero, instead of a run-time error

 

[Cindy M -WordMVP-]

Hi Horndog,

I created a VB front end for mail merges. The mail merge issue I am
having problems with is that I have two combo boxes, one pulls the
month (long format, i.e January) from the database and the Year in
4-digit format.

If a user selects a month and year that doesn't not exist

I'd have this interface *before* Word is ever started. And use ADO (or
DAO) to query the record set. If you get back 0 records, then "error
out" before the Word code.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow question
or reply in the newsgroup and not by e-mail

 

[horndog]

I figured that much out...

But..

I have tried

If oDoc.MailMerge.Datasource.recordset = 0 Then _
MsgBox "blah blah blah", vbOkOnly

End If

But that didn't work, it didn't like th
oDoc.MailMerge.Datasource.recordset object

 

[Cindy M -WordMVP-]

Hi Horndog,

anyone know how I would add an error message stating the datasource
records are zero, instead of a run-time error?

Did you see my reply to your original question? You can't trap
run-time errors when you're automating; you have to anticipate the
problem and avoid it.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any follow
question or reply in the newsgroup and not by e-mail

 

[horndog]

i understand that, I was looking for a solution, there has to be a wa
to trap the error

 

[Cindy M -WordMVP-]

Hi Horndog,

I repeat, did you read my original message? I tell you
there that you'll need to use ADO to connect to the data
soruce BEFORE you start with the Word stuff. If the same
query, in that interface, returns zero records, don't even
open the Word document. Give the user the message that
there are no records and let them try again (or whatever is
appropriate).

I figured that much out...

But..

I have tried

If oDoc.MailMerge.Datasource.recordset = 0 Then _
MsgBox "blah blah blah", vbOkOnly

End If

But that didn't work, it didn't like the
oDoc.MailMerge.Datasource.recordset object.

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update
Sep 30 2003)
http://www.word.mvps.org

This reply is posted in the Newsgroup; please post any
follow question or reply in the newsgroup and not by e-mail