Insanity with Runtime Error 3219

[sanderson]

Hello:

Here is my code, which worked fine a few hours ago, and I have made no changes to this part of the program...

All the way at the bottom is rs.close. This happened once before, and it just started magically working the next day. Maybe I will get lucky in the morning. Anyhow, the rs.close produces the error 3219 - Operation not allowed in this context. When I remove, the error goes away but the program does not execute.

Thank you.

Private Sub cmdSubmitTimeCard_Click()
    Dim rs As adodb.recordset
    Set rs = New adodb.recordset


    Dim sql As String
   
    sql = "SELECT * FROM tblocalApprovalLog "
    sql = sql & "WHERE (empid = '" & empid & "' OR supid = '" & Forms!frmTimeCard.txtEmpID.Value & "') "
    sql = sql & "AND approve = True "
    sql = sql & "AND approved = False "
    sql = sql & "AND dayDate = '" & Forms!frmTimeCard.txtDate.Value & "' "
    sql = sql & "AND weekDate = '" & Forms!frmTimeCard.txtPeriodStart.Value & " - " & Forms!frmTimeCard.txtPeriodEnd.Value & "' "
    sql = sql & "AND shortchar02 = 'Salaried'"


    Debug.Print ("sql: " & sql)
      
    rs.Open sql, CurrentProject.Connection, adOpenDynamic, adLockOptimistic
    On Error GoTo endloop


    Dim c As Integer
    c = 0
        
    Do While Not rs.BOF Or rs.EOF
        c = c + 1
        Debug.Print ("RecordCount: " & CStr(c))


        ' On Error GoTo endloop
        txtEmpID = rs.Fields("empid")
        rs.Fields("approved") = True
                            
        ' Send EMail
        GenerateEmailContent_Weekly (txtEmpID)
        Debug.Print ("Email # " & c & " has been sent.")
            
        rs.MoveNext
        
    Loop


endloop:
    Me.Requery
    rs.Close
    Set rs = Nothing


End Sub

[ItsMe]

If there are not any records retrieved with .Open you will not be able to .Close. At least that is how it is with DAO. I usually check that recordcount is greater than zero directly after.Open and exit sub if not.

[sanderson]

This is the query produced, and it does produce data!

SELECT * FROM tblocalApprovalLog WHERE (empid = '2255' OR supid = '2261') AND approve = True AND approved = False AND dayDate = '11/6/2015' AND weekDate = '11/2/2015 - 11/8/2015' AND shortchar02 = 'Salaried'

Is there a better way to check for recordset validity than the way I'm doing it? Perhaps the rs.EOF or rs.BOF is not good??

Thanks!

[ItsMe]

Anytime you use a WHERE clause within a query, there will be a chance that zero records will be retrieved. I do not have all of the nuances of ADO memorized. I am not certain what the best approach is for determining a valid recordset. Over the years, while using DAO, I have used different approaches and, now, use something like the following ...

If rs.Recordcount < 1 then
Set rs = Nothing
exit sub
end if

I do this because a Dynaset or Snapshot in DAO will always retrieve at least one record from a valid recordset, even without Movelast. I do not believe this to be much different than testing for both EOF and BOF. The takeaway should be that if you do not have any records, if you are on EOF or BOF, set the rs to Nothing and exit the procedure without trying to .Close an invalid recordset that is not in an Open state.