ADO recordset question

Hello all.

This is such a basic question, but I can't find an answer.

I have a function in a class module that finds data from a series of
database tables. The data, when found, is placed into a new recordset within
the function. All OK here.
Now, where I am stuck is how do I make a query from this query?

For example, I have my new recordset in my function that contain all of the
data that I have extracted from the database tables, now I want to search
that recordset for a further sub set of data, that is where I am stuck. OK,
I could bung it all into a temporary table, but that will hit the
performance of my app. So I thought of querying the query instead

A snippet of code to illustrate follows:

'In a class module

Public Function Some_Function(strTable As String, strMedianTbl As String,
strField As String, strPast As String, sSurvey As Integer, ByVal LabID As
Long) As String


Dim NewRS As New ADODB.Recordset
Dim RsResult As New ADODB.Recordset

Dim ReagentID As Integer 'The ReagentID for that survey
Dim WHOSurvey As Integer 'The WHOSurvey for that survey

ReagentID = 0
WHOSurvey = 0

With cnNEQAS
.Open
.CursorLocation = adUseClient

With NewRS.Fields 'First define the fields of the new recordset
.Append "LabID", adInteger, 4
.Append "Survey", adInteger, 4
.Append "Used", adVarChar, 7
.Append "Dev", adSingle, 7
End With

With NewRS
.Open
End With


strSQL = "SELECT SomeTable.Survey, SomeTable.LabID, SampleNo." & strPast
& ", SomeTable.ReagentID " _
& "FROM SomeTable INNER JOIN WHOSampleNo ON SomeTable.Survey =
SampleNo.SurveyN " _
& "Where (((SomeTable.LabID) = " & LabID & ") AND
((SomeTable.Survey)<= " & sSurvey & ")) " _
& "ORDER BY SomeTable.Survey"

rsGeneric.Open strSQL, cnNEQAS, , , adCmdText
Set rsGeneric.ActiveConnection = Nothing
strSQL = ""

If rsGeneric.RecordCount > 0 Then
With rsGeneric
.MoveFirst
Do While Not .EOF

ReagentID = !ReagentID
WHOSurvey = !Survey

'here I save the data that matches my criteria

If RsResult.RecordCount > 0 Then
With NewRS
'Push into a new recordset
.AddNew
!LabID = RsResult!LabID
!Survey = RsResult!Survey
!Dev = (Format(RsResult![Dev], "###0.00"))
.Update
.MoveNext
End With
Set RsResult = Nothing
   >>>>>>>>>>ALL OK UP TO HERE<<<<<<<<<<<


ELSE

.....Some other action
......
....
End If
Loop
etc

<<<<<<<'HERE IS WHERE I AM STUCK>>>>>>>>

'Query the recordset (NewRs) obtained from above and do something with it.

strSQL = "SELECT TOP 6 NewRs.Survey, NewRs.LabID, NewRs.Ratio " _
& "FROM NewRs " _
& "ORDER BY NewRs.Survey ASC"
RsResult.Open strSQL, cnNEQAS, , , adCmdText
Set RsResult.ActiveConnection = Nothing
strSQL = ""

Obviously, here I get an error that states that there is no table or query
called NewRS in the database
..........................

How do I query this NewRs please?


Thank you for any help that you can give me.

Best regards

David clifford

The best you would be able to do is use the Find or Filter methods of the
recordset - note both are limiting..

Find:
<a rel="nofollow" style='text-decoration: none;' href="http://www.devguru.com/Technologies/ado/quickref/recordset_find.html" target="_blank">http://www.devguru.com/Technologies/ado/quickref/recordset_find.html</a>

Filter:
<a rel="nofollow" style='text-decoration: none;' href="http://www.devguru.com/Technologies/ado/quickref/recordset_filter.html" target="_blank">http://www.devguru.com/Technologies/ado/quickref/recordset_filter.html</a>


--
Chris Hanscom - Microsoft MVP (VB)
Veign's Resource Center
<a rel="nofollow" style='text-decoration: none;' href="http://www.veign.com/vrc_main.asp" target="_blank">http://www.veign.com/vrc_main.asp</a>
--

"David Clifford" wrote in message

 > Hello all.
 >
 > This is such a basic question, but I can't find an answer.
 >
 > I have a function in a class module that finds data from a series of
 > database tables. The data, when found, is placed into a new recordset
within
 > the function. All OK here.
 > Now, where I am stuck is how do I make a query from this query?
 >
 > For example, I have my new recordset in my function that contain all of
the
 > data that I have extracted from the database tables, now I want to search
 > that recordset for a further sub set of data, that is where I am stuck.
OK,
 > I could bung it all into a temporary table, but that will hit the
 > performance of my app. So I thought of querying the query instead
 >
 > A snippet of code to illustrate follows:
 >
 > 'In a class module
 >
 > Public Function Some_Function(strTable As String, strMedianTbl As String,
 > strField As String, strPast As String, sSurvey As Integer, ByVal LabID As
 > Long) As String
 >
 >
 > Dim NewRS As New ADODB.Recordset
 > Dim RsResult As New ADODB.Recordset
 >
 > Dim ReagentID As Integer 'The ReagentID for that survey
 > Dim WHOSurvey As Integer 'The WHOSurvey for that survey
 >
 > ReagentID = 0
 > WHOSurvey = 0
 >
 > With cnNEQAS
 > .Open
 > .CursorLocation = adUseClient
 >
 > With NewRS.Fields 'First define the fields of the new recordset
 > .Append "LabID", adInteger, 4
 > .Append "Survey", adInteger, 4
 > .Append "Used", adVarChar, 7
 > .Append "Dev", adSingle, 7
 > End With
 >
 > With NewRS
 > .Open
 > End With
 >
 >
 > strSQL = "SELECT SomeTable.Survey, SomeTable.LabID, SampleNo." &
strPast
 > & ", SomeTable.ReagentID " _
 > & "FROM SomeTable INNER JOIN WHOSampleNo ON SomeTable.Survey =
 > SampleNo.SurveyN " _
 > & "Where (((SomeTable.LabID) = " & LabID & ") AND
 > ((SomeTable.Survey)<= " & sSurvey & ")) " _
 > & "ORDER BY SomeTable.Survey"
 >
 > rsGeneric.Open strSQL, cnNEQAS, , , adCmdText
 > Set rsGeneric.ActiveConnection = Nothing
 > strSQL = ""
 >
 > If rsGeneric.RecordCount > 0 Then
 > With rsGeneric
 > .MoveFirst
 > Do While Not .EOF
 >
 > ReagentID = !ReagentID
 > WHOSurvey = !Survey
 >
 > 'here I save the data that matches my criteria
 >
 > If RsResult.RecordCount > 0 Then
 > With NewRS
 > 'Push into a new recordset
 > .AddNew
 > !LabID = RsResult!LabID
 > !Survey = RsResult!Survey
 > !Dev = (Format(RsResult![Dev], "###0.00"))
 > .Update
 > .MoveNext
 > End With
 > Set RsResult = Nothing
   > >>>>>>>>>>ALL OK UP TO HERE<<<<<<<<<<<
 >
 >
 > ELSE
 >
 > ....Some other action
 > .....
 > ...
 > End If
 > Loop
 > etc
 >
 > <<<<<<<'HERE IS WHERE I AM STUCK>>>>>>>>
 >
 > 'Query the recordset (NewRs) obtained from above and do something with it.
 >
 > strSQL = "SELECT TOP 6 NewRs.Survey, NewRs.LabID, NewRs.Ratio " _
 > & "FROM NewRs " _
 > & "ORDER BY NewRs.Survey ASC"
 > RsResult.Open strSQL, cnNEQAS, , , adCmdText
 > Set RsResult.ActiveConnection = Nothing
 > strSQL = ""
 >
 > Obviously, here I get an error that states that there is no table or query
 > called NewRS in the database
 > .........................
 >
 > How do I query this NewRs please?
 >
 >
 > Thank you for any help that you can give me.
 >
 > Best regards
 >
 > David clifford
 >
 >
 >
 >