built-in filter functions failing when form recordsource is set to recordset

[Patrick Gonzalez]

Is it possible to use the built-in filter functions (ie. "Filter by
Selection", etc..) when a form's recordsource has been set to a
recordset object during the load event. When I try to do this, I
receive the message "Cannot apply filter on one or more fields
specified in the filter property".

here is the code for the recordset creation:

Set conn = New ADODB.Connection
Set rsSQL = New ADODB.Recordset
gstrSQL = "SELECT CheckList.* " & _
"FROM CheckList " & _
"ORDER BY CheckList.RunDate DESC ,
CheckList.LoanNumber;"
rsSQL.Open gstrSQL, conn, adOpenStatic, adLockReadOnly
Set Me.frmCheckList.Form.Recordset = rsSQL

It doesn't matter if the recordset is ADO (as in this example) or DAO.
For DAO recodsets, I receive a different error.

[Michael \(michka\) Kaplan [MS]]

You can't, sorry. :-(
--
MichKa [MS]
NLS Collation/Locale/Keyboard Development
Globalization Infrastructure and Font Technologies
Windows International Division

This posting is provided "AS IS" with
no warranties, and confers no rights.

"Patrick Gonzalez" <pa**************@oomc.com> wrote in message
news:f3*************************@posting.google.co m...

Is it possible to use the built-in filter functions (ie. "Filter by
Selection", etc..) when a form's recordsource has been set to a
recordset object during the load event. When I try to do this, I
receive the message "Cannot apply filter on one or more fields
specified in the filter property".

here is the code for the recordset creation:

Set conn = New ADODB.Connection
Set rsSQL = New ADODB.Recordset
gstrSQL = "SELECT CheckList.* " & _
"FROM CheckList " & _
"ORDER BY CheckList.RunDate DESC ,
CheckList.LoanNumber;"
rsSQL.Open gstrSQL, conn, adOpenStatic, adLockReadOnly
Set Me.frmCheckList.Form.Recordset = rsSQL

It doesn't matter if the recordset is ADO (as in this example) or DAO.
For DAO recodsets, I receive a different error.

[Dimitri Furman]

Actually, it works here just fine for disconnected recordsets based on
stored procedures (Access 2002 and 2003, ADO 2.7 and 2.8, SQL Server 2000
SP3a). I would make sure that the CursorLocation property of the recordset
is set to adUseClient and that its ActiveConnection property is set to
Nothing before binding the recordset to the form. Setting the UniqueTable
property of the form to any non zero-length string makes the underlying
disconnected recordset updatable, if that is needed.

The only issue I saw with this technique so far is that Access raised an
untrappable error "Data Provider could not be initialized" when removing
filter/sort via Access UI, but that can be worked around in the ApplyFilter
event, by cancelling the filter and re-binding the original unfiltered
recordset to the form.

On Jul 12 2004, 12:24 pm, "Michael \(michka\) Kaplan [MS]"
<mi*****@online.microsoft.com> wrote in news:40********@news.microsoft.com:

You can't, sorry. :-(

"Patrick Gonzalez" <pa**************@oomc.com> wrote in message
news:f3*************************@posting.google.co m...

Is it possible to use the built-in filter functions (ie. "Filter by
Selection", etc..) when a form's recordsource has been set to a
recordset object during the load event. When I try to do this, I
receive the message "Cannot apply filter on one or more fields
specified in the filter property".

here is the code for the recordset creation:

Set conn = New ADODB.Connection
Set rsSQL = New ADODB.Recordset
gstrSQL = "SELECT CheckList.* " & _
"FROM CheckList " & _
"ORDER BY CheckList.RunDate DESC ,
CheckList.LoanNumber;"
rsSQL.Open gstrSQL, conn, adOpenStatic, adLockReadOnly
Set Me.frmCheckList.Form.Recordset = rsSQL

It doesn't matter if the recordset is ADO (as in this example) or DAO.
For DAO recodsets, I receive a different error.

--
remove a 9 to reply by email