Use filter on recordset only in code 1

[fmientjes]

In a function I trie to use a filter, but I am having problems with syntax (I think).

Function test(strLineCode as string) as string
dim rs as recordset, strfilter as string
set rs=currentdb.openrecordset("tblTest&quot

strfilter ='" & strLineCode & "'"
rs.Filter = strfilter

Unfortunately it gives back all records, so no filter applied.
Can someone give me a clue?
I need to use a filter because of speed reasons (need to open the same table several times)

Thanks,
Frans

 

[FancyPrairie]

Note that the Filter property doesn't apply to table-type recordsets. Also, the new filtering will never retrieve additional rows from the original source table.

Try this:

dim rst as recordset
dim rstFiltered as recordset

set rs=currentdb.openrecordset("Select * From tblTest&quot
rs.Filter = strfilter
Set rstFiltered = rst.OpenRecordset()

 

[cmmrfrds]

If you make your recordset an ADO recordset you can use the filter or even sort the recordset. You are correct the filter is very efficient, but as FancyPrairie says the filter will only work on the records already retrieved.

 

[fmientjes]

Thanks for your useful answers. Strange (for me) that the result of a filter can only be retrieved with setting it to another recordset.
Does it mean that I can do the following:

set rs=currentdb.openrecordset("tblTest&quot
'Step 1
strfilter1="Line_code=..."
rs.Filter = strfilter1
Set rsFiltered = rst.OpenRecordset()
If rsFiltered.EOF = False Then
zz = rsFiltered!Price
Else
'Step 2
strfilter2="Country=..."
rs.Filter = strfilter2
Set rsFiltered = rst.OpenRecordset()
If rsFiltered.EOF = False Then
zz = rsFiltered!Price
Else
.....

Is this the quickest way? In this case the recordset rs needs to be openend only once, but rsFiltered every time.
Or is it better to adjust everything to ADO (how?)?
Because of large the number of records and complex filtering, speed is important.

Thanks again,
Frans

 

[FancyPrairie]

There are times when it's faster to open a new recordset based on a SQL expression rather than using the Filter property. You'll just have to time it and see which is fastest.

 

[cmmrfrds]

This is not tested code but it should be close - anyhow you get the idea.

Dim rs as New ADODB.Recordset
Dim cn as New ADODB.Connection

Set cn = CurrentProject.Connection
rs.CursorLocation = adUseClient
rs.CursorType = adOpenStatic

rs.open "tblTest", cn,,,adCmdTable

rs.Filter = "Line_code=..."
'- do something on filtered data
rs.Filter = adFilterNone
Else
'Step 2
rs.Filter = "Country=..."
'- do something on filtered data
rs.Filter = adFilterNone
Else
.....

Other options are to disconnect the recordset from the connection while you are working on it. You can also index the fields you want to filter on before using the filter.

rs.("line_code&quot.Properties("OPTIMIZE&quot = True
rs.("Country&quot.Properties("OPTIMIZE&quot = True

Can sort if needed.
rs.sort = "Country desc"