How to handle an empty query result?

[Gary Stark]

I have a small macro that queries an Access database and returnes a result set, loading that result into a Word document.

When there's data returned, all is fine, but when the query result contains zero records an error dialog pops up.

I would like to be able to interrogate the result set before populating the document with the returned records, so that I can put a "no records found" message in the document rather than get the error message that I'm currently seeing.

This is the marco as it currently stands; any help will be greatly appreciated.


Sub InvalidItem()
'
' InvalidItem Macro
' Macro recorded 10/06/2004 by Gary Stark
'
Selection.GoTo What:=wdGoToBookmark, Name:="InvalidItem"
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data Source=C:\Superfund\Migrate\Prelim.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet OLEDB:Engine Type=5;Jet OLED" _
, SQLStatement:="SELECT * FROM `04 - Invalid Item`" & "", PasswordDocument _
:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", DataSource:="C:\Data\Prelim.mdb", _
From:=-1, To:=-1, IncludeFields:=True
End Sub

 

[Cindy M -WordMVP-]

Hi =?Utf-8?B?R2FyeSBTdGFyaw==?=,

You have to either

1. Set up a DAO or ADO connection to the data base, pass the query, count the records that
are returned and only try to insert the database if the count is > 0

OR

2. Assuming the error (which you unfortunately don't quote) is the field code result, use
an IF field to trap the error text and suppress the error

OR

3. Use error handling to catch the error in your code

I have a small macro that queries an Access database and returnes a result set, loading

that result into a Word document.

When there's data returned, all is fine, but when the query result contains zero records an error dialog pops up.

I would like to be able to interrogate the result set before populating the document

with the returned records, so that I can put a "no records found" message in the document
rather than get the error message that I'm currently seeing.

This is the marco as it currently stands; any help will be greatly appreciated.


Sub InvalidItem()
'
' InvalidItem Macro
' Macro recorded 10/06/2004 by Gary Stark
'
Selection.GoTo What:=wdGoToBookmark, Name:="InvalidItem"
Selection.Find.ClearFormatting
With Selection.Find
.Text = ""
.Replacement.Text = ""
.Forward = True
.Wrap = wdFindContinue
.Format = False
.MatchCase = False
.MatchWholeWord = False
.MatchWildcards = False
.MatchSoundsLike = False
.MatchAllWordForms = False
End With
Selection.Range.InsertDatabase Format:=0, Style:=0, LinkToSource:=False, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data

Source=C:\Superfund\Migrate\Prelim.mdb;Mode=Read;Extended Properties="""";Jet OLEDB:System
database="""";Jet OLEDB:Registry Path="""";Jet OLEDBatabase Password="""";Jet
OLEDB:Engine Type=5;Jet OLED" _

, SQLStatement:="SELECT * FROM `04 - Invalid Item`" & "", PasswordDocument _
:="", PasswordTemplate:="", WritePasswordDocument:="", _
WritePasswordTemplate:="", DataSource:="C:\Data\Prelim.mdb", _
From:=-1, To:=-1, IncludeFields:=True
End Sub

Cindy Meister
INTER-Solutions, Switzerland
http://homepage.swissonline.ch/cindymeister (last update Jun 8 2004)
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