RE: Re-run a Query Based on Previous Results

My browser says your time is 6:21 AM PST. What dedication! I wouldn't have
had a drink by then either! It's 14:30 GMT here.

We're moving on now. I've cleared another couple of minor errors and here's
the SQL I'm working with:
SELECT Fsbill.COMPONENT, Fsbill.COMP_DESC, Fsbill.PARENT, Fsbill.PARNT_DESC
FROM Fsbill
WHERE Fsbill.COMPONENT = ["Enter Component Part Number:"]
AND fnProgenitor("Fsbill", Fsbill.PARENT) = ["Enter Parent Part Number:"];

And here's the code. I modified the arguments and lngMyID to type String:

Function fnProgenitor(Fsbill As String, ID As String) As Long

Dim strSQL As String
Dim rs As DAO.Recordset

Dim varParentID As Variant
Dim lngMyID As String
varParentID = Null

lngMyID = ID
fnProgenitor = 0
Do
strSQL = "SELECT ID, Parent_ID FROM [" & Fsbill & "] " _
& "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

At this stage I'm getting the following run-time error:
Syntax error (missing operator) in query expression 'ID = 0-021875GA'.
The value for ID is the first Parent in Fsbill.

If I click End on this message box, the following is shown:
This expression is typed incorrectly, or it is too complex to be evaluated ...
Clicking Debug highlights the "Set rs = Current..." line in the code.
--
Richard

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


"Dale Fye" wrote:

Sorry, you can tell it is still early and I haven't had my cup of coffee yet.

Yes, it does require two, in the version I sent. This version includes a
TableName (string) as the first argument in the function, so you can use it
with other heirarchical datasets. So you need to modify the criteria to:

fnProgenitor("Fsbill", [Fsbill].[COMPONENT]) = [Enter Parent Part Number];

Are [Component] and [Parent] strings, or long integer values? You may also
need to declare the datatype of the values of your two parameters.

Have you modified fnProgenitor to use the correct field names for your table
structure? I always use ID and Parent_ID as the fields that define the
parent-child relationships, so you will need to modify fnProgenitor( ) with
the correct fields names.

--
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'm using the first query from your post, and I've addapted it accordingly.
But when I try to run it, and error is displayed, saying that the number of
arguments is wrong in the WHERE clause:
Fsbill.COMPONENT = [Enter Component Part #]
AND fnProgenitor([Fsbill.COMPONENT]) = [Enter Parent Part Number];

Does fnProgenitor expect two arguments?
--
Richard

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


"Dale Fye" wrote:

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

  • 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)
  • RE: Re-run a Query Based on Previous Results
    ... Public Function fnProgenitor(TableName as String, ... Dim rs As DAO.Recordset ... Dim varParentID As Variant ... I have a function I use to determine the ROOT LEVEL parent (the ...
    (microsoft.public.access.queries)
  • RE: Re-run a Query Based on Previous Results
    ... When you declare the parameters, you are declaring the values you plan on ... value of fnProgenitor from long to string, and the default value from 0 to ... You will need to do that too, or the function will not return a string ... Dim rs As DAO.Recordset ...
    (microsoft.public.access.queries)
Loading