Filter recordset error

[Gwy]

Ive looked through the posts and nobody seems to have this problem: I
want to filter a DAO recordset. I understand that you must copy the
recordset after you filter it. I also understand that it would be alot
more simple to put the restrictions in my WHERE clause, however, for
the purpose of my project, this is not possible/efficient.

Here is an idea of my code:

Private Function test()

Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim strSQL As String

Set db = OpenDatabase(CurrentProject.Path & "\" &
CurrentProject.Name, False, False)
strSQL = "SELECT Pop.SubCode, Pop.Region " & _
"FROM Pop" & _
"WHERE (Pop.Region = 1) " & _
"ORDER BY Pop.Size DESC;"
'My SQL is alot more complexe then this... this is just to give you an
idea

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.Filter = "Pop.SubCode = 9999"

Set rsFilter = rs.OpenRecordset() '***** MY ERROR OCCURS AT THIS
LINE

MsgBox rs.RecordCount

rs.Close
rsFilter.Close
db.Close

End Function

The error is the following:
"Run-time error '3061': Too few parameters. Expected 2."

However the parameters that are expected are optional. I tried filling
them in with various values to no avail.

TIA,
Gwy

 

[Dirk Goldgar]

Ive looked through the posts
Bravo!

and nobody seems to have this problem: I
want to filter a DAO recordset. I understand that you must copy the
recordset after you filter it. I also understand that it would be alot
more simple to put the restrictions in my WHERE clause, however, for
the purpose of my project, this is not possible/efficient.

Here is an idea of my code:

Private Function test()

Dim db As Database
Dim rs As Recordset
Dim rsFilter As Recordset
Dim strSQL As String

Set db = OpenDatabase(CurrentProject.Path & "\" &
CurrentProject.Name, False, False)

I'm puzzled. This sounds like you're reopening the current database.
Is there some reason you don't use

Set db = CurrentDb

or

Set db = DBEngine.Workspaces(0)(0)

?

strSQL = "SELECT Pop.SubCode, Pop.Region " & _
"FROM Pop" & _
"WHERE (Pop.Region = 1) " & _
"ORDER BY Pop.Size DESC;"
'My SQL is alot more complexe then this... this is just to give you an
idea

Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
rs.Filter = "Pop.SubCode = 9999"

Set rsFilter = rs.OpenRecordset() '***** MY ERROR OCCURS AT THIS
LINE

MsgBox rs.RecordCount

rs.Close
rsFilter.Close
db.Close

End Function

The error is the following:
"Run-time error '3061': Too few parameters. Expected 2."

However the parameters that are expected are optional. I tried filling
them in with various values to no avail.

There are no parameters in the sample code you posted, so I can't tell
what your best approach to fixing the problem will be. If your
parameters are in the Filter string, you should certainly be able to
resolve them in advance and build the string with literal values instead
of parameters. If your parameters are in the original query, then you
must already be doing something to resolve them.

Please post either the actual code or an example that is closer to the
real situation.

 

[Alex Martínez]

Ive looked through the posts and nobody seems to have this problem: I
want to filter a DAO recordset. I understand that you must copy the
recordset after you filter it.

Sounds like an error in the Filter condition.
Check the field name you are using.

I tried this code:

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = DBEngine.OpenDatabase("K:\COMB\comb.mdb")
Set rs = db.OpenRecordset("SELECT * FROM local_tAltColFili",
dbOpenSnapshot)
rs.MoveLast
rs.MoveFirst
Debug.Print rs.RecordCount
rs.Filter = "NOM = ""JOSE MARTINEZ"""
Set rs = rs.OpenRecordset()
rs.MoveLast
rs.MoveFirst
Debug.Print rs.RecordCount

and it worked fine.
Then, I changed the field name I was using (NOM is a valid field, but NOMP
is not):
rs.Filter = "NOMP = ""JOSE MARTINEZ"""
and I got the error you describe.