OK; I guess it's back to the drawing board; thanks.
"Jerry Whittle" wrote:
> 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.
> > > >
> > > >
Similar Threads
