Re: Finding Records

From: Frank Hickman [MVP] (fhickman_NOSP_at_M_noblesoft.com)
Date: 01/25/05 

Date: Tue, 25 Jan 2005 10:56:22 -0500

"Geoff Jones" <nodamnspam@email.com> wrote in message
news:41f6582f$0$7599$cc9e4d1f@news.dial.pipex.com...
> Hi
>
> Can anybody give me some advice on the following?
>
> Suppose I have two tables: A and B. There is a one to many relationship
> between A and B. I would like to find all the child rows in B for a given
> row in A. Then, from these returned rows, I would like to find all the
> rows which satisfy a given criteria.
>
> With me so far? :)
>
> I know how to create a relationship between A and B, and have been able to
> get the child rows. However, the only way I can think to find the rows in
> the last step is to go through every row and check the criteria to see if
> it is satisfied:
>
> For Each row As DataRow In ChildRowsReturnedByRelationship
> If row.Item("Name") = "Geoff" Then
> ' Do something
> EndIf
> Next
>
> which works for me but I don't feel it is as good or efficient as it could
> be.
>
> I know that for a DataTable, you can use something like "FindRows". Is
> there an equivalent I could use for some returned rows e.g. something
> like:
>
> ChildRowsReturnedByRelationship.FindRows("Geoff")
>
> ???
>
> Thanks in advance
>
> Geoff
>

Why not just limit the returned rows to the criteria you need, then you can
just operate on the rows together?

SELECT * FROM Table2
WHERE ( FK = SELECT PK FROM Table1 WHERE Id = SomeID )
AND ( Name = 'Geoff' )

Or you can set a filter to further shrink the result set, assuming
ChildRowsReturnedByRelationship is a recordset...

' This comes from the MSDN docs...
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

Set FilteredRS = FilterField( ChildRowsReturnedByRelationship, "Name",
"Geoff" )

For Each row As DataRow In FilteredRS
    ' Do something
Next 

-- 
============
Frank Hickman
Microsoft MVP
NobleSoft, Inc.
============
Replace the _nosp@m_ with @ to reply.