Repeating SQL statement in module??? OpenRecordset-

[Apollo6]

I have the following code which I am trying to determine if any records are returned given the criteria in the SQL statement. When I step through the code and it attempts to set rs with the results, I can see in the debug window that the SQL statement that is built is being repeated three times!? There will be two records that meet the condition and I'm not sure if that is related to why my SQL statement is being repeated...

Here is the code:


Option Compare Database
Option Explicit

Public db As Database
Public rs As Recordset
Public SQL As String

Public Sub Renew_Credentials()

Set db = CurrentDb

SQL = SQL & &quot;SELECT Count(*) FROM Physician_Insurance WHERE (((Physician_Insurance.Renew_Credential_Date1)<=Date()+60) AND ((Physician_Insurance.Noted)=No))&quot;
Set rs = db.OpenRecordset(SQL, dbOpenDynaset)
If (rs.RecordCount >= 1) Then
MsgBox &quot;Records&quot;
Else
MsgBox &quot;No Records&quot;
End If

End Sub


Am I making this too difficult? All I want to do is see if the query returns any records. Any suggestions are appreciated.

 

[Apollo6]

Figured it out if anyone has the same need... I used a DCount function to return the number of records. Much easier way to get same results.

Code:

Public Sub Renew_Credentials()

Dim iCnt As Integer

iCnt = DCount (&quot;[Renew_Credential_Date1]&quot;, &quot;Physician_Insurance&quot;, &quot;[Renew_Credential_Date1]<=Date()+60 AND [Noted] = No&quot
If iCnt >= 1 Then
MsgBox &quot;Records&quot;
Else
MsgBox &quot;No Records&quot;
End If

End Sub