Connection Error (3709) in Opening Recordset

[axkoam]

Windows 7, MS Access 2007

I'm getting "Run-time error '3709': The connection cannot be used to perform this operation. It is either closed or invalid in this context.

My scenario is that I'm using a script similar to the one below on a form, and then this slightly altered version below for another form if the logic permits it. The original script on the 1st form works, but when it runs from this form I get the error noted above. I've been reading a lot about this error but I haven't been able to solve it yet.

Any ideas?

This code is supposed to take any records that have the Boolean EmailSent not checked and send emails to those. Emails are created/sent through the exporthtml routine near the end of this routine.

Public Function parse_WorkflowNew()
    Dim rs As ADODB.Recordset, str_getSend As String
    Dim rs_Missing As ADODB.Recordset
 
    Set rs = New ADODB.Recordset
    Set rs_Missing = New ADODB.Recordset
 
    rs_Missing.Open "Select Mfg_Cd from q_AuthToRoute Where [E-Mail] is null Group by Mfg_Cd", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
    rs.Open "Select [E-Mail] from q_AuthToRoute Where [E-Mail] is not null Group by [E-Mail]", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly
 
    If Not rs.EOF And Not rs.BOF Then
        If EmailSent = False Then
        rs.MoveFirst
        Do
                Dim rs_Data As ADODB.Recordset
                Set rs_Data = New ADODB.Recordset
                --------rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "' And EmailSent = No, CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly"
                If Not rs_Data.BOF And Not rs_Data.EOF Then
                    rs_Data.MoveFirst
                    Dim str_Table As String
                End If
                exporthtml rs.Fields("E-Mail"), rs_Data
            rs.MoveNext
        Loop Until rs.EOF
    End If
    End If
 
End Function

Debugging highlights the line with --------

Someone suggested the 'EmailSent = No' part being the issue but it doesn't seem to matter if I set it to EmailSent = False....or 0

It seems the error has to do with the connection, probably because I pulled the script over from a Form to a module, even though I modified it. I'm not sure why that would cause this error though.

 

[axkoam]

Well I had a simple quotation error in this line:

rs_Data.Open "Select * From q_AuthToRoute Where [E-Mail] = '" & rs.Fields("E-Mail") & "' And EmailSent = No", CurrentProject.Connection, adOpenForwardOnly, adLockReadOnly

But now I'm getting an error that says "No value for one or more required parameters" on that same line. I'm confused because that doesn't seem to be the case when I research "Open Method (ADO Recordset)".

Anyone see what's going on?

 

[axkoam]

I had an attribute that wasn't in the q_AuthToRoute!! ARRRGG