Filtering a form based on ADODB recordset? (1 Viewer)

Hi,

I have a form that shows records from ADODB recordset.
When I try to apply filter to the underlying recordset it works all right but the form doesn't reflect the changes. It shows same rows as before filtering. In debug I can see that the recordset contains only filtered records.
Me.Refresh (Recalc, Requery) doesn't help.

Code is as follows:

Dim rs As New ADODB.Recordset
rs.Open sql, conn, adOpenStatic, adLockOptimistic
Set Me.Recordset = rs

Sub combo_AfterUpdate()
Me.Recordset.Filter = "CompanyNo = 123"
End Sub

The form is in Continuous forms mode.
I cant use DAO because the data comes from SQL server user-defined function.

Any help would be greatly appreciated.

What about the below, (not tested).

After filtering the recordset reload it to the form with:

Set Me.Recordset = Me.Recordset

JHB said:

What about the below, (not tested).

Code:Copy to clipboard

Sub combo_AfterUpdate()
   Me.Filter = "CompanyNo = 123"
   Me.FilterOn = True
End Sub

Click to expand...

I tried it and the line Me.FilterOn = True failes with error "Object doesn't support this property or method". The problem is ADODB.Recordset doesn't have FilterOn property. It is filtered when I update the Filter property, I can see it in watch window, but the form doesn't show it..

Galaxiom said:

After filtering the recordset reload it to the form with:

Set Me.Recordset = Me.Recordset

Click to expand...

Thanks, but it failes with error "Invalid procedure call or argument"....

dribak said:

Thanks, but it failes with error "Invalid procedure call or argument"....

Click to expand...

It is what I have used to requery forms based ADODB recordsets. Maybe it is for disconnected recordsets only.

Try resetting the form's recordset again to requery.

dribak said:

Code:Copy to clipboard

Sub combo_AfterUpdate()
Me.Recordset.Filter = "CompanyNo = 123"
End Sub

Click to expand...

Not like this.The filter should be applied directly to the recordset object.

rs.Fliter = "CompanyNo = 123"