RecordSet not a qualified reference

Question

I am pulling through a query to create a calendar. I am trying to bring the different information into each window but it says that my rsfiltered is not qualified to be an openrecordset. I have underlined the problem in the following so its easier to see. This code ran perfectly when I had an exact date in the rsfilter before I added the lbound & ubound etc but now it is not bringing up my fields in the immediate window.

Public Sub LoadArray()

Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim rsFiltered As DAO.Recordset
Dim strSQL As String
Dim i As Integer


strSQL = "SELECT Events.[ID], Events.[Title], Events.[Location], Events.[Post Code], Events.[Description], Events.[Use Again] FROM Events ORDER BY Events.[Post Code], Events.[Use Again] DESC;"


'Debug.Print strSQL



    
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL)




    If Not rs.BOF And Not rs.EOF Then
    
        For i = LBound(myArray) To UBound(myArray)
            rs.Filter = "[Use Again]=" & myArray(i, 0)
            Set rsFiltered = .OpenRecordset
            
            Do While (Not rsFiltered.EOF)
            
                myArray(i, 2) = myArray(i, 2) & vbNewLine _
                & rsFiltered!Title & " - " _
                & rsFiltered!Location & " " _
                & rsFiltered!PostCode & " " _
                & rsFiltered!Description & " " _
                & rsFiltered!UseAgain
                
                
            
                rsFiltered.MoveNext
            
            Loop
            Debug.Print myArray(i, 2)
            
        Next i
    
    End If
    rs.Close


Set rs = Nothing
Set db = Nothing



End Sub

Any ideas?

Answer 1

Hi,

Just a thought, you had this:

.OpenRecordset

but I didn't see any With/End With block.

Hope it helps...

Answer 2

You might try a

Do While Not RS.EOF

 .......

Loop

--instead of ubound, lbound

---

Rich P

Answer 3

Would

Set rsFiltered = .OpenRecordset

not need to be

Set rsFiltered = rs.OpenRecordset

You should also be cleaning up after your rsFiltered  object

Here's more along the lines of what I'd being doing

Public Sub LoadArray()
    On Error GoTo Error_Handler
    Dim db                    As DAO.Database
    Dim rs                    As DAO.Recordset
    Dim rsFiltered            As DAO.Recordset
    Dim strSQL                As String
    Dim i                     As Integer

    strSQL = "SELECT Events.[ID], Events.[Title], Events.[Location], Events.[Post Code], Events.[Description], Events.[Use Again]" & vbCrLf & _
             " FROM Events" & vbCrLf & _
             " ORDER BY Events.[Post Code], Events.[Use Again] DESC;"
    'Debug.Print strSQL

    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL)
    If rs.RecordCount <> 0 Then
        For i = LBound(myArray) To UBound(myArray)
            rs.Filter = "[Use Again]=" & myArray(i, 0)
            Set rsFiltered = rs.OpenRecordset
            If rsFiltered.RecordCount <> 0 Then
                Do While Not rsFiltered.EOF
                    myArray(i, 2) = myArray(i, 2) & vbNewLine _
                                    & rsFiltered!Title & " - " _
                                    & rsFiltered!Location & " " _
                                    & rsFiltered!PostCode & " " _
                                    & rsFiltered!Description & " " _
                                    & rsFiltered!UseAgain
                    rsFiltered.MoveNext
                Loop
                '            Debug.Print myArray(i, 2)
            End If
        Next i
    End If

Error_Handler_Exit:
    On Error Resume Next
    rsFiltered.Close
    Set rsFiltered = Nothing
    rs.Close
    Set rs = Nothing
    Set db = Nothing
    Exit Sub

Error_Handler:
    MsgBox "The following error has occured" & vbCrLf & vbCrLf & _
           "Error Number: " & Err.Number & vbCrLf & _
           "Error Source: LoadArray" & vbCrLf & _
           "Error Description: " & Err.Description & _
           Switch(Erl = 0, "", Erl <> 0, vbCrLf & "Line No: " & Erl) _
           , vbOKOnly + vbCritical, "An Error has Occured!"
    Resume Error_Handler_Exit
End Sub

What is the data type of the field [Use Again]?  Number, Boolean?  Or something else?  It doesn't need quotes around the value?

---

Daniel Pineault, 2010-2016 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net