Dear all,
    I've two simple sub procedure, one for recordset.filter and the other
dump the records:
===========================
Sub test_recordset_filter()
    Dim db As Database, rst As Recordset, rstFilter As Recordset

    Set db = CurrentDb
    Set rst = db.OpenRecordset("myinventory", dbOpenDynaset)
    rst.Filter = "[price]>=1000 AND [price]<=1000"
*    dump_recordset (rst)

End Sub
------------------------------
Sub dump_recordset(rst As Recordset)
    Dim rstFilter As Recordset
    Set rstFilter = rst.OpenRecordset
    Dim fld As Field

    Do Until rstFilter.EOF
        For Each fld In rstFilter.Fields
            Debug.Print fld.Value; " ";
        Next fld
    Loop
End Sub
====================
    When I run the code, type mismatch appeared and stopped at the * line.
What's wrong?

Best regards,
Questioner

Questioner (?)

Try below.

Sub test_recordset_filter()
    Dim db As Database, rst As Recordset, rstFilter As Recordset, fld As Field

    Set db = CurrentDb
    Set rst = db.OpenRecordset("z", dbOpenDynaset)
*   'this is the same as rst.Filter = "[price] = 1000"
    rst.Filter = "[price]>=1000 AND [price]<=1000"

*  'forgot to use "Call"
   Call dump_recordset(rst)

End Sub
Sub dump_recordset(rst As Recordset)
    Dim rstFilter As Recordset, fld as Field
    Set rstFilter = rst.OpenRecordset

*   'check that there is data in the recordset
       If rstFilter.EOF And rstFilter.BOF Then
       Debug.Print "The recordset is empty"
    Else
        'move to the beginning of the recordset
        rstFilter.MoveFirst
        Do Until rstFilter.EOF
            For Each fld In rstFilter.Fields
                Debug.Print fld.Value; " ";
            Next fld
            'move to the next record in the recordset
            rstFilter.MoveNext
        Loop
        'stop printing on same line
        Debug.Print
    End If
End Sub

From the Help File Example:

Tip   In most situations, it's more efficient to create the second Recordset
object with the desired conditions in one step. When you know what data you
want to select, it's generally more efficient to create a recordset with an
SQL statement. The next example shows how you can create just one recordset
and obtain the same results as in the preceding example:

Sub CreateRecordsetWithSQL()
 Dim dbs As Database, rst As Recordset
 Dim strInput As String

 Set dbs = CurrentDb
 strInput = InputBox("Enter name of country on which to filter.")
 Set rst = dbs.OpenRecordset("SELECT * FROM Orders " _
  & "WHERE ShipCountry = '" & strInput & "';")
 rst.MoveLast
 MsgBox "Recordset contains " & rst.RecordCount & " records."
 rst.Close
 Set dbs = Nothing
End Sub

--
Mark Gordon

314-647-8880x352

What version of Access are you running?  In Access 2000, you may need to Dim
all your Recordset objects As DAO.Recordset, to distinguish this ambiguous
type name from the ADODB.Recordset that is also available.
Database.OpenRecordset returns a DAO recordset, not an ADODB.Recordset, but
A2K by default puts the ADO library higher in the reference list than the
DAO library (if you have a reference to DAO at all).

--
Dirk Goldgar
www.datagnostics.com

(to reply via e-mail, remove NOSPAM from address)