VB Forum / Database Access / February 2005

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

 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