Filter recordset based on a form

[Chewie via AccessMonster.com]

I am trying to create a recordset, based on a filtered form. The problem
seems to be that rst.filter doesn't accept a form's filter. Code example :

Public Function TestMe()
Dim dbs As DAO.Database
Dim rst As DAO.Recordset
Set dbs = DBEngine(0)(0)
Set rst = dbs.OpenRecordset(Form_Name.RecordSource, dbOpenSnapshot)
rst.Filter = Form_Name.Filter
rst.Sort = Form_Name.OrderBy
Set rst = rst.OpenRecordset()
'==> ERROR
End Function

? TestMe
? err.Number
3061
? err.Description
Too few parameters. The expected number is: 2.
? Form_Name.Filter
((tblTable.Field="Content"))
? Form_Name.OrderBy
tblTable.Field DESC

The same problems persists with the rst.Sort method. It's not possible to
create the form with a query already assigned as recordsource. It should
be a table that is filtered. Is there any way to create a "real" filtered
and ordered recordsource from a form ?

 

[Alex Dybenko]

Maybe you have references to a form in Form_Name.RecordSource and it cause
error?

BTW - you can also use form's recordsetclone property to achieve same
results

 

[Chewie via AccessMonster.com]

The form's recordsource : tblTable
The form's filter : ((tblTable.FieldName="FieldValue"))

To use the recordset's filter, we need to get rid of "tblTable" in the
form's filter. Apparently, Access places the table name automatically
before the field name when applying filters. The same happens with sorting
the form.

PS : recordsetclone has the same issue

 

[Alex Dybenko]

So what is your final idea?
what do you mean with "recordsetclone has the same issue"?
if you write:
Set rst = me.recordsetclone
you also get error?

 

[Chewie via AccessMonster.com]

Seems it's not possible to apply a form filter or order by to a recordset
filter or sort. The syntax of the form cannot be used for a recordset :

FORM : ((tblTable.FieldName="FieldValue"))
RECORDSET : ((FieldName='FieldValue'))

FORM : tblTable.FieldName DESC
RECORDSET : FieldName DESC

The purpose would be to create a complete, filtered recordset, based on a
form where there are already filters applied. It doesn't make a difference
if you code it with the recordsetclone method or just create a new
recordset trough code.

 

[Bitsqueezer]

Hi,

although this is an old thread, I found this when I was searching for a solution about this.
Now I found a solution on my own and maybe it's useful for other users searching for the same issue:

It's right, Access adds the form name into the filter string but there is a simple solution to use this inside of a query. If the underlying table of the form has the name "MyTable" and the form has the name "MyForm" and you want to use a very much faster SQL command to access the filtered records, simply replace the table name like this (in a command button inside the filtered form for example):

Dim rs As DAO.Recordset 
Set rs = Me.RecordsetClone
 
CurrentDb.Execute "UPDATE MyTable SET MyField = 'Whatever' WHERE " & _
Replace(Me.Filter, "[MyForm].", "")

Tested with Access 2007.

In the same way you can of course use the replaced filter to set it onto a recordset.

Cheers,

Christian