I took off most of the fields in an effort to identify the issue. The
original query is below. If I change the query to an update query it will
change the records. However, if I manually try to change the records the
system just beeps. Any ideas?
SELECT tblProd.SchedItem, tblRoughCutHdr.WeekStartDate, tblProd.UPCFip13,
tblRoughCutHdr.ProdID, tblProdTemplate.TemplateDesc, tblProd.SeqNo,
tblProd.ProdDesc, tblProd.TemplateID, tblProd.DaysToCover,
tblProd.Tolerance,
tblProd.MinimumOrder, tblProd.IncrementOrder, tblProd.ProdUnitsHr,
tblSchedDtl.WeekNo,
IdentifyPromo([tblSchedDtl].[SchedDate],[tblProd].[UPCFip13]) AS SunPromo,
tblSchedDtl.SchedDate AS SunDte, tblSchedDtl.ProduceIt AS SunProduceIt,
tblSchedDtl.BegInvAdj AS SunBegInvAdj, tblSchedDtl.Forecast AS
SunForecast,
tblSchedDtl.ForecastType AS SunForecastType, tblSchedDtl.SalesOrdAdj AS
SunSalesOrdAdj, tblSchedDtl.SalesOthAdj AS SunSalesOthAdj,
tblSchedDtl.Prod
AS SunProd, tblSchedDtl.ProdAdj AS SunProdAdj, tblSchedDtl.EndInv AS
SunEndInv, tblSchedDtl.ProdCalc AS SunProdCalc, tblSchedDtl.ErrMsg AS
SunErrMsg, [SunProdAdj]/[ProdUnitsHr] AS SunLineHrs,
IdentifyPromo([tblSchedDtl_1].[SchedDate],[tblProd].[UPCFip13]) AS
MonPromo,
tblSchedDtl_1.SchedDate AS MonDte, tblSchedDtl_1.ProduceIt AS
MonProduceIt,
tblSchedDtl_1.BegInvAdj AS MonBegInvAdj, tblSchedDtl_1.Forecast AS
MonForecast, tblSchedDtl_1.ForecastType AS MonForecastType,
tblSchedDtl_1.SalesOrdAdj AS MonSalesOrdAdj, tblSchedDtl_1.SalesOthAdj AS
MonSalesOthAdj, tblSchedDtl_1.Prod AS MonProd, tblSchedDtl_1.ProdAdj AS
MonProdAdj, tblSchedDtl_1.EndInv AS MonEndInv, tblSchedDtl_1.ProdCalc AS
MonProdCalc, tblSchedDtl_1.ErrMsg AS MonErrMsg, [MonProdAdj]/[ProdUnitsHr]
AS
MonLineHrs,
IdentifyPromo([tblSchedDtl_2].[SchedDate],[tblProd].[UPCFip13])
AS TuePromo, tblSchedDtl_2.SchedDate AS TueDte, tblSchedDtl_2.ProduceIt AS
TueProduceIt, tblSchedDtl_2.BegInvAdj AS TueBegInvAdj,
tblSchedDtl_2.Forecast
AS TueForecast, tblSchedDtl_2.ForecastType AS TueForecastType,
tblSchedDtl_2.SalesOrdAdj AS TueSalesOrdAdj, tblSchedDtl_2.SalesOthAdj AS
TueSalesOthAdj, tblSchedDtl_2.Prod AS TueProd, tblSchedDtl_2.ProdAdj AS
TueProdAdj, tblSchedDtl_2.EndInv AS TueEndInv, tblSchedDtl_2.ProdCalc AS
TueProdCalc, tblSchedDtl_2.ErrMsg AS TueErrMsg, [TueProdAdj]/[ProdUnitsHr]
AS
TueLineHrs,
IdentifyPromo([tblSchedDtl_3].[SchedDate],[tblProd].[UPCFip13])
AS WedPromo, tblSchedDtl_3.SchedDate AS WedDte, tblSchedDtl_3.ProduceIt AS
WedProduceIt, tblSchedDtl_3.BegInvAdj AS WedBegInvAdj,
tblSchedDtl_3.Forecast
AS WedForecast, tblSchedDtl_3.ForecastType AS WedForecastType,
tblSchedDtl_3.SalesOrdAdj AS WedSalesOrdAdj, tblSchedDtl_3.SalesOthAdj AS
WedSalesOthAdj, tblSchedDtl_3.Prod AS WedProd, tblSchedDtl_3.ProdAdj AS
WedProdAdj, tblSchedDtl_3.EndInv AS WedEndInv, tblSchedDtl_3.ProdCalc AS
WedProdCalc, tblSchedDtl_3.ErrMsg AS WedErrMsg, [WedProdAdj]/[ProdUnitsHr]
AS
WedLineHrs,
IdentifyPromo([tblSchedDtl_4].[SchedDate],[tblProd].[UPCFip13])
AS ThuPromo, tblSchedDtl_4.SchedDate AS ThuDte, tblSchedDtl_4.ProduceIt AS
ThuProduceIt, tblSchedDtl_4.BegInvAdj AS ThuBegInvAdj,
tblSchedDtl_4.Forecast
AS ThuForecast, tblSchedDtl_4.ForecastType AS ThuForecastType,
tblSchedDtl_4.SalesOrdAdj AS ThuSalesOrdAdj, tblSchedDtl_4.SalesOthAdj AS
ThuSalesOthAdj, tblSchedDtl_4.Prod AS ThuProd, tblSchedDtl_4.ProdAdj AS
ThuProdAdj, tblSchedDtl_4.EndInv AS ThuEndInv, tblSchedDtl_4.ProdCalc AS
ThuProdCalc, tblSchedDtl_4.ErrMsg AS ThuErrMsg, [ThuProdAdj]/[ProdUnitsHr]
AS
ThuLineHrs,
IdentifyPromo([tblSchedDtl_5].[SchedDate],[tblProd].[UPCFip13])
AS FriPromo, tblSchedDtl_5.SchedDate AS FriDte, tblSchedDtl_5.ProduceIt AS
FriProduceIt, tblSchedDtl_5.BegInvAdj AS FriBegInvAdj,
tblSchedDtl_5.Forecast
AS FriForecast, tblSchedDtl_5.ForecastType AS FriForecastType,
tblSchedDtl_5.SalesOrdAdj AS FriSalesOrdAdj, tblSchedDtl_5.SalesOthAdj AS
FriSalesOthAdj, tblSchedDtl_5.Prod AS FriProd, tblSchedDtl_5.ProdAdj AS
FriProdAdj, tblSchedDtl_5.EndInv AS FriEndInv, tblSchedDtl_5.ProdCalc AS
FriProdCalc, tblSchedDtl_5.ErrMsg AS FriErrMsg, [FriProdAdj]/[ProdUnitsHr]
AS
FriLineHrs,
IdentifyPromo([tblSchedDtl_6].[SchedDate],[tblProd].[UPCFip13])
AS SatPromo, tblSchedDtl_6.SchedDate AS SatDte, tblSchedDtl_6.ProduceIt AS
SatProduceIt, tblSchedDtl_6.BegInvAdj AS SatBegInvAdj,
tblSchedDtl_6.Forecast
AS SatForecast, tblSchedDtl_6.ForecastType AS SatForecastType,
tblSchedDtl_6.SalesOrdAdj AS SatSalesOrdAdj, tblSchedDtl_6.SalesOthAdj AS
SatSalesOthAdj, tblSchedDtl_6.Prod AS SatProd, tblSchedDtl_6.ProdAdj AS
SatProdAdj, tblSchedDtl_6.EndInv AS SatEndInv, tblSchedDtl_6.ProdCalc AS
SatProdCalc, tblSchedDtl_6.ErrMsg AS SatErrMsg, [SatProdAdj]/[ProdUnitsHr]
AS
SatLineHrs, tblProd.Curd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS
SunLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriLgCurd,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatLgCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ProdAdj]*[FillQty],0) AS SunSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_1].[ProdAdj]*[FillQty],0) AS
MonSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_2].[ProdAdj]*[FillQty],0) AS
TueSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_3].[ProdAdj]*[FillQty],0) AS
WedSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_4].[ProdAdj]*[FillQty],0) AS
ThuSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_5].[ProdAdj]*[FillQty],0) AS
FriSmCurd,
IIf([tblProd].[Curd]=No,[tblSchedDtl_6].[ProdAdj]*[FillQty],0) AS
SatSmCurd,
tblSchedDtl.ForecastFip,
IIf([tblProd].[Curd]=No,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstSmLbs,
IIf([tblProd].[Curd]=Yes,[tblSchedDtl].[ForecastFip]*[FillQty],0) AS
FipFcstLgLbs, tblProd.TypeProd
FROM (tblSchedDtl AS tblSchedDtl_6 INNER JOIN (tblSchedDtl AS
tblSchedDtl_5
INNER JOIN (tblSchedDtl AS tblSchedDtl_4 INNER JOIN (tblSchedDtl AS
tblSchedDtl_3 INNER JOIN ((((tblRoughCutHdr INNER JOIN tblSchedDtl ON
tblRoughCutHdr.SunID = tblSchedDtl.SchedDtlID) INNER JOIN tblSchedDtl AS
tblSchedDtl_1 ON tblRoughCutHdr.MonID = tblSchedDtl_1.SchedDtlID) INNER
JOIN
tblProd ON tblRoughCutHdr.ProdID = tblProd.ProdID) INNER JOIN tblSchedDtl
AS
tblSchedDtl_2 ON tblRoughCutHdr.TueID = tblSchedDtl_2.SchedDtlID) ON
tblSchedDtl_3.SchedDtlID = tblRoughCutHdr.WedID) ON
tblSchedDtl_4.SchedDtlID
= tblRoughCutHdr.ThuID) ON tblSchedDtl_5.SchedDtlID =
tblRoughCutHdr.FriID)
ON tblSchedDtl_6.SchedDtlID = tblRoughCutHdr.SatID) INNER JOIN
tblProdTemplate ON tblProd.TemplateID = tblProdTemplate.TemplateID
WHERE (((tblProd.SchedItem)=-1) AND
((tblRoughCutHdr.WeekStartDate)>=DateAdd("d",-Weekday(Date())+1-7,Date())))
ORDER BY tblProdTemplate.TemplateDesc, tblProd.SeqNo;
--
M. Shipp
Baz said:
For a query containing a join to be updatable one would normally expect
to
have the primary key from both tables in the SELECT list. Your query
doesn't have *any* fields from tblSchedDtl_1 in the SELECT list. Are you
sure that tblSched_Dtl_1 is even needed? Or has someone modified the
query
to remove fields from the SELECT list (thereby rendering the query
non-updatable)?
