Recordset Filtering

[George]

Good Morning,



The following code is used to filter a table called "Class", however a runtime error 3251 returns, stating "Operation is not supported by this type of object. what can be the problem?

Private Sub Command0_Click()
Dim curDatabase As Database
Set curDatabase = CurrentDb

Dim rst As Recordset

Set rst = CurrentDb.OpenRecordset("Class")
rst.Filter = "Year = 2011" ' The problem is here.

MsgBox rst.RecordCount

Set rst = Nothing
End Sub

[NTC]

You might explore the DCount method as an approach.

HTH

[RuralGuy]

You should really disambiguate your variables:
Dim curDatabase As DAO.Database
Set curDatabase = CurrentDb
Dim rst As DAO.Recordset

And then you have the problem that Year is a reserved word in VBA.
http://www.allenbrowne.com/AppIssueBadWord.html#Y

You might try brackets to get around the issue:
rst.Filter = "[Year] = 2011"

Do you really have a field named YEAR? Is it Numeric or a String?

[RuralGuy]

I'm going to take a flyer and say the REAL problem is:
From VBA Help on Filter:
Use the Filter property to apply a filter to a dynaset-, snapshot-, or forward-only–type Recordset object.

The default for OpenRecordset if nothing is specified is a Table so it probably does not like the Filter.

Change your code to:
Set rst = CurrentDb.OpenRecordset("Class", dbOpenDynaset)
...and I suspect the error will go away.

[George]

This is my updated code. My new problem is that the Rs2.RecordCcount is giving a number of 1 when there are may more records in this filter. The Rs1.RecordCount is now working fine. Can the reason for this problem be explained?

Private Sub Command0_Click()
Dim db As Dao.Database, rs1 As Dao.Recordset
Dim rs2 As Dao.Recordset

Set db = currentDb
Set rs1 = db.OpenRecordset("Select * from Class", dbOpenDynaset)

rs1.Filter = "TheYear = 2008 "
Set rs2 = rs1.OpenRecordset

MsgBox rs1.RecordCount
MsgBox rs2.RecordCount

Set rs2 = Nothing: Set rs1 = Nothing
Set db = Nothing

End Sub

[RuralGuy]

RecordCount is not guaranteed accurate until after you have done a .MoveLast.

[RuralGuy]

When you first open a Recordset, you are at the beginning and if there are *any* records then .RecordCount will be non zero. If the RecordCount is important to you then it is an excellent time to do a .MoveLast and then a .MoveFirst.

[George]

Thanks to you experts my problem is solved by the movelast and Movefirst Methods

[George]

I am grateful to you. My problem is resolved

[RuralGuy]

Glad we could help. I marked this thread Solved for you.