runtime error 91-- object variable or with block variable not set

[asatyss]

I'm trying to run a loop through records that looks at all of the records related to a certain SiteCode and then checks to see if certain flags are checked. I'm doing it in a nested loop because there are many SiteCodes but I want to take them one at a time.

Anyway, I am getting a runtime error 91-- object variable or with block variable not set on the line that says "rst2.Open SQL2, CurrentProject.Connection". Any help would be appreciated.

Private Sub Command0_Click()
Dim rst As ADODB.Recordset
Set rst = New ADODB.Recordset
Dim rst2 As ADODB.Recordset
Set rst2 = New ADODB.Recordset
Dim sitecode As Integer
Dim SQL As String

SQL = "SELECT SiteCodes.[SITE CODE]FROM SiteCodes;"
rst.Open SQL, CurrentProject.Connection
Do Until rst.EOF
sitecode = rst("Site Code")

SQL2 = "SELECT * FROM Sheet1 WHERE [SITE CODE]=" & sitecode & ";"
rst2.Open SQL2, CurrentProject.Connection
rst2.MoveFirst
Dim MKISConsent As String
Dim CIDSConsent As String
Dim ConsentType As String
MKISConsent = rst2("CONSENT FLAG")
CIDSConsent = rst2("Field15")
ConsentType = rst2("CONSENT TYPE CODE")
Do Until rst2.EOF
'Case of when there is a V Consent Type in the first line, if so, move to next SiteCode
'If not, go to next record
If MKISConsent = CIDSConsent And ConsentType = "V" Then
Exit Do
End If

'If we have a record that MKISConsent is a Y and Field18 is a N with a ConsentType of V, kick out a Message
If rst2("CONSENT TYPE CODE") = "V" And rst2("Field18") = "N" And MKISConsent = "Y" Then
MsgBox (rst2("SITE CODE"))
Exit Do
End If

rst2.MoveNext
Loop
'End If
rst2.Close
Set rst2 = Nothing
rst.MoveNext
Loop

rst.Close
Set rst = Nothing
End Sub

 

[PHV]

Put the following after the Loop:
Set rst2 = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[asatyss]

PHV-

I have that in there, is it in the incorrect spot?

Thanks for your help.

 

[PHV]

Did you replace this:
rst2.Close
Set rst2 = Nothing
rst.MoveNext
Loop
rst.Close
with this ?
rst2.Close
rst.MoveNext
Loop
Set rst2 = Nothing
rst.Close

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886