Find all records that match attributes of current part number

[Guest]

I have a main form that lists a part number for an item. I need to build a
query that returns all other part numbers that match certain attributes of
the current part number. For example, I am on a record in my main form that
lists part number 123. Return all part numbers that have the same length,
diameter, material, color, etc as part number 123. This should be easy to do
but I'm drawing a blank on the best way to do it.

I was going to create a temporary query that returned all the fields for
part number=123. Then build a second query that had a whole bunch of
criteria in the form of "length=tempquery.length" and
"diameter=tempquery.diameter". This two step approach can't be the best way
to do it.

Your pointers would be greatly appreciated.

 

[Guest]

To add more information. I have the two queries made that I talked about in
my previous post. Now, I am trying to create an ADO recordset from the
results of my query. I've tried several variations of:

rst.Open "qryRelatedPartNumbersForCurrentMainDesign",
CurrentProject.Connection, adOpenStatic, adLockOptimistic, adcmdTable

I've varied adcmdtable, adlock, adopen and I always get an Error
-2147217900: Invalid SQL statement; expected 'DELETE', 'INSERT'....etc

When I run the query by itself it works fine.

Q: So, how do I get the results of a select query into an ADO recordset so
I can do my further processing in VBA?

 

[[MVP] S.Clark]

The best way to do it is the way you know how, understand, and can maintain
if you came back to it a year later.

I might have a form with a button, that when clicked, creates a SQL
statement in line, which is used as the recordsource for a subform or
report, depending on your need.

 

[John Vinson]

I have a main form that lists a part number for an item. I need to build a
query that returns all other part numbers that match certain attributes of
the current part number. For example, I am on a record in my main form that
lists part number 123. Return all part numbers that have the same length,
diameter, material, color, etc as part number 123. This should be easy to do
but I'm drawing a blank on the best way to do it.

You might want to try a "Self Join" query. Add the parts table to the
query window TWICE; join the two instances by Length, Diameter,
Material, Color - up to ten join lines are allowed. DON'T join by Part
Number.

Now if you apply a criterion to the first table instance's part
number, you'll see all the matching parts in the second table's
fields.

John W. Vinson[MVP]

 

[Guest]

Still struggling with this! Now I have tried the following code, taken from
"Access 2000 Developer's Handbook":

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockReadOnly
strQryName= "qryRelatedPartNumbersForCurrentMainDesign"
rs.Open strQryName, Options:=adCmdTableDirect

The error I receive now is:
Error -2147217904: Too Few Parameters. Expected 1.

This is taken right out of the Developer's Handbook so I'm confused why it
won't work. I have tried changing the reference from ADO 2.8 to 2.5, 2.1 and
none of them work. The database also has a reference to DAO, but since I
create the recordset using adodb.recordset there should be no ambiguity.
I've tried on both Access 2000 and Access 2003.

Any ideas ??? I'm out of things to try

 

[John Vinson]

Still struggling with this! Now I have tried the following code, taken from
"Access 2000 Developer's Handbook":

Set rs = New ADODB.Recordset
Set rs.ActiveConnection = CurrentProject.Connection
rs.CursorType = adOpenKeyset
rs.LockType = adLockReadOnly
strQryName= "qryRelatedPartNumbersForCurrentMainDesign"
rs.Open strQryName, Options:=adCmdTableDirect

The error I receive now is:
Error -2147217904: Too Few Parameters. Expected 1.

Apparently qryRelatedPartNumbersForCurrentMainDesign is a Parameter
query and you haven't specified the parameter. I'm a DAO user, and I
know how to evaluate parameters in a DAO querydef, but not in ADODB.

Perhaps you could post the SQL view of
qryRelatedPartNumbersForCurrentMainDesign.

John W. Vinson[MVP]

 

[Guest]

Here's the SQL of the two queries in question. Is the parameter you refer to
the criteria in qryWindchillDataForCurrentMainRecord
[forms]![frmmaindesign]![partnum] ?? The form is open when I am trying to
run the query so I thought it could pull the value directly. You imply I may
have to specify it directly.

qryWindchillDataForCurrentMainRecord SQL is:

SELECT tblMainWindchillData_Fixed.number AS Expr1,
tblMainWindchillData_Fixed.name AS Expr2, tblMainWindchillData_Fixed.NOUN AS
Expr3, tblMainWindchillData_Fixed.[TUBING SIZE NOMINAL] AS Expr4,
tblMainWindchillData_Fixed.[LOBE CONFIGURATION] AS Expr5,
tblMainWindchillData_Fixed.STAGES AS Expr6, tblMainWindchillData_Fixed.[MODEL
SIZE] AS Expr7, tblMainWindchillData_Fixed.TYPE1 AS Expr8,
tblMainWindchillData_Fixed.[EXTERNAL COATING] AS Expr9
FROM tblMainWindchillData_Fixed
WHERE ((([tblMainWindchillData_Fixed].[number]) Like "*" &
[forms]![frmmaindesign]![partnum]));



qryRelatedPartNumbersForCurrentMainDesign SQL is:

SELECT tblMainWindchillData_Fixed.number AS Expr1,
tblMainWindchillData_Fixed.NOUN AS Expr2, tblMainWindchillData_Fixed.[TUBING
SIZE NOMINAL] AS Expr3, tblMainWindchillData_Fixed.[LOBE CONFIGURATION] AS
Expr4, tblMainWindchillData_Fixed.STAGES AS Expr5,
tblMainWindchillData_Fixed.[MODEL SIZE] AS Expr6,
tblMainWindchillData_Fixed.TYPE1 AS Expr7,
tblMainWindchillData_Fixed.[EXTERNAL COATING] AS Expr8
FROM tblMainWindchillData_Fixed, qryWindchillDataForCurrentMainRecord
WHERE
((([tblMainWindchillData_Fixed].[NOUN])=[qryWindchillDataForCurrentMainRecord].[noun])
AND (([tblMainWindchillData_Fixed].[TUBING SIZE
NOMINAL])=[qryWindchillDataForCurrentMainRecord].[tubing size nominal]) AND
(([tblMainWindchillData_Fixed].[LOBE
CONFIGURATION])=[qryWindchillDataForCurrentMainRecord].[lobe configuration])
AND
(([tblMainWindchillData_Fixed].[STAGES])=[qryWindchillDataForCurrentMainRecord].[stages])
AND (([tblMainWindchillData_Fixed].[MODEL
SIZE])=[qryWindchillDataForCurrentMainRecord].[model size]) AND
(([tblMainWindchillData_Fixed].[TYPE1])=[qryWindchillDataForCurrentMainRecord].[type1])
AND (([tblMainWindchillData_Fixed].[EXTERNAL
COATING])=[qryWindchillDataForCurrentMainRecord].[external coating]));


Thank You