none
RecordSet not a qualified reference RRS feed

  • 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?

    Thursday, February 16, 2017 2:36 AM

All replies

  • Hi,

    Just a thought, you had this:

    .OpenRecordset

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

    Hope it helps...

    Thursday, February 16, 2017 2:56 AM
  • You might try a

    Do While Not RS.EOF

     .......

    Loop

    --instead of ubound, lbound


    Rich P

    Thursday, February 16, 2017 10:41 PM
  • 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





    Thursday, February 16, 2017 10:52 PM