RE: Re-run a Query Based on Previous Results

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

Sorry, forgot to paste that in.

Public Function fnProgenitor(TableName as String, ID As Long) As Long

Dim strSQL As String
Dim rs As DAO.Recordset

Dim varParentID As Variant
Dim lngMyID As Long
varParentID = Null

lngMyID = ID
fnProgenitor = 0
Do
strSQL = "SELECT ID, Parent_ID FROM [" & TableName & "] " _
& "WHERE ID = " & lngMyID
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent_ID")) Then
fnProgenitor = rs("ID")
Exit Do
Else
lngMyID = rs("Parent_ID")
End If
rs.Close
Set rs = Nothing
Loop

End Function


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"RichUE" wrote:

Thanks Dale, but what is fnProgenitor()?
--
Richard

Search the web and raise money for charity at www.everyclick.com


"Dale Fye" wrote:

Rich,

Querying heirarchical datasets can be complicated. To assist in this
process, I have a function I use to determine the ROOT LEVEL parent (the
value of [Parent] field is NULL) of a particular item in a heirarchical
dataset. The code is shown below. Copy it and paste it in a public code
module, then modify the data types as appropriate for your dataset.

You could then build a query using this that looks like:

SELECT Component, Component_Description, _
Parent, Parent_Description
FROM yourTable
WHERE Component = [Enter Component Part #]
AND fnProgenitor([Component]) = [Enter Parent Part Number]

You may need to add some quotes to this if either of those fields is a string.

My first guess is that this will evaluate fnProgenitor() for every record in
your table. You might also want to try it as the following, and see which is
quicker.

SELECT *
FROM (
SELECT Component, Component_Description, _
Parent, Parent_Description
FROM yourTable
WHERE Component = [Enter Component Part #]) as temp
WHERE fnProgenitor(Temp.[Component]) = [Enter Parent Part Number]


--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.



"RichUE" wrote:

I would like to know whether the following can be done in one go, with
minimal input from the user, simply by entering a parameter and producing a
list of items.

We hold bill of material (BOM) details in a table called Bill. There are
Parent, Parent_Description, Component and Component_Description fields
included. I want to 'look' down a BOM structure for particular types of
Component.

First, I want to prompt for the Parent (an alpha-numeric part number), to
isolate those Components of interest, and then to apply an "either or" filter
to the Component field. The filter picks out two types: one is itself a
Parent and is to be used as the parameter when the query is run again; the
other is the Component of interest, which is to be captured in a list (or a
new table). The list is to include the four fields mentioned above.

I want the query to repeat for as many Parents as are found, and compile a
list of Components and which Parent they belong to.

Your help is apprceiated.
--
Richard
Using Access 97
Search the web and raise money for charity at www.everyclick.com
.

Relevant Pages

  • RE: Re-run a Query Based on Previous Results
    ... Function fnProgenitor(Fsbill As String, ... Dim rs As DAO.Recordset ... The value for ID is the first Parent in Fsbill. ...
    (microsoft.public.access.queries)
  • speeding up a file conversion from text file to XML format
    ... Public CURRENTFILE As String = "" ... Dim sr As New StreamReader ... lupOrigACNO = DataReader.ToString ... parent As Xml.XmlNode) ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Treeview
    ... I'll have a B please Bob. ... > Public DirectoryPath as string ... > Dim BaseFilePath as FilePath 'the root object> 'open a stream from the file and read each line into a local variable. ... > 'now get the parent address which should be the same for every line ...
    (microsoft.public.dotnet.languages.vb)
  • RE: Re-run a Query Based on Previous Results
    ... When you changed the datatype declaration on lngMyID to string, ... Let me rewrite fnProgenitor as I think it should probably read for your ... Dim rs As DAO.Recordset ... The value for ID is the first Parent in Fsbill. ...
    (microsoft.public.access.queries)
  • Search pattern
    ... Dim strfile As String ... Dim bAddressFound As Boolean ... Dim strCurrentChar As String ...
    (comp.databases.ms-access)