filter recordset

[Liz]

I have tired to set up a filter for a recordset, but it does not seem to work.


Set rstTemp = CurrentDb.OpenRecordset("Entry")
rstTemp.Filter = "ClassNumber = '12'"
Set rstFiltered = rstUnfiltered.OpenRecordset

When I try to run this I get a runtime error 3251 on the second line.

Can anyone help on how to assign the filter? The ClassNumber field is a
text field. I eventually want to construct a string so the class number can
be incremented, but if I can get the filter to work at all I will be
delighted!

 

[Klatuu]

That is not the way I would do it. I would suggest you use a query with a
WHERE clause rather than using the filter property. It is actually faster.

 

[Tom Wickerath]

Hi Liz,

It might be easier (and faster as well) to use a SQL statement to accomplish
the same goal. Something like this:

Set rstTemp = CurrentDb.OpenRecordset( _
"SELECT * FROM Entry WHERE ClassNumber = '12'")



Tom Wickerath
Microsoft Access MVP
http://www.accessmvp.com/TWickerath/
__________________________________________

 

[CraigH]

Tom and Dave are correct that this is not the best way to do it.

But to answer you question: lines 1 and 2 look good to me it is 3 that
looks bad - you apply the filter to rstTemp but in 3 you open rstUnfiltered -
at this point without seeing the rest of the code this is undefined. So it
should be
Set rstFiltered = rstTemp.OpenRecordset
Unless it is because you haven't defined rstTemp as a recordset and that is
why line 2 is giving you the error.

And to give you an example of another way of doing this (With your expanded
needs - incrementing the number):
First I don't know why you would make a number a text in this situation
so I am going to assume the change to a long number.

Dim rstFiltered As DAO.Recordset
Dim strSQL as String
Dim lngClassNumber As Long

lngClassNumber = 12
Do

strSQL = "SELECT * FROM Entry WHERE ClassNumber = " & lngClassNumber
Set rstFiltered = CurrentDb.OpenRecordset(strSQL)
....
Do whatever you need
....

lngClassNumber = lngClassNumber + 1
Until lngClassNumber = (Somthing)

The code is just a basic example - and should be changed on what your need
is, For example
If you need to be able to select different Class number at the start and
increment through a few of them then this should be in a function and
lngClassNumber would be a passed variable.

Or you may want to set the SQL statment up so that you get the values for
the class number between 2 numbers and sort the SQL on the Class number. And
then you can process what you need to do in order.

Just some ideas to think about.

Craig

 

[Liz]

Thanks to you all.
Klatuu's post helped me with the initial problem, but you all helped me with
further code for the same database.


Liz