Update Query runs, no warnings, and not passing values.

[uncleG]

Mission: Pass the calculated values from qryBomMaterialsAddParts2.[IncreaseBy] into tblBomMaterials.AssemblyAdds

There can be 0 to 25 or more records involved in the record set to update.
Query 2 provides the recordset needed to update.
Query 3 should update the table, but does not.

Query1 qryBomMaterialsAddParts (Gets the filter criteria [VersionG], [Ln2] and the value to pass [AddForFallout])

SELECT qryBomMaterialsAssem.VersionG, qryBomMaterialsAssem.BomLineId, qryBomMaterialsAssem.LN, Int([LN]) AS Ln2, qryBomMaterialsAssem.ItemType, qryBomMaterialsAssem.AddForFallout, qryBomMaterialsAssem.SubTotal
FROM qryBomMaterialsAssem
WHERE (((qryBomMaterialsAssem.VersionG)=[Forms].[frmBom].[Form]![VersionG]) AND ((Int([LN]))=Int([Forms].[frmBom].[Form].[frmBomMaterialsAssem]![LN])) AND ((qryBomMaterialsAssem.Qty)>0));

Query 2 qryBomMaterialsAddParts2 (Selects the Record Set to update)

SELECT tblBomMaterials.BomLineId, tblBomMaterials.VersionG, tblItems.ItemType, tblBomMaterials.LN, Int([tblBomMaterials].[LN]) AS Family, tblBomMaterials.Qty, IIf(([tblBomMaterials].[LN])=([tblBomMaterials].[LN]/1),1,0) AS IsTopLevel, DLookUp("AddForFallout","QryBomMaterialsAddParts") AS AFF, [Qty]*[AFF] AS IncreaseBy
FROM tblBomMaterials LEFT JOIN tblItems ON tblBomMaterials.ItemNo = tblItems.ItemID
WHERE (((tblBomMaterials.VersionG)=[Forms].[frmBom].[Form]![VersionG]) AND
((Int([tblBomMaterials].[LN]))=Int([Forms].[frmBom].[Form].[frmBomMaterialsAssem]![LN])) AND ((tblBomMaterials.Qty)>0) AND
((IIf(([tblBomMaterials].[LN])=([tblBomMaterials].[LN]/1),1,0))=1));

Query 3 qryBomMaterialsAddParts3 (Should Update records selected in Query 2 (tblBomMaterials.AssemblyAdds)with the value in [IncreaseBy])

UPDATE tblBomMaterials SET tblBomMaterials.AssemblyAdds = qryBomMaterialsAddParts2.IncreaseBy
WHERE (((tblBomMaterials.VersionG)=[Forms].[frmBom].[Form]![VersionG]) AND ((Int([tblBomMaterials].[LN]))=Int([Forms].[frmBom].[Form].[frmBomMaterialsAssem]![LN])) AND ((tblBomMaterials.Qty)>0) AND ((IIf(([tblBomMaterials].[LN])=([tblBomMaterials].[LN]/1),1,0))=1));

The Problem:
Query 3 produces the correct number of records but the fields are all blank, and also runs with out a warning even though warnings are set and passes no values to the table.

Thanks,
UncleG

 

[dhookom]

Query 3 doesn't have qryBomMaterialsAddParts2 in a from clause. I'm surprised you don't get prompted for qryBomMaterialsAddParts2.IncreaseBy.

I also question this syntax:

[Forms].[frmBom].[Form]![VersionG]

Duane
Hook'D on Access
MS Access MVP

 

[uncleG]

Thanks for showing some interest here Duane, I have read your answers here at tek tips and have learned a lot over the years. Query 3 was the result of several days of frustration. Trying this that and the other thing. In essence one query contained the records and the values to update and I just couldn't figure out how to pass the value to the target fields in my table. Some where yesterday on tek tips I stumbled across using alais's when working within the same table, written by PHV, to help some one with a delete query. The light came on and I re wrote query 3 using alais's and it all somehow came together.

This is the query that finally did the trick.

UPDATE tblBomMaterials AS P INNER JOIN qryBomMaterialsAddParts2 AS I ON (P.VersionG = I.VersionG) AND (P.BomLineId = I.BomLineId) SET P.AssemblyAdds = [I].[IncreaseBy]
WHERE (((P.VersionG)=[Forms]![frmBom]![VersionG]) AND ((Int([P].[LN]))=Int([Forms]![frmBom].[Form].[frmBomMaterialsAssem]![LN])) AND ((P.Qty)>0) AND ((IIf(([P].[LN])=([P].[LN]/1),1,0))=1));