help with figuring out how to make subqueries

[DawnTreader]

Hello All

i have a few tables that i need to use to get a list of Products that
"qualify" for adding parts to the products part list.

i actually have this problem solved, it works, it is just very slow in doing
what i want and there is one small problem that i have no idea how to fix. so
i am hoping someone can suggest how to fix the one circumstance in which this
fails and something to make the whole thing quicker and more efficient.

here is my code:

Private Sub AddPartsToProductsManually()

Dim cn As ADODB.Connection

Dim rstProduct As ADODB.Recordset
Dim rstPartsToAdd As ADODB.Recordset

Dim sqlProducts As String
Dim sqlPartsToAdd As String
Dim sqlInsertPartsToProducts As String

Set cn = CurrentProject.Connection

'sqlProducts = "SELECT qryWhereUsed.PART_ID, qryWhereUsed.QTY_PER,
qryWhereUsed.WORKORDER_BASE_ID, tblProductList.SerialNumber,
tblProductList.ProductID FROM qryWhereUsed LEFT JOIN tblProductList ON
qryWhereUsed.WORKORDER_BASE_ID = tblProductList.WorkOrder WHERE
(((qryWhereUsed.PART_ID) Like " &
[Forms]![frmFindPartWhereUsedwProductSerial]![txtWhatPartHidden] & ") And
((tblProductList.SerialNumber) Is Not Null))"

sqlProducts = "SELECT qryWhereUsedinWhichProduct.WORKORDER_TYPE,
qryWhereUsedinWhichProduct.WORKORDER_BASE_ID,
qryWhereUsedinWhichProduct.PART_ID, qryWhereUsedinWhichProduct.QTY_PER,
qryWhereUsedinWhichProduct.SerialNumber,
qryWhereUsedinWhichProduct.ProductID,
qryProductsThatDoNotQualifyForPartsToLink.IMWPartNumberID,
qryProductsThatDoNotQualifyForPartsToLink.RequirementID,
qryProductsThatDoNotQualifyForPartsToLink.PartToLinkIMWPN " & _
"FROM qryWhereUsedinWhichProduct LEFT JOIN
qryProductsThatDoNotQualifyForPartsToLink ON
qryWhereUsedinWhichProduct.ProductID =
qryProductsThatDoNotQualifyForPartsToLink.ProductID " & _
"WHERE (((qryWhereUsedinWhichProduct.WORKORDER_TYPE)=""w"") AND
((qryWhereUsedinWhichProduct.PART_ID) Like " &
[Forms]![frmFindPartWhereUsedwProductSerial]![txtWhatPartHidden] & ") AND
((qryWhereUsedinWhichProduct.SerialNumber) Is Not Null) AND
((qryProductsThatDoNotQualifyForPartsToLink.RequirementID) Is Null) AND
((qryProductsThatDoNotQualifyForPartsToLink.PartToLinkIMWPN) Is Null))"

sqlPartsToAdd = "SELECT tblPartsToLink.PartToLinkID,
tblPartsToLink.PartToLinkIMWPN, dbo_PART.DESCRIPTION,
tblPartsToLink.SectionNameID FROM tblPartsToLink LEFT JOIN dbo_PART ON
tblPartsToLink.PartToLinkIMWPN = dbo_PART.ID"

' MsgBox sqlProducts
' MsgBox sqlPartsToAdd

Set rstProduct = New ADODB.Recordset
With rstProduct
Set .ActiveConnection = cn
.Source = sqlProducts
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Set rstPartsToAdd = New ADODB.Recordset
With rstPartsToAdd
Set .ActiveConnection = cn
.Source = sqlPartsToAdd
.LockType = adLockReadOnly
.CursorType = adOpenForwardOnly
.Open
End With

Do While Not rstProduct.EOF
rstPartsToAdd.MoveFirst
Do While Not rstPartsToAdd.EOF
sqlInsertPartsToProducts = "INSERT INTO tblProductPartList
(ProductID, IMWPartNumberID, QTY, SectionNameID) VALUES (" &
rstProduct.Fields("ProductID") & ", " &
rstPartsToAdd.Fields("PartToLinkIMWPN") & ", " & rstProduct.Fields("QTY_PER")
& ", " & rstPartsToAdd.Fields("SectionNameID") & ")"
MsgBox sqlInsertPartsToProducts
DoCmd.RunSQL sqlInsertPartsToProducts
rstPartsToAdd.MoveNext
Loop
MsgBox rstProduct.Fields("ProductID")
rstProduct.MoveNext
Me.sfrmqryWhereUsedinWhichProduct.Requery
Loop

'kill everything
rstProduct.Close
rstPartsToAdd.Close
cn.Close
Set cn = Nothing
Set rstProduct = Nothing
Set rstPartsToAdd = Nothing

End Sub

that is the code behind my form. here is the sql for the 3 queries involved
in making the form show what is going on:

qryProductsThatDoNotQualifyForPartsToLink
SELECT
tblProductPartList.ProductID,
tblProductPartList.IMWPartNumberID,
tblProductPartList.RequirementID,
tblProductPartList.DateCreated,
tblPartsToLink.PartToLinkIMWPN
FROM
tblPartsToLink RIGHT JOIN tblProductPartList ON
tblPartsToLink.PartToLinkIMWPN = tblProductPartList.IMWPartNumberID
WHERE
(((tblProductPartList.RequirementID) Is Null) AND
((tblPartsToLink.PartToLinkIMWPN) Is Not Null));

qryWhereUsedinWhichProduct
SELECT
dbo_REQUIREMENT.WORKORDER_TYPE,
dbo_REQUIREMENT.WORKORDER_BASE_ID,
dbo_REQUIREMENT.PART_ID,
dbo_REQUIREMENT.QTY_PER,
tblProductList.SerialNumber,
tblProductList.ProductID
FROM
dbo_REQUIREMENT LEFT JOIN tblProductList ON
dbo_REQUIREMENT.WORKORDER_BASE_ID = tblProductList.WorkOrder;

qryProductsThatQualifyForPartsToLink
SELECT
qryWhereUsedinWhichProduct.WORKORDER_TYPE,
qryWhereUsedinWhichProduct.WORKORDER_BASE_ID,
qryWhereUsedinWhichProduct.PART_ID,
qryWhereUsedinWhichProduct.QTY_PER,
qryWhereUsedinWhichProduct.SerialNumber,
qryWhereUsedinWhichProduct.ProductID,
qryProductsThatDoNotQualifyForPartsToLink.IMWPartNumberID,
qryProductsThatDoNotQualifyForPartsToLink.RequirementID,
qryProductsThatDoNotQualifyForPartsToLink.DateCreated,
qryProductsThatDoNotQualifyForPartsToLink.PartToLinkIMWPN
FROM
qryWhereUsedinWhichProduct LEFT JOIN
qryProductsThatDoNotQualifyForPartsToLink ON
qryWhereUsedinWhichProduct.ProductID =
qryProductsThatDoNotQualifyForPartsToLink.ProductID
WHERE
(((qryWhereUsedinWhichProduct.WORKORDER_TYPE)="w") AND
((qryWhereUsedinWhichProduct.PART_ID)=[Forms]![frmFindPartWhereUsedwProductSerial]![txtWhatPartHidden]) AND
((qryWhereUsedinWhichProduct.SerialNumber) Is Not Null) AND
((qryProductsThatDoNotQualifyForPartsToLink.RequirementID) Is Null)
AND((qryProductsThatDoNotQualifyForPartsToLink.PartToLinkIMWPN) Is Null));

like i said it all works, it is just the updating of the parts to the
product part list takes a long while and the fact that if there is more than
one part to add to the product then there is a problem.

the problem goes like this; if a product qualifies for one part but not the
other then it wont be updated or show in the qualifying list. this shouldnt
be a problem, but there is always the possiblity that some one will make a
mistake and so i look for an answer to the problem.

somehow the query that sees if a product has what is stored in the parts to
add table needs to realise that just because one part is listed doesnt mean
that it doesnt qualify for the other part(s).

can any one help? if you need more information please let ask!