Very Computer

I swear I've done this, but I can't find the code, and the intuitive
approach isn't working.  I'm working with VB5 and DAO 3.5.

If I have a recordset call rsMain that is valid and contains x
records and was properly created using a SQL statement:

strSQL = "SELECT * FROM Invoice Where ((Invoice.Date) = #" & _
txtCheckDate & "# ORDER BY InvoiceNo;"

set rsMain = dbData.OpenRecordset(strSQL)

then I want to create a new recordset based on rsMain, using a new
strSQL I would think that:

set rsNew = rsMain.OpenRecordset(strSQL)

would do it.  I want to hit against the recordset because it's
infinitely smaller then the original database,  so I'm trying to avoid
simply adding my 2 SQL criterias together and going against my
original database.

Any help would be appreciated.

On Fri, 29 Aug 1997 14:30:58 -0400, Srinivas Kanchibhotla >

Quote:>   Welcome to the group of VB 3.0 mourners. In VB3.0, your problem can
>be solved by using the clone method. i.e.
>    Set rsNew = rsMain.clone()
>and By using the filter property with the where criterion, you can have
>the desired recordset without hitting the database again. i.e.
>    rsNew.filter = "[column1] = blah and [column2] = blah"
>    Set rsNew = rsNew.createsnapshot[createdynaset]()
>But with VB5, the Clone method can be applied on to ODBC-direct
>databases. If yours is one, then U are lucky. You can use the same
>method described above. But with RD0 2.0, you have no alternative but
>creating another resultset with the modified query.

Stu

There are two workarounds, both of which can be done in 3.0, 4.0, or
5.0 using any compatible version of DAO:

(1) Create a clone of the first recordset, using the Clone method of
the Recordset object. Then apply the Filter and/or Sort methods of the
Recordset object on the clone to select the records you want to see
and order them the way you want them ordered.

(2) Instead of creating the first recordset, use a Make Table query
"SELECT field1[, field2[, ...]] INTO newtable FROM sourcetable") to
create a new, temporary table. You can then create as many recordsets
as you want from the temporary table. When you're done with the
temporary table, use a DROP TABLE SQL statement to blow it away.

Stu

Michael Schamberger:

Quote:>then I want to create a new recordset based on rsMain, using a new
>strSQL I would think that:

>set rsNew = rsMain.OpenRecordset(strSQL)

The setting or return value is a String data type that contains the WHERE
clause of an SQL statement without the reserved word WHERE.

Remarks

Use the Filter property to apply a filter to a dynaset-, snapshot-, or
forward-onlytype Recordset object.
You can use the Filter property to restrict the records returned from an
existing object when a new Recordset object is opened based on an existing
Recordset object.
In many cases, it's faster to open a new Recordset object by using an SQL
statement that includes a WHERE clause.
Use the U.S. date format (month-day-year) when you filter fields containing
dates, even if you're not using the U.S. version of the Microsoft Jet
database engine (in which case you must assemble any dates by concatenating
strings, for example, strMonth & "-" & strDay & "-" & strYear). Otherwise,
the data may not be filtered as you expect.

If you set the property to a string concatenated with a non-integer value,
and the system parameters specify a non-U.S. decimal character such as a
comma (for example, strFilter = "PRICE > " & lngPrice, and lngPrice =
125,50), an error occurs when you try to open the next Recordset. This is
because during concatenation, the number will be converted to a string using
your system's default decimal character, and Microsoft Jet SQL only accepts
U.S. decimal characters.
=========
So, it gets set, but only takes affect when you create a new recordset based
on the old one:

rsMain.Filter = strNewSQLWhereClauseOnly
set rsNew = rsMain

Or, the Help file's example is:

This example uses the Filter property to create a new Recordset from an
existing Recordset based on a specified condition. The FilterField function
is required for this procedure to run.

Sub FilterX()

 Dim dbsNorthwind As Database
 Dim rstOrders As Recordset
 Dim intOrders As Integer
 Dim strCountry As String
 Dim rstOrdersCountry As Recordset
 Dim strMessage As String

 Set dbsNorthwind = OpenDatabase("Northwind.mdb")
 Set rstOrders = dbsNorthwind.OpenRecordset("Orders", _
  dbOpenSnapshot)

 ' Populate the Recordset.
 rstOrders.MoveLast
 intOrders = rstOrders.RecordCount

 ' Get user input.
 strCountry = Trim(InputBox( _

"Enter a country to filter on:"))

 If strCountry <> "" Then
  ' Open a filtered Recordset object.
  Set rstOrdersCountry = _
   FilterField(rstOrders, "ShipCountry", strCountry)

  With rstOrdersCountry
   ' Check RecordCount before populating Recordset;
   ' otherwise, error may result.
   If .RecordCount <> 0 Then .MoveLast
   ' Print number of records for the original
   ' Recordset object and the filtered Recordset
   ' object.

strMessage = "Orders in original recordset: " & _
    vbCr & intOrders & vbCr & _
    "Orders in filtered recordset (Country = '" & _
    strCountry & "'): " & vbCr & .RecordCount
   MsgBox strMessage
   .Close
  End With

 End If

 rstOrders.Close

 dbsNorthwind.Close

End Sub

Function FilterField(rstTemp As Recordset, _
 strField As String, strFilter As String) As Recordset

 ' Set a filter on the specified Recordset object and then

' open a new Recordset object.
 rstTemp.Filter = strField & " = '" & strFilter & "'"
 Set FilterField = rstTemp.OpenRecordset

End Function

Note   To see the effects of filtering rstOrders, you must set its Filter
property, and then open a second Recordset object based on rstOrders.

Note   When you know the data you want to select, it's usually more
efficient to create a Recordset with an SQL statement. This example shows
how you can create just one Recordset and obtain records from a particular
country.

Sub FilterX2()

 Dim dbsNorthwind As Database
 Dim rstOrders As Recordset

 Set dbsNorthwind = OpenDatabase("Northwind.mdb")

 ' Open a Recordset object that selects records from a
 ' table based on the shipping country.
 Set rstOrders = _
  dbsNorthwind.OpenRecordset("SELECT * " & _
  "FROM Orders WHERE ShipCountry = 'USA'", _
  dbOpenSnapshot)

 rstOrders.Close
 dbsNorthwind.Close

End Sub