about DAO Recordset

[m_kristofer]

How do I filter Recordset in DAO?

Here's my code:

Dim ws As DAO.Workspace
Dim dbsaccess As DAO.Database
Dim recset As DAO.Recordset
Dim recset2 As DAO.Recordset
Dim rstTemp As DAO.Recordset

Set ws = DBEngine(0)
Set dbsaccess = ws.OpenDatabase("C:\MyAccess.mdb")
Set recset = dbsaccess.OpenRecordset("tblCheckINOUT")

rstTemp.Filter = "USERID = 9999" '<= Error Occurs here.
Set recset2 = rstTemp .OpenRecordset

...

Error says: Operation is not supported for this type of object.

What is the problem with my code?
Can anybody help me please?

 

[SammyJ]

In DAO if you just open a recordset based on a table name it opens a table-type recordset, which doesn't support the Filter property. Use the dbOpenDynaset with OpenRecordset to get a dynaset-type recordset and this should work.

I had to look this up though as I never really use the Filter property. Have you considered simply opening your recordset with the filter in place, so:

Set recset = dbsaccess.OpenRecordset("SELECT * FROM tblCheckINOUT WHERE USERID = 9999")

Sam.