Re-run a Query Based on Previous Results

[RichUE]

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.

 

[Dale Fye]

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]

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

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

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.

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.

 

[Dale Fye]

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.

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

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

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.

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.

 

[RichUE]

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

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.

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

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

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.



:

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.

 

[Dale Fye]

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.

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

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.

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

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


:

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.



:

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.

 

[RichUE]

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

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.

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

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.



:

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

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


:

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.



:

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.

 

[Dale Fye]

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

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

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.

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


:

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.



:

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

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


:

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.



:

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.

 

[RichUE]

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

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

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.



:

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


:

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.



:

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

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


:

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.



:

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.

 

[Dale Fye]

When you declare the parameters, you are declaring the values you plan on
entering when the query is run. Since you are planning on typing values into
input boxes for the component and the parent parts, those are the parameters
you should be declaring. In this case, the Parameters line should look
something like:

PARAMETERS [Enter Component Part Number:] Text (15),
[Enter Parent Part Number:] Text (15) ;

I would set the # of characters (I used 15) to the same number of characters
defined for the fields (Component, and Parent) in your table.

Also, I just noticed that I failed to change the declaration of the return
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
value.

Function fnProgenitor(Tablename as string, ID as String) as String

fnProgenitor = ""

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

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


:

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.



:

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


:

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.



:

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

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


:

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.



:

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.

 

[RichUE]

Thanks again, Dale. I modified the PARAMETERS declaration, but Access 97
won't accept the character length. I made that small modification to the
function.

At this point, Debug kicks in with "Too few parameters. Expected: 1" and
highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options, lockedits)"

I added an extra "," but this gave the error "Invalid argument". I tried
moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

--
Richard

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

When you declare the parameters, you are declaring the values you plan on
entering when the query is run. Since you are planning on typing values into
input boxes for the component and the parent parts, those are the parameters
you should be declaring. In this case, the Parameters line should look
something like:

PARAMETERS [Enter Component Part Number:] Text (15),
[Enter Parent Part Number:] Text (15) ;

I would set the # of characters (I used 15) to the same number of characters
defined for the fields (Component, and Parent) in your table.

Also, I just noticed that I failed to change the declaration of the return
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
value.

Function fnProgenitor(Tablename as string, ID as String) as String

fnProgenitor = ""

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

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


:

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.



:

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


:

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.



:

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

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


:

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

 

[Dale Fye]

Rich,

That particular error implies that the SQL string is not formatted properly.

When it bombs, and asks whether you to end or debug, select debug. Then, in
the immediate window at the bottom of the VB editor type:

?strSQL

Then copy and paste what it prints into another message. My guess is there
is a " missing. You might also want to post the most recent copy of the code
for fnProgenitor.

I'll be out of the net for a couple of hours.
--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks again, Dale. I modified the PARAMETERS declaration, but Access 97
won't accept the character length. I made that small modification to the
function.

At this point, Debug kicks in with "Too few parameters. Expected: 1" and
highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options, lockedits)"

I added an extra "," but this gave the error "Invalid argument". I tried
moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

--
Richard

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

When you declare the parameters, you are declaring the values you plan on
entering when the query is run. Since you are planning on typing values into
input boxes for the component and the parent parts, those are the parameters
you should be declaring. In this case, the Parameters line should look
something like:

PARAMETERS [Enter Component Part Number:] Text (15),
[Enter Parent Part Number:] Text (15) ;

I would set the # of characters (I used 15) to the same number of characters
defined for the fields (Component, and Parent) in your table.

Also, I just noticed that I failed to change the declaration of the return
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
value.

Function fnProgenitor(Tablename as string, ID as String) as String

fnProgenitor = ""

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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


:

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

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


:

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.



:

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


:

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.



:

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

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


:

Rich,

 

[Bob Barrows [MVP]]

Thanks again, Dale. I modified the PARAMETERS declaration, but Access
97 won't accept the character length. I made that small modification
to the function.

At this point, Debug kicks in with "Too few parameters. Expected: 1"
and highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

That is totally irrelevant. That error is a result of a problem in your
sql statement.

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options,
lockedits)"

I added an extra "," but this gave the error "Invalid argument". I
tried moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

The error says nothing about arguments. You are focusing on the wrong
thing. The "too few parameters" error has a couple possible causes:
1. You've misspelled a field name somewhere in your sql statement - when
Jet encounters an object name that cannot be resolved, it treats it as a
parameter, and looks for a corresponding parameter value that should
have been passed.
2. You have deliberately put parameters in your sql statement but have
failed to supply values for those parameters via the Querydef's
Parameters collection.

Looking back over this thread, I see that you want the user to be
prompted for some parameter values. That means Access needs to be
involved. When you open a recordset via DAO, Access is not involved, so
there can be no automatic prompting for parameter values. YOU have to
prompt the user for values and supply them to the query via the Querydef
object's Parameters collection. If you want Access to prompt the user
automatically, then you need to forget about opening a recordset: the
only way to get Access to run a query is to use the DoCmd.RunSQL method.
Since it appears you require a recordset, you need to prompt the user
for values (using Inputbox or better: an unbound form) and supply the va
lues the user enters to a Querydef's Parameters collection, like this:

dim db as database, qdf as querydef, rs as dao.recordset
dim sql as string, component as string,parentpart as string
component =inputbox("Enter component")
parentpart=inputbox("Enter parent part")
'validate that data was entered
sql = "SELECT Fsbill.COMPONENT, Fsbill.COMP_DESC, " & _
"Fsbill.PARENT, Fsbill.PARNT_DESC " & _
"FROM Fsbill " & _
"WHERE COMPONENT = [Enter Component Part Number] " & _
"AND fnProgenitor(""Fsbill"", Fsbill.PARENT) = " & _
"[Enter Parent Part Number:]"
set db=currentdb
set qdf=db.createquerydef("",sql)
qdf(0)=component
qdf(1)=parentpart
set rs=qdf.openrecordset

 

[RichUE]

I think you deserve that!

Forgive me, but I can't find an Immediate window in 97. But I opened the
Debug window and strSQL is
" : strSQL : "SELECT Component, Parent FROM [Fsbill] WHERE ID =
"0-021875GA"" : String
This looks good - in terms of quotes, that is.

I shall also be out of the office now until Monday (I'm a part-timer), but
I'll try to catch you from home. Cheers, Dale.
--
Richard

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

Rich,

That particular error implies that the SQL string is not formatted properly.

When it bombs, and asks whether you to end or debug, select debug. Then, in
the immediate window at the bottom of the VB editor type:

?strSQL

Then copy and paste what it prints into another message. My guess is there
is a " missing. You might also want to post the most recent copy of the code
for fnProgenitor.

I'll be out of the net for a couple of hours.
--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks again, Dale. I modified the PARAMETERS declaration, but Access 97
won't accept the character length. I made that small modification to the
function.

At this point, Debug kicks in with "Too few parameters. Expected: 1" and
highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options, lockedits)"

I added an extra "," but this gave the error "Invalid argument". I tried
moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

--
Richard

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

When you declare the parameters, you are declaring the values you plan on
entering when the query is run. Since you are planning on typing values into
input boxes for the component and the parent parts, those are the parameters
you should be declaring. In this case, the Parameters line should look
something like:

PARAMETERS [Enter Component Part Number:] Text (15),
[Enter Parent Part Number:] Text (15) ;

I would set the # of characters (I used 15) to the same number of characters
defined for the fields (Component, and Parent) in your table.

Also, I just noticed that I failed to change the declaration of the return
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
value.

Function fnProgenitor(Tablename as string, ID as String) as String

fnProgenitor = ""

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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


:

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

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


:

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.



:

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


:

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

 

[Dale Fye]

Bob is right, it is the ID field in the SQL String.

It should read:

strSQL = "SELECT [Component], Parent FROM [" & TableName & "] " _
& "WHERE [Component] = " & chr$(34) & strMyID & chr$(34)

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

I think you deserve that!

Forgive me, but I can't find an Immediate window in 97. But I opened the
Debug window and strSQL is
" : strSQL : "SELECT Component, Parent FROM [Fsbill] WHERE ID =
"0-021875GA"" : String
This looks good - in terms of quotes, that is.

I shall also be out of the office now until Monday (I'm a part-timer), but
I'll try to catch you from home. Cheers, Dale.
--
Richard

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

Rich,

That particular error implies that the SQL string is not formatted properly.

When it bombs, and asks whether you to end or debug, select debug. Then, in
the immediate window at the bottom of the VB editor type:

?strSQL

Then copy and paste what it prints into another message. My guess is there
is a " missing. You might also want to post the most recent copy of the code
for fnProgenitor.

I'll be out of the net for a couple of hours.
--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.

Thanks again, Dale. I modified the PARAMETERS declaration, but Access 97
won't accept the character length. I made that small modification to the
function.

At this point, Debug kicks in with "Too few parameters. Expected: 1" and
highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options, lockedits)"

I added an extra "," but this gave the error "Invalid argument". I tried
moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

--
Richard

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


:

When you declare the parameters, you are declaring the values you plan on
entering when the query is run. Since you are planning on typing values into
input boxes for the component and the parent parts, those are the parameters
you should be declaring. In this case, the Parameters line should look
something like:

PARAMETERS [Enter Component Part Number:] Text (15),
[Enter Parent Part Number:] Text (15) ;

I would set the # of characters (I used 15) to the same number of characters
defined for the fields (Component, and Parent) in your table.

Also, I just noticed that I failed to change the declaration of the return
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
value.

Function fnProgenitor(Tablename as string, ID as String) as String

fnProgenitor = ""

--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

Thanks, Dale. I understood everything you posted, and re-pasted the function.
Iadded Parameter type definitions for Fsbill.COMPONENT and
fnProgenitor("Fsbill", Fsbill.PARENT) but I have a syntax error on the
PARAMETERS line of the SQL:
PARAMETERS Fsbill.COMPONENT Text, [Expr1:
fnProgenitor("Fsbill",[Fsbill]].[[PARENT])] Text;
The second parameter looks strange, but what do I know?

Thanks very much for sticking with me this far. It's a real education for me.
--
Richard

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


:

Actually, I'm on Eastern Daylight Time(it is currently 9:50 AM).

I've got a couple of points.

1. You don't need to use quotes inside of your parameters.

Change: ["Enter Component Part Number:"]
to: [Enter Component Part Number:]

When I said you might need to declare their data types, I meant that you
should define the parameters. You do this by right clicking in the upper
portion of the query grid (but not on a table), and selecting the Parameters
option from the shortcut menu. This will display the Parameters dialog box,
where you can enter your parameters (I usually cut and paste from my query to
make sure there is an exact match), and select their data type. This helps
Access/Jet correctly interpret the data type of the parameter being passed.

2. When you changed the datatype declaration on lngMyID to string, I would
have changed the name of the variable as well, to strMyID. This would make
your code easier to read and to interpret. Additionally, since the data type
is a string, you have to make sure you wrap the variable (strMyID) in quotes
in the SQL string.

Let me rewrite fnProgenitor as I think it should probably read for your
application:

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

Dim strSQL As String
Dim rs As DAO.Recordset

Dim strMyID As String

strMyID = ID
fnProgenitor = 0

Do
strSQL = "SELECT Component, Parent FROM [" & TableName & "] " _
& "WHERE ID = " & chr$(34) & strMyID & chr$(34)
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)
If IsNull(rs("Parent")) Then
fnProgenitor = rs("Component")
Exit Do
Else
strMyID = rs("Parent")
End If
rs.Close
Set rs = Nothing
Loop

End Function

You had changed TableName in the function to "FsBill", but did not need to
do that, since you TableName is a variable, and you are now passing that
value to the function.



--
HTH
Dale

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

email address is invalid
Please reply to newsgroup only.



:

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


:

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.



:

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


:

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

 

[RichUE]

Thanks, Bob, for your input. I'm processing your answer. I examined your code
but I'm unsure whether it entirely replaces the code that I'd previously
developed with Dale's help. Or perhaps your code should be inserted after the
declarations and before the Function? Please help an Access newbie who's
willing to learn but has to put up with Access 97.

Referring to the prompting matter, how should I modify the WHERE clause in
the query? At present the SQL is like this:

PARAMETERS [Enter Component Part Number:] Text, [Enter Parent Part Number:]
Text;
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:]));

I understand that the parameters are now requested from the fnProgenitor
function so I can't simply remove the WHERE clause.

--
Richard

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

Thanks again, Dale. I modified the PARAMETERS declaration, but Access
97 won't accept the character length. I made that small modification
to the function.

At this point, Debug kicks in with "Too few parameters. Expected: 1"
and highlights the line:
Set rs = CurrentDb.OpenRecordset(strSQL, , dbFailOnError)

The Help for the OpenRecordset method says:

That is totally irrelevant. That error is a result of a problem in your
sql statement.

"For Connection and Database objects:
Set recordset = object.OpenRecordset (source, type, options,
lockedits)"

I added an extra "," but this gave the error "Invalid argument". I
tried moving the "," but this only gave a syntax error.

Another argument seems to be required. What should it be?

The error says nothing about arguments. You are focusing on the wrong
thing. The "too few parameters" error has a couple possible causes:
1. You've misspelled a field name somewhere in your sql statement - when
Jet encounters an object name that cannot be resolved, it treats it as a
parameter, and looks for a corresponding parameter value that should
have been passed.
2. You have deliberately put parameters in your sql statement but have
failed to supply values for those parameters via the Querydef's
Parameters collection.

Looking back over this thread, I see that you want the user to be
prompted for some parameter values. That means Access needs to be
involved. When you open a recordset via DAO, Access is not involved, so
there can be no automatic prompting for parameter values. YOU have to
prompt the user for values and supply them to the query via the Querydef
object's Parameters collection. If you want Access to prompt the user
automatically, then you need to forget about opening a recordset: the
only way to get Access to run a query is to use the DoCmd.RunSQL method.
Since it appears you require a recordset, you need to prompt the user
for values (using Inputbox or better: an unbound form) and supply the va
lues the user enters to a Querydef's Parameters collection, like this:

dim db as database, qdf as querydef, rs as dao.recordset
dim sql as string, component as string,parentpart as string
component =inputbox("Enter component")
parentpart=inputbox("Enter parent part")
'validate that data was entered
sql = "SELECT Fsbill.COMPONENT, Fsbill.COMP_DESC, " & _
"Fsbill.PARENT, Fsbill.PARNT_DESC " & _
"FROM Fsbill " & _
"WHERE COMPONENT = [Enter Component Part Number] " & _
"AND fnProgenitor(""Fsbill"", Fsbill.PARENT) = " & _
"[Enter Parent Part Number:]"
set db=currentdb
set qdf=db.createquerydef("",sql)
qdf(0)=component
qdf(1)=parentpart
set rs=qdf.openrecordset

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

 

[Bob Barrows [MVP]]

I think Dale already added more information beyond this. You should read
his reply and then ask for specifics.

Thanks, Bob, for your input. I'm processing your answer. I examined
your code but I'm unsure whether it entirely replaces the code that
I'd previously developed with Dale's help. Or perhaps your code
should be inserted after the declarations and before the Function?

You seem to be confused between the SQL statement and the VBA code used
to run the sql statement.

Please help an Access newbie who's willing to learn but has to put up
with Access 97.

Referring to the prompting matter, how should I modify the WHERE
clause in the query? At present the SQL is like this:

PARAMETERS [Enter Component Part Number:] Text, [Enter Parent Part
Number:] Text;
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:]));

This requires no modification. The VBA code needs to be modified to
provide the values for the parameters in your sql statement.

Parameters collection, like this:

dim db as database, qdf as querydef, rs as dao.recordset
dim sql as string, component as string,parentpart as string
component =inputbox("Enter component")
parentpart=inputbox("Enter parent part")
'validate that data was entered
sql = "SELECT Fsbill.COMPONENT, Fsbill.COMP_DESC, " & _
"Fsbill.PARENT, Fsbill.PARNT_DESC " & _
"FROM Fsbill " & _
"WHERE COMPONENT = [Enter Component Part Number] " & _
"AND fnProgenitor(""Fsbill"", Fsbill.PARENT) = " & _
"[Enter Parent Part Number:]"
set db=currentdb
set qdf=db.createquerydef("",sql)
qdf(0)=component
qdf(1)=parentpart
set rs=qdf.openrecordset

I still think you should use an unbound form to solicit the values from
the user. That way you would no longer have to worry about
programmatically supplying those values.