Consulting

Results 1 to 2 of 2

Thread: recordset.filter not filtering and i dont know why

  1. #1

    recordset.filter not filtering and i dont know why

    Hi all.

    thanks for any help.

    below you will find a function that converts an array to a records set. seeing as the array size can change you will notice i use loops to define the field names. you will therefore have to trust me that their is a fieldname named period with a value of 1. the dataformat is set to varchar.

    i've tried every combination of the .filter method that i can think of. can someone please take a stab at editing my code such that the filter works.

    thanks



    Private Function ConvertToRecordset(arrValues As Variant) As Recordset
    
        Dim oRs  As New Recordset
        Dim lRecCount
        Dim i As Integer
        Dim FieldName As String
        
        lRecCount = UBound(arrValues)
        Num_col = UBound(arrValues, 2)
        
        
        For icol = 0 To Num_col
        FieldName = arrValues(0, icol)
           
        oRs.Fields.Append FieldName, adVarChar, adFldMayBeNull
        
        Next icol
    
    
        oRs.Open
        
        
        
        For irow = 1 To lRecCount
        
        If (lRecCount > 0) Then
              oRs.AddNew
    
    
              
            For icol = 0 To Num_col
            FieldName = arrValues(0, icol)
            oRs(FieldName).Value = arrValues(irow, icol)
            Next icol
              
    
    
       End If
       
       Next irow
      
       
    
    
    
       
       Set ConvertToRecordset = oRs
       
       'up to here works
    
    
    '"some_field NOT LIKE '%ABC%' "
    ConvertToRecordset.Filter = "Period = '1'"
     
       
       
    With ThisWorkbook.Sheets("Sheet1").QueryTables.Add( _
            Connection:=ConvertToRecordset, _
            Destination:=ThisWorkbook.Sheets("Sheet1").Range("A13"))
        .Name = "Contact List"
        .FieldNames = True
        .RowNumbers = False
        .FillAdjacentFormulas = False
        .PreserveFormatting = True
        .RefreshOnFileOpen = False
        .BackgroundQuery = True
        .RefreshStyle = xlInsertDeleteCells
        .SavePassword = True
        .SaveData = True
        .AdjustColumnWidth = True
        .RefreshPeriod = 0
        .PreserveColumnInfo = True
        .Refresh BackgroundQuery:=False
    End With
       
       
       
    End Function
    Last edited by samuelimtech; 04-20-2017 at 04:05 AM.

  2. #2
    VBAX Expert
    Joined
    Oct 2012
    Posts
    726
    Location
    Your code seems a little convoluted.


    I'm sure you can just copy an array to Excel.
    Since this is a disconnected recordset I don't see what you'd gain from adding it as a connection (assuming it would allow you to) because you can't refresh it.


    Anyway, you need to open a recordset from your recordset.


    oRs.Filter = "Period = '1'"
    Set ConvertToRecordset = oRs.openrecordset

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •