filter recordset

  • Thread starter Thread starter Liz
  • Start date Start date
L

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!
 
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.
 
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/
__________________________________________
 
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
 
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
 
Back
Top