I'm very surprised that the query below is updateable when attached to a
form. There's multiple Right and Left Joins and some functions which would
keep those fields from being updated. TblBOMDetails seems to be joined a
bunch of different ways to other tables.

If this complicate query is updateable, then it's just possible that basing
an update query on it is just too much for Access to handle.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"bicyclops" wrote:

QryBOMDetails2 is a biggie. But it's also attached to a form & normally
updates with no problem. Sql is below.

SELECT TblBOMDetails.LineItem, TblBOMDetails.BOMDetailID,
TblBOMDetails.PartNumID, TblBOMDetails.ChildBOMID, [PartPrefix] & "-" &
Format([Part#Suffix],"0000") AS [ItemPart#], TblBOMDetails.ParentBOMID,
TblBOMDetails.Qty, TblBOMDetails.OverBuyQty, TblBOMDetails.Refdes,
TblComps.Description, TblPartPrefix.[Prefix Description],
TblBOMDetails.MfrPNID, TblMFRPN.MFRPNPart, TblMFR.MFRMfrName,
TblMFRPN.MFRDesc, TblMFRPN.Price AS MfrBasePrice, TblBOMDetails.VendorPNID,
TblVendorPN.VendorID, TblVendor.SUSupplier, TblVendorPN.VendorPN,
TblBOMDetails.CustPNID, TblCompanies.CompanyName, TblCustPN.CustPN,
TblBOMDetails.SourceFromVendor, TblBOMDetails.VendorSourceQty,
TblBOMDetails.SourceFromCust, TblBOMDetails.CustSourceQty,
TblBOMDetails.SourceFromMfr, TblBOMDetails.MfrSourceQty,
TblBOMDetails.SourceFromStock, TblBOMDetails.StockSourceQty,
TblComps.StockQty, TblBOM.BuildQty,
(Nz([BuildQty],1)*[Qty])+(Nz([OverBuyQty],0)) AS ExtQty, TblComps.UnitsID,
TblUnits.BuyAs, TblBOMDetails.Price AS BOMPrice, TblBOMDetails.MfrPNTemp,
TblVendor.SUID,
IIf([ExtQty](Nz([VendorSourceQty],0))+(Nz([CustSourceQty],0))+(Nz([MfrSourceQty],0))+(Nz([StockSourceQty],0)),1,0) AS QtyMismatchFlag
FROM TblVendor RIGHT JOIN (TblVendorPN RIGHT JOIN (TblUnits RIGHT JOIN
(TblPartPrefix RIGHT JOIN (TblMFR RIGHT JOIN ((TblCompanies RIGHT JOIN
TblCustPN ON TblCompanies.CompanyID = TblCustPN.CustID) RIGHT JOIN (TblBOM
RIGHT JOIN ((TblComps RIGHT JOIN TblBOMDetails ON TblComps.[Part#ID] =
TblBOMDetails.PartNumID) LEFT JOIN TblMFRPN ON TblBOMDetails.MfrPNID =
TblMFRPN.MFRPNID) ON TblBOM.BOMID = TblBOMDetails.ParentBOMID) ON
TblCustPN.CustPNID = TblBOMDetails.CustPNID) ON TblMFR.MFRID =
TblMFRPN.MFRPNMFRID) ON TblPartPrefix.PartPrefixID = TblComps.PartPrefixID)
ON TblUnits.ID = TblComps.UnitsID) ON TblVendorPN.VendorPNID =
TblBOMDetails.VendorPNID) ON TblVendor.SUID = TblVendorPN.VendorID
ORDER BY TblBOMDetails.LineItem;


"Jerry Whittle" wrote:

QryBOMDetails2

We probably need to see the sql for it. There's many reasons why a query
isn't updateable.

BTW: Check out my signature line!
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.


"bicyclops" wrote:

The below query works well as a Select Query. But when I turn it into an
update query, I get a whole lot of nothing in the results.

Interestingly, if I try to call this query from a Macro on my form, I get
error (-20324) repeated the number of times that there are records in the
query.

It was designed to go into a table of price breaks & decide whether the
quantities being ordered meet the price breaks. It then takes either that
adjusted price, or the original price & populate the values into a table.

Select Query:
SELECT QryBOMDetails2.ParentBOMID, QryBOMDetails2.BOMPrice,
IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And
[Level]=" &
[ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" &
[MfrPNID] & "And [Level]=" & [ExtQty])) AS q
FROM QryBOMDetails2
WHERE (((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]) AND
((QryBOMDetails2.BOMPrice) Is Null));

Update Query:
UPDATE QryBOMDetails2 SET QryBOMDetails2.BOMPrice =
IIf(IsNull(DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" & [MfrPNID] & "And
[Level]=" &
[ExtQty])),[mfrbaseprice],DMin("[Price]","TblMfrPriceBreak","[MfrPNID]=" &
[MfrPNID] & "And [Level]=" & [ExtQty]))
WHERE (((QryBOMDetails2.BOMPrice) Is Null) AND
((QryBOMDetails2.ParentBOMID)=[Forms]![FrmBOMEntry]![TxtBOMID]));

FYI I'm doing this using the query builder.
Thanks in advance for any advice.