DAO Recordset Filter Not Working

[billharrison9]

I'm having a problem filtering a recordset and would appreciate your
suggestions on what's going wrong. I apply the filter to the first
recordset, then create another recordset from the filtered recordset.
My understanding is that this should work, but it isn't.

Here's the code


Dim dbInfo As Database
Dim rstLeadershipMasterFilteredBySelectedL3 As DAO.Recordset
Dim rstLeadershipFilteredForSector As DAO.Recordset

Set dbInfo = CurrentDb()

'Open the first recordset
Set rstLeadershipMasterFilteredBySelectedL3 =
dbInfo.OpenRecordset("tblLeadershipMaster_FanChartData", dbOpenDynaset)

'Now, we apply the filter to the recordset
Debug.Print "Running sector " & strSector
rstLeadershipMasterFilteredBySelectedL3.Filter =
strFilterValue
Debug.Print rstLeadershipMasterFilteredBySelectedL3.Filter

'Open a recordset from the filtered recordset
Set rstLeadershipFilteredForSector =
rstLeadershipMasterFilteredBySelectedL3.OpenRecordset()

'Output a count of the records to see if the filter worked.
rstLeadershipFilteredForSector.MoveLast
Debug.Print "Record Count is " &
rstLeadershipFilteredForSector.RecordCount
rstLeadershipFilteredForSector.MoveFirst


The output remains unfiltered. Can you tell me what I've missed?

Thanks in advance!

 

[Tim Ferguson]

'Open a recordset from the filtered recordset
Set rstLeadershipFilteredForSector =
rstLeadershipMasterFilteredBySelectedL3.OpenRecordset()

I'd do it in one step:

jetSQL = " SELECT Something " & _
" FROM tblLeadershipMaster_FanChartData" & _
" WHERE " & strFilterValue & _
" ORDER BY Something;"

Set rstLeadershipFilteredForSector = dbInfo. _
OpenRecordset(jetSQL, dbOpenSnapshot, dbForwardOnly)



The most important piece of information that you didn't show us was the
contents of strFilterValue. It is meant to be the text of an SQL WHERE
clause without the WHERE keyword.

Hope that helps.


Tim F