Query Problem in Test.

  • Thread starter Thread starter Doctor
  • Start date Start date
D

Doctor

I keep getting errors on the WHERE part of my query. I'm sure it's something
simple.

I don't know if you will need the whole code to see what the problem is. It
is lengthy, so I will start with just where the problem is.

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If


Thanks for the help.
 
I keep getting errors on the WHERE part of my query. I'm sure it's something
simple.

I don't know if you will need the whole code to see what the problem is. It
is lengthy, so I will start with just where the problem is.

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

Thanks for the help.
Click to expand...

Can you provide the WHERE clause (the code that produces strWhere)? I
would recommend breaking up the code - namely, state the query in a
string first so you can see what the entirety of the query will be and
then running that in a query designer window. This will show any
errors, often with more meaningful help that just throwing the query
at the DB engine.

By the way, I wouldn't use rs.RecordCount in the way you are trying to
use it - use rs.EOF instead. RecordCount is only populated when you
move to the end of the recordset whereas EOF always returns a reliable
true/false indicator.

-- James
 
I see. Although strWhere was initialized it was never set. I know that the
query works in the query designer. I've run it several times that way. The
WHERE clause in the query is this: WHERE
(((T2.Distance)<=CDbl([DISTANCE_MILES])))

Side Note: T2 is a query that has a paramater of [ZipCode]

However from my form I am trying to set two paramaters (the other one is set
in T2) as the WHERE statement. Maybe I am going about this wrong. Can I just
put: "WHERE paramZip, paramMiles" as my WHERE without using strWhere. But
then in the lower portion of my code what would I use to test the results
with IF statements?

Private Sub Search_Click()
Dim strWhere As String
Dim rs As ADODB.Recordset

'Set rs = SelectRadius.SelectRadius(txtZip.Value, CDbl(txtDistance.Value))

Dim qry As New ADODB.Command
Dim paramZip As ADODB.Parameter
Dim paramMiles As ADODB.Parameter

Set paramZip = qry.CreateParameter("ZIP_CODE", adVarChar, adParamInput,
255, txtZip.Value)
Set paramMiles = qry.CreateParameter("DISTANCE_MILES", adDouble,
adParamInput, 8, CDbl(txtDistance.Value))

qry.Parameters.Append paramZip
qry.Parameters.Append paramMiles

qry.CommandText = "ChurchDistance"
qry.ActiveConnection = CurrentProject.Connection

Set rs = qry.Execute()
rs.MoveNext

'Begin my added code
' Check to see that we built a filter
If IsNull(strWhere) Then
MsgBox "You must enter search criteria.", vbInformation, gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation, gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rs.MoveLast
' If 5 or less or frmContacts already open,
If (rs.RecordCount < 6) Or
CurrentProject.AllForms("ChurchInformation").IsLoaded Then
'Or IsFormLoaded("Contacts")
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on church information form
Forms!ChurchInformation.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Your search found " & rs.RecordCount & "
churches. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "ChurchSummary", WhereCondition:=strWhere
' Make sure focus is on contact summary
Forms!ChurchSummary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on contacts
Forms!Contacts.SetFocus
End If
End If

' Done
'DoCmd.Close acForm, Me.Name
' Clean up recordset
rs.Close
Set rs = Nothing
End Sub
 
If the variable strWhere isn't populated, then the statement

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)

will raise an error because you've got the keyword WHERE in the SQL with
nothing after it.

What is DISTANCE_MILES? Is that there to generate a parameter prompt, or is
it a field in one of the two tables?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doctor said:
I see. Although strWhere was initialized it was never set. I know that the
query works in the query designer. I've run it several times that way. The
WHERE clause in the query is this: WHERE
(((T2.Distance)<=CDbl([DISTANCE_MILES])))

Side Note: T2 is a query that has a paramater of [ZipCode]

However from my form I am trying to set two paramaters (the other one is
set
in T2) as the WHERE statement. Maybe I am going about this wrong. Can I
just
put: "WHERE paramZip, paramMiles" as my WHERE without using strWhere. But
then in the lower portion of my code what would I use to test the results
with IF statements?

Private Sub Search_Click()
Dim strWhere As String
Dim rs As ADODB.Recordset

'Set rs = SelectRadius.SelectRadius(txtZip.Value,
CDbl(txtDistance.Value))

Dim qry As New ADODB.Command
Dim paramZip As ADODB.Parameter
Dim paramMiles As ADODB.Parameter

Set paramZip = qry.CreateParameter("ZIP_CODE", adVarChar, adParamInput,
255, txtZip.Value)
Set paramMiles = qry.CreateParameter("DISTANCE_MILES", adDouble,
adParamInput, 8, CDbl(txtDistance.Value))

qry.Parameters.Append paramZip
qry.Parameters.Append paramMiles

qry.CommandText = "ChurchDistance"
qry.ActiveConnection = CurrentProject.Connection

Set rs = qry.Execute()
rs.MoveNext

'Begin my added code
' Check to see that we built a filter
If IsNull(strWhere) Then
MsgBox "You must enter search criteria.", vbInformation,
gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rs.MoveLast
' If 5 or less or frmContacts already open,
If (rs.RecordCount < 6) Or
CurrentProject.AllForms("ChurchInformation").IsLoaded Then
'Or IsFormLoaded("Contacts")
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on church information form
Forms!ChurchInformation.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Your search found " & rs.RecordCount & "
churches. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "ChurchSummary", WhereCondition:=strWhere
' Make sure focus is on contact summary
Forms!ChurchSummary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on contacts
Forms!Contacts.SetFocus
End If
End If

' Done
'DoCmd.Close acForm, Me.Name
' Clean up recordset
rs.Close
Set rs = Nothing
End Sub


Minton M said:
Can you provide the WHERE clause (the code that produces strWhere)? I
would recommend breaking up the code - namely, state the query in a
string first so you can see what the entirety of the query will be and
then running that in a query designer window. This will show any
errors, often with more meaningful help that just throwing the query
at the DB engine.

By the way, I wouldn't use rs.RecordCount in the way you are trying to
use it - use rs.EOF instead. RecordCount is only populated when you
move to the end of the recordset whereas EOF always returns a reliable
true/false indicator.

-- James
Click to expand...
Click to expand...
 
[Distance_Miles] is one of the two paramaters necessary for the query to run.

How do I make strWhere equal what the two paramaters are?

Here is what the two paramaters get designated as in the code:

I know that this works. What I am trying to accomplish in the lower portion
of my code is run a series of tests on the query that was run based on the
paramaters that were entered into the form.
1. I want to show a message if no contacts are found in the search
2. If more than 5 records are found that meet the criteria, I want the
results to open in form ChurchSummary if the user chooses to.
3. Show the results in form ChurchInformation.

I do appreciate your feedback.


Douglas J. Steele said:
If the variable strWhere isn't populated, then the statement

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)

will raise an error because you've got the keyword WHERE in the SQL with
nothing after it.

What is DISTANCE_MILES? Is that there to generate a parameter prompt, or is
it a field in one of the two tables?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doctor said:
I see. Although strWhere was initialized it was never set. I know that the
query works in the query designer. I've run it several times that way. The
WHERE clause in the query is this: WHERE
(((T2.Distance)<=CDbl([DISTANCE_MILES])))

Side Note: T2 is a query that has a paramater of [ZipCode]

However from my form I am trying to set two paramaters (the other one is
set
in T2) as the WHERE statement. Maybe I am going about this wrong. Can I
just
put: "WHERE paramZip, paramMiles" as my WHERE without using strWhere. But
then in the lower portion of my code what would I use to test the results
with IF statements?

Private Sub Search_Click()
Dim strWhere As String
Dim rs As ADODB.Recordset

'Set rs = SelectRadius.SelectRadius(txtZip.Value,
CDbl(txtDistance.Value))

Dim qry As New ADODB.Command
Dim paramZip As ADODB.Parameter
Dim paramMiles As ADODB.Parameter

Set paramZip = qry.CreateParameter("ZIP_CODE", adVarChar, adParamInput,
255, txtZip.Value)
Set paramMiles = qry.CreateParameter("DISTANCE_MILES", adDouble,
adParamInput, 8, CDbl(txtDistance.Value))

qry.Parameters.Append paramZip
qry.Parameters.Append paramMiles

qry.CommandText = "ChurchDistance"
qry.ActiveConnection = CurrentProject.Connection

Set rs = qry.Execute()
rs.MoveNext

'Begin my added code
' Check to see that we built a filter
If IsNull(strWhere) Then
MsgBox "You must enter search criteria.", vbInformation,
gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rs.MoveLast
' If 5 or less or frmContacts already open,
If (rs.RecordCount < 6) Or
CurrentProject.AllForms("ChurchInformation").IsLoaded Then
'Or IsFormLoaded("Contacts")
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on church information form
Forms!ChurchInformation.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Your search found " & rs.RecordCount & "
churches. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "ChurchSummary", WhereCondition:=strWhere
' Make sure focus is on contact summary
Forms!ChurchSummary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on contacts
Forms!Contacts.SetFocus
End If
End If

' Done
'DoCmd.Close acForm, Me.Name
' Clean up recordset
rs.Close
Set rs = Nothing
End Sub


Minton M said:
I keep getting errors on the WHERE part of my query. I'm sure it's
something
simple.

I don't know if you will need the whole code to see what the problem
is. It
is lengthy, so I will start with just where the problem is.

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.*
FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

Thanks for the help.

Can you provide the WHERE clause (the code that produces strWhere)? I
would recommend breaking up the code - namely, state the query in a
string first so you can see what the entirety of the query will be and
then running that in a query designer window. This will show any
errors, often with more meaningful help that just throwing the query
at the DB engine.

By the way, I wouldn't use rs.RecordCount in the way you are trying to
use it - use rs.EOF instead. RecordCount is only populated when you
move to the end of the recordset whereas EOF always returns a reliable
true/false indicator.

-- James
Click to expand...
Click to expand...
Click to expand...
 
First of all, IsNull(strWhere) is meaningless, because variables declared as
String can never be Null (the only data type that can be Null is Variant)

I'm not sure why you're trying to do those tests after you've already opened
the recordset. Just remove that added code, and add additional logic before
your check for If (rs.RecordCount < 6)

You've declared rs as an ADO Recordset, yet you're trying to use DAO to
populate it the second time. While both the ADO and DAO models have an
object named Recordset in them, they're not interchangable.


--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doctor said:
[Distance_Miles] is one of the two paramaters necessary for the query to
run.

How do I make strWhere equal what the two paramaters are?

Here is what the two paramaters get designated as in the code:

I know that this works. What I am trying to accomplish in the lower
portion
of my code is run a series of tests on the query that was run based on the
paramaters that were entered into the form.
1. I want to show a message if no contacts are found in the search
2. If more than 5 records are found that meet the criteria, I want the
results to open in form ChurchSummary if the user chooses to.
3. Show the results in form ChurchInformation.

I do appreciate your feedback.


Douglas J. Steele said:
If the variable strWhere isn't populated, then the statement

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)

will raise an error because you've got the keyword WHERE in the SQL with
nothing after it.

What is DISTANCE_MILES? Is that there to generate a parameter prompt, or
is
it a field in one of the two tables?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Doctor said:
I see. Although strWhere was initialized it was never set. I know that
the
query works in the query designer. I've run it several times that way.
The
WHERE clause in the query is this: WHERE
(((T2.Distance)<=CDbl([DISTANCE_MILES])))

Side Note: T2 is a query that has a paramater of [ZipCode]

However from my form I am trying to set two paramaters (the other one
is
set
in T2) as the WHERE statement. Maybe I am going about this wrong. Can I
just
put: "WHERE paramZip, paramMiles" as my WHERE without using strWhere.
But
then in the lower portion of my code what would I use to test the
results
with IF statements?

Private Sub Search_Click()
Dim strWhere As String
Dim rs As ADODB.Recordset

'Set rs = SelectRadius.SelectRadius(txtZip.Value,
CDbl(txtDistance.Value))

Dim qry As New ADODB.Command
Dim paramZip As ADODB.Parameter
Dim paramMiles As ADODB.Parameter

Set paramZip = qry.CreateParameter("ZIP_CODE", adVarChar,
adParamInput,
255, txtZip.Value)
Set paramMiles = qry.CreateParameter("DISTANCE_MILES", adDouble,
adParamInput, 8, CDbl(txtDistance.Value))

qry.Parameters.Append paramZip
qry.Parameters.Append paramMiles

qry.CommandText = "ChurchDistance"
qry.ActiveConnection = CurrentProject.Connection

Set rs = qry.Execute()
rs.MoveNext

'Begin my added code
' Check to see that we built a filter
If IsNull(strWhere) Then
MsgBox "You must enter search criteria.", vbInformation,
gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

' Hide me to fix later focus problems
Me.Visible = False
' Move to last to find out how many
rs.MoveLast
' If 5 or less or frmContacts already open,
If (rs.RecordCount < 6) Or
CurrentProject.AllForms("ChurchInformation").IsLoaded Then
'Or IsFormLoaded("Contacts")
' Open Contacts filtered
' Note: if form already open, this just applies the filter
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on church information form
Forms!ChurchInformation.SetFocus
Else
' Ask if they want to see a summary list first
If vbYes = MsgBox("Your search found " & rs.RecordCount & "
churches. " & _
"Do you want to see a summary list first?", _
vbQuestion + vbYesNo, gstrAppTitle) Then
' Show the summary
DoCmd.OpenForm "ChurchSummary", WhereCondition:=strWhere
' Make sure focus is on contact summary
Forms!ChurchSummary.SetFocus
Else
' Show the full contacts info filtered
DoCmd.OpenForm "ChurchInformation", WhereCondition:=strWhere
' Make sure focus is on contacts
Forms!Contacts.SetFocus
End If
End If

' Done
'DoCmd.Close acForm, Me.Name
' Clean up recordset
rs.Close
Set rs = Nothing
End Sub


:

I keep getting errors on the WHERE part of my query. I'm sure it's
something
simple.

I don't know if you will need the whole code to see what the problem
is. It
is lengthy, so I will start with just where the problem is.

Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.*
FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

Thanks for the help.

Can you provide the WHERE clause (the code that produces strWhere)? I
would recommend breaking up the code - namely, state the query in a
string first so you can see what the entirety of the query will be and
then running that in a query designer window. This will show any
errors, often with more meaningful help that just throwing the query
at the DB engine.

By the way, I wouldn't use rs.RecordCount in the way you are trying to
use it - use rs.EOF instead. RecordCount is only populated when you
move to the end of the recordset whereas EOF always returns a reliable
true/false indicator.

-- James
Click to expand...
Click to expand...
Click to expand...
 
So if I start with the below code alone, how do I display the results of this
code in my Summary form (Like I was trying to do with the previous code with
strWhere)?

By the way, thank you for your help.

Private Sub Search_Click()
Dim rs As ADODB.Recordset
Dim qry As New ADODB.Command
Dim paramZip As ADODB.Parameter
Dim paramMiles As ADODB.Parameter

Set paramZip = qry.CreateParameter("ZIP_CODE", adVarChar, adParamInput,
255, txtZip.Value)
Set paramMiles = qry.CreateParameter("DISTANCE_MILES", adDouble,
adParamInput, 8, CDbl(txtDistance.Value))
qry.Parameters.Append paramZip
qry.Parameters.Append paramMiles
qry.CommandText = "ChurchDistance"
qry.ActiveConnection = CurrentProject.Connection
Set rs = qry.Execute()
rs.MoveNext
End Sub

The parameters in this code come from two queries as pasted below:

"ChurchDistance"
SELECT T2.Distance, T1.*
FROM (Church AS T1 INNER JOIN qryChurchZip1 ON
T1.ChurchID=qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON
qryChurchZip1.Zip5=T2.ZIPCode
WHERE (((T2.Distance)<=CDbl([DISTANCE_MILES])))
ORDER BY T2.Distance;

"DistanceQuery"
SELECT
CDbl(Distance(Point.Latitude,Point.Longitude,Region.Latitude,Region.Longitude)) AS Distance, Region.*
FROM ZIPCodes AS Point, ZIPCodes AS Region
WHERE (((Point.ZIPCode)=[ZIP_CODE]) AND ((Point.CityType)='D') AND
((Region.CityType)='D') AND ((Region.Latitude)<>0) AND
((Region.Longitude)<>0));
 
All I was suggesting to eliminate is the section:

'Begin my added code
' Check to see that we built a filter
If IsNull(strWhere) Then
MsgBox "You must enter search criteria.", vbInformation,
gstrAppTitle
Exit Sub
End If
' Open a recordset to see if any rows returned with this filter
Set rs = DBEngine(0)(0).OpenRecordset("SELECT T2.Distance, T1.* FROM
(Church AS T1 INNER JOIN qryChurchZip1 ON T1.ChurchID =
qryChurchZip1.ChurchID) INNER JOIN DistanceQuery AS T2 ON qryChurchZip1.Zip5
= T2.ZIPCode WHERE " & strWhere)
' See if found none
If rs.RecordCount = 0 Then
MsgBox "No Churches meet your criteria.", vbInformation,
gstrAppTitle
' Clean up recordset
rs.Close
Set rs = Nothing
Exit Sub
End If

Put your check for RecordCount = 0 just after the rs.MoveLast statement.
 
Back
Top