ADO .Filter Method causes "Data Provider Cannot be Initialized" Error

[StrikeEagleII]

I have a form with subform that is bound to an ADO recordset (Access 2003 to SQL Server 2008 R2). I have two buttons on the subform - one to apply a filter and one to remove it. At first, I was just using the following code to apply the filter:

      intOldItem = Me.cboItemID
      Set rsClone = Me.Recordset.Clone

      Screen.PreviousControl.SetFocus      
      DoCmd.RunCommand acCmdFilterBySelection
      
      rsClone.Find "[ItemID] = " & intOldItem
      Me.Bookmark = rsClone.Bookmark

The first time I do this it works great. However if I try to remove the filter by

    Set rsClone = Me.Recordset.Clone
        
    DoCmd.RunCommand acCmdRemoveFilterSort
      
    rsClone.Find "[ItemID] = " & intOldItem
    Me.Bookmark = rsClone.Bookmark

or apply an additional filter, I get the error "Cannot Initialize Data Provider" when trying to create the recordset clone.

I also tried to set the filter by

      intOldItem = Me.cboItemID
      
      Me.FilterOn = False
      Me.OrderByOn = False 'This was the "solution" to this problem in another forum post

      Set rsClone = Me.Recordset.Clone

      If Me.Filter = "" Then
         Me.Filter = Me.txtFilter
      Else
         Me.Filter = Me.Filter & " AND " & Me.txtFilter
      End If
      Me.FilterOn = True

      rsClone.Find "[ItemID] = " & intOldItem
      Me.Bookmark = rsClone.Bookmark

and remove it by

   intOldItem = Me.cboItemID
    
    Me.FilterOn = False
    Me.OrderByOn = False
    
    Set rsClone = Me.Recordset.Clone
    
    Me.Filter = ""
      
    rsClone.Find "[ItemID] = " & intOldItem
    Me.Bookmark = rsClone.Bookmark

where txtFilter is an unbound textbox that contains what the filter should be (i.e Owner = 'Manufacturing') but the results are the same - works great the first time and bombs everytime afterwards with the error above (and not only causes the error, but when I put the form back in design view, it causes access to crash)

Any thoughts?

 

[vbajock]

I may be wrong on this, but I think recordsetclone objects can only be assigned to DAO recordsets in A2003.

 

[MajP]

As stated the forms recordsetclone only returns a DAO recordset, but that is not what the OP is doing. They are cloning an AODB recordset using the clone method.
recordset.clone not recordsetclone

If the form is bound to and AODB recordset the recordset property will return the adodb recordset and the clone method will return an adodb recordset clone.

I do not have access here to test. But It appears that you are not having problem retrieving the AODB recordset bound to the form. So why not apply the filter method to the forms recordset.

dim rs as aodb.recordset
'assuming you bound the form to an aodb recordset
set rs = me.recordset
rs.filter = me.txtfilter

n ADO, the Filter property applies directly to the Recordset object on which you set the filter. The ADO Filter property allows you to create a temporary view that can be used to locate a particular record or set of records within the current Recordset object. When a filter is applied to the current Recordset object, the RecordCount property reflects just the number of records available after you apply the filter. The filter can be removed by setting the Filter property to adFilterNone.

If the CursorLocation Property is set to adUseClient (use the client cursor engine), the Filter property will work if MDAC 2.0 or later is installed but will not work properly with earlier versions of ADO.

 

[vbajock]

This might help:

http://support.microsoft.com/kb/281998

 

[StrikeEagleII]

Here's my latest attempt:

Private Sub cmdFilter_Click()

   If Me.NewRecord = False And Me.txtFilter <> "" Then
      intOldItem = Me.cboItemID
      
      Set rst = Me.Recordset
      strFilter = Me.txtFilter
      
      If Me.Recordset.Filter = 0 Then
         rst.Filter = strFilter
      Else
         strFilter = rst.Filter & " AND " & strFilter
         rst.Filter = adFilterNone
         rst.Filter = strFilter
         
      End If
      Debug.Print rst.Filter
      
      Set Me.Recordset = rst
      Set rsClone = Me.Recordset.Clone
      rsClone.Find "[ItemID] = " & intOldItem
      Me.Bookmark = rsClone.Bookmark
   End If

This works better (doesn't throw an error), however if I filter for a second item or third item then access just crashes. i added the line to remove the exitsting filter before applying a new filter with additional criteria and that inconsistently helped (sometimes it crashes on the second time sometimes on the third)

On the main form I have a combo box that is initally used to set the recordset of the subform based on the value in the combo box...I will probably just create 4 additional combo boxes on the main form to extend that to the other fields I want to filter. That's probably a better solution anyway since it's more consistent.

 

[MajP]

There are some limits on the criteria of a filter. You may be violating that as you chain the filter. However, you would not expect it to crash the system. Any chance you can debug.print the filter before applying it. That way you can see if it is an offending filter string

The Criteria argument can be three or more conditions with the following restrictions:

The operators must be the same for all conditions. If the operators are "LIKE", then the filtered region may be unexpected.
In all cases, if the "=" operator is used, then the column names specified in the where clause must be keyed columns in the file.

One restriction on these combinations is that OR clauses can only be used at the highest (major) level of the logical operation.

Examples of acceptable Criteria meeting these conditions are:

recordset.Filter = "(Title='Manager' AND Salary>30000) OR (Title='Administrator' AND Salary>50000)"
recordset.Filter ="Salary > 30000.0"
An example of illegal Criteria is:

recordset.Filter = "(Title='Associate' OR Salary >30000) AND (Title='Administrator')"
The operator can also use wildcards (* or %) in character expressions as follows:

recordset.Filter = "Lastname LIKE '*SMITH*'"

You may be better off building a new sql string and just return a new recordset.